select *
from (select
a.cert_type,
a.cert_no,
a.cust_name,
e.oper_no,
to_char(c.vou_start_no + instr(c.vou_status, '0') - 1,
'fm00000000') as vou,
e.bran_code
from comr_cifbinfo a,
comc_box b,
comr_clerk_vou c,
auto_comc_clerk e
where a.cert_type = '1'
and substr(rtrim(a.cert_no), 18, 1) is not null
and a.cert_no not in
('330721197211217006X', '1302011981101070315')
and a.valid_flag = '0'
and not exists
(select * from comr_cifacctno ta where ta.cust_no = a.cust_no)
and c.vou_status like '%0%'
and b.bran_code = e.bran_code
and c.bran_code = e.bran_code
and a.bank_corp_code = c.bank_corp_code
and b.box_no = c.box_no
and b.oper_no = e.oper_no
and c.vou_status like '%0%'
and c.vou_type = '11'
and c.sub_vou_type = '0'
and e.post_no in ('12002', '12402')
and e.flag = '0'
order by vou asc)
where rownum < 2;
之前的SQL 由于有坑爹的c.vou_status like '%0%' 条件,导致最终分页SQL无法彻底优化，这次先把like搞掉
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID gv9chqfu3770y, child number 0
-------------------------------------
select * from (select a.cert_type,
a.cert_no, a.cust_name, e.oper_no,
to_char(c.vou_start_no + instr(c.vou_status, '0') - 1,
'fm00000000') as vou, e.bran_code
from comr_cifbinfo a, comc_box b,
comr_clerk_vou c, auto_comc_clerk e
where a.cert_type = '1' and substr(rtrim(a.cert_no), 18, 1)
is not null and a.cert_no not in
('330721197211217006X', '1302011981101070315') and
a.valid_flag = '0' and not exists (select * from
comr_cifacctno ta where ta.cust_no = a.cust_no) /* and
c.vou_status like '%0%'*/ and b.bran_code = e.bran_code
and c.bran_code = e.bran_code and a.bank_corp_code =
c.bank_corp_code and b.box_no = c.box_no and
b.oper_no = e.oper_no /* a
Plan hash value: 2236318696
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.28 | 12288 |
|* 1 | COUNT STOPKEY | | 1 | | 1 |00:00:00.28 | 12288 |
| 2 | VIEW | | 1 | 1 | 1 |00:00:00.28 | 12288 |
| 3 | NESTED LOOPS ANTI | | 1 | 1 | 1 |00:00:00.28 | 12288 |
| 4 | NESTED LOOPS | | 1 | 103 | 3843 |00:00:00.26 | 7317 |
| 5 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 406 |
| 6 | NESTED LOOPS | | 1 | 179 | 198 |00:00:00.01 | 89 |
| 7 | TABLE ACCESS BY INDEX ROWID| COMR_CLERK_VOU | 1 | 2487 | 6 |00:00:00.01 | 9 |
|* 8 | INDEX RANGE SCAN | IDX_COMR_CLERK_VOU | 1 | 15 | 6 |00:00:00.01 | 3 |
|* 9 | TABLE ACCESS FULL | COMC_BOX | 6 | 12 | 198 |00:00:00.01 | 80 |
|* 10 | TABLE ACCESS BY INDEX ROWID | AUTO_COMC_CLERK | 198 | 1 | 1 |00:00:00.01 | 317 |
|* 11 | INDEX UNIQUE SCAN | AUTO_COMC_CLERK_INX | 198 | 1 | 162 |00:00:00.01 | 202 |
|* 12 | TABLE ACCESS BY INDEX ROWID | COMR_CIFBINFO | 1 | 100 | 3843 |00:00:00.26 | 6911 |
|* 13 | INDEX RANGE SCAN | CIFB_IDX2 | 1 | 101 | 3843 |00:00:00.25 | 3138 |
|* 14 | INDEX RANGE SCAN | CIFACCTNO_IDX2 | 3843 | 2110K| 3842 |00:00:00.01 | 4971 |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<2)
8 - access("C"."VOU_TYPE"=11 AND "C"."SUB_VOU_TYPE"=0)
9 - filter("B"."BOX_NO"="C"."BOX_NO")
10 - filter((INTERNAL_FUNCTION("E"."POST_NO") AND "E"."FLAG"='0' AND "B"."BRAN_CODE"="E"."BRAN_CODE" AND
"C"."BRAN_CODE"="E"."BRAN_CODE"))
11 - access("B"."OPER_NO"="E"."OPER_NO")
12 - filter("A"."VALID_FLAG"='0')
13 - access("A"."CERT_TYPE"='1' AND "A"."BANK_CORP_CODE"="C"."BANK_CORP_CODE")
filter((SUBSTR(RTRIM("A"."CERT_NO"),18,1) IS NOT NULL AND "A"."CERT_NO"<>'330721197211217006X' AND
"A"."CERT_NO"<>'1302011981101070315' AND "A"."BANK_CORP_CODE"="C"."BANK_CORP_CODE"))
14 - access("TA"."CUST_NO"="A"."CUST_NO")
55 rows selected.
create index IDX_COMR_CLERK_VOU on COMR_CLERK_VOU (VOU_TYPE, SUB_VOU_TYPE, TO_CHAR(VOU_START_NO+INSTR(VOU_STATUS,'0')-1,'fm00000000'))
|* 8 | INDEX RANGE SCAN | IDX_COMR_CLERK_VOU | 1 | 15 | 6 |00:00:00.01 | 3 |
此时扫描了6条记录停止