<pre name="code" class="sql">explain plan for
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'
and c.vou_start_no is not null
and c.vou_status is not null
order by vou asc)
where rownum < 2;
select * from table(dbms_xplan.display());
Plan hash value: 1521997440
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 85 | | 15567 (2)| 00:03:07 |
|* 1 | COUNT STOPKEY | | | | | | |
| 2 | VIEW | | 268 | 22780 | | 15567 (2)| 00:03:07 |
|* 3 | SORT ORDER BY STOPKEY | | 268 | 70216 | | 15567 (2)| 00:03:07 |
|* 4 | HASH JOIN | | 268 | 70216 | | 15566 (2)| 00:03:07 |
| 5 | TABLE ACCESS FULL | COMC_BOX | 1013 | 23299 | | 5 (0)| 00:00:01 |
|* 6 | HASH JOIN | | 21678 | 5059K| | 15560 (2)| 00:03:07 |
|* 7 | TABLE ACCESS FULL | AUTO_COMC_CLERK | 645 | 19995 | | 9 (0)| 00:00:01 |
|* 8 | HASH JOIN | | 3594 | 730K| | 15550 (2)| 00:03:07 |
|* 9 | TABLE ACCESS BY INDEX ROWID| COMR_CLERK_VOU | 124 | 12648 | | 122 (0)| 00:00:02 |
|* 10 | INDEX RANGE SCAN | COMR_CLERK_VOU_IDX3 | 124 | | | 28 (0)| 00:00:01 |
|* 11 | HASH JOIN ANTI | | 289 | 30634 | 2488K| 15427 (2)| 00:03:06 |
|* 12 | TABLE ACCESS FULL | COMR_CIFBINFO | 28899 | 2144K| | 7735 (2)| 00:01:33 |
| 13 | INDEX FAST FULL SCAN | CIFACCTNO_IDX2 | 2110K| 60M| | 3326 (2)| 00:00:40 |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<2)
3 - filter(ROWNUM<2)
4 - access("B"."BRAN_CODE"="E"."BRAN_CODE" AND "B"."BOX_NO"="C"."BOX_NO" AND
"B"."OPER_NO"="E"."OPER_NO")
6 - access("C"."BRAN_CODE"="E"."BRAN_CODE")
7 - filter(("E"."POST_NO"='12002' OR "E"."POST_NO"='12402') AND "E"."FLAG"='0')
8 - access("A"."BANK_CORP_CODE"="C"."BANK_CORP_CODE")
9 - filter("C"."VOU_START_NO" IS NOT NULL)
10 - access("C"."VOU_TYPE"=11 AND "C"."SUB_VOU_TYPE"=0)
filter("C"."VOU_STATUS" LIKE '%0%' AND "C"."VOU_STATUS" IS NOT NULL AND "C"."SUB_VOU_TYPE"=0 AND
"C"."VOU_STATUS" IS NOT NULL)
11 - access("TA"."CUST_NO"="A"."CUST_NO")
12 - filter(SUBSTR(RTRIM("A"."CERT_NO"),18,1) IS NOT NULL AND "A"."CERT_TYPE"='1' AND
"A"."VALID_FLAG"='0' AND "A"."CERT_NO"<>'330721197211217006X' AND "A"."CERT_NO"<>'1302011981101070315')
访问了comr_clerk_vou c 对应索引为
create index COMR_CLERK_VOU_IDX3 on COMR_CLERK_VOU (VOU_TYPE, VOU_STATUS, SUB_VOU_TYPE)
tablespace APP_DATA
查看实际的数据访问量:
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID djqtt6d1vgz75, 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 and c.vou_status like '%0%'
Plan hash value: 1521997440
-------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:05.02 | 41155 | 12584 | | | |
|* 1 | COUNT STOPKEY | | 1 | | 1 |00:00:05.02 | 41155 | 12584 | | | |
| 2 | VIEW | | 1 | 268 | 1 |00:00:05.02 | 41155 | 12584 | | | |
|* 3 | SORT ORDER BY STOPKEY | | 1 | 268 | 1 |00:00:05.02 | 41155 | 12584 | 77824 | 77824 | |
|* 4 | HASH JOIN | | 1 | 268 | 301K|00:00:04.71 | 41155 | 12584 | 963K| 963K| 1305K (0)|
| 5 | TABLE ACCESS FULL | COMC_BOX | 1 | 1013 | 1013 |00:00:00.01 | 15 | 0 | | | |
|* 6 | HASH JOIN | | 1 | 21678 | 2100K|00:00:03.65 | 41140 | 12584 | 1000K| 1000K| 1348K (0)|
|* 7 | TABLE ACCESS FULL | AUTO_COMC_CLERK | 1 | 645 | 648 |00:00:00.01 | 30 | 0 | | | |
|* 8 | HASH JOIN | | 1 | 3594 | 301K|00:00:03.01 | 41110 | 12584 | 763K| 763K| 1231K (0)|
|* 9 | TABLE ACCESS BY INDEX ROWID| COMR_CLERK_VOU | 1 | 124 | 538 |00:00:00.11 | 878 | 0 | | | |
|* 10 | INDEX RANGE SCAN | COMR_CLERK_VOU_IDX3 | 1 | 124 | 538 |00:00:00.11 | 361 | 0 | | | |
|* 11 | HASH JOIN ANTI | | 1 | 289 | 831 |00:00:02.80 | 40232 | 12584 | 56M| 4221K| 64M (0)|
|* 12 | TABLE ACCESS FULL | COMR_CIFBINFO | 1 | 28899 | 558K|00:00:00.69 | 28292 | 12584 | | | |
| 13 | INDEX FAST FULL SCAN | CIFACCTNO_IDX2 | 1 | 2110K| 2013K|00:00:00.42 | 11940 | 0 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<2)
3 - filter(ROWNUM<2)
4 - access("B"."BRAN_CODE"="E"."BRAN_CODE" AND "B"."BOX_NO"="C"."BOX_NO" AND "B"."OPER_NO"="E"."OPER_NO")
6 - access("C"."BRAN_CODE"="E"."BRAN_CODE")
7 - filter((INTERNAL_FUNCTION("E"."POST_NO") AND "E"."FLAG"='0'))
8 - access("A"."BANK_CORP_CODE"="C"."BANK_CORP_CODE")
9 - filter("C"."VOU_START_NO" IS NOT NULL)
10 - access("C"."VOU_TYPE"=11 AND "C"."SUB_VOU_TYPE"=0)
filter(("C"."VOU_STATUS" LIKE '%0%' AND "C"."VOU_STATUS" IS NOT NULL AND "C"."SUB_VOU_TYPE"=0 AND "C"."VOU_STATUS" IS NOT NULL))
11 - access("TA"."CUST_NO"="A"."CUST_NO")
12 - filter((SUBSTR(RTRIM("A"."CERT_NO"),18,1) IS NOT NULL AND "A"."CERT_TYPE"='1' AND "A"."VALID_FLAG"='0' AND
"A"."CERT_NO"<>'330721197211217006X' AND "A"."CERT_NO"<>'1302011981101070315'))
55 rows selected.
|* 10 | INDEX RANGE SCAN | COMR_CLERK_VOU_IDX3 | 1 | 124 | 538 |00
访问了538条记录,看到SORT ORDER BY STOPKEY 就肯定有问题里，分页语句不允许出现SORT ORDER BY STOPKEY
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'))
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 2pqcknku6yaby, child number 0
-------------------------------------
select * from (select /*+ index_asc(c
idx_comr_clerk_vou)*/ 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.
Plan hash value: 2236318696
----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.87 | 26008 | 4 |
|* 1 | COUNT STOPKEY | | 1 | | 1 |00:00:00.87 | 26008 | 4 |
| 2 | VIEW | | 1 | 1 | 1 |00:00:00.87 | 26008 | 4 |
| 3 | NESTED LOOPS ANTI | | 1 | 1 | 1 |00:00:00.87 | 26008 | 4 |
| 4 | NESTED LOOPS | | 1 | 100 | 7901 |00:00:00.85 | 18814 | 4 |
| 5 | NESTED LOOPS | | 1 | 1 | 3 |00:00:00.01 | 706 | 4 |
| 6 | NESTED LOOPS | | 1 | 168 | 102 |00:00:00.01 | 537 | 4 |
|* 7 | TABLE ACCESS BY INDEX ROWID| COMR_CLERK_VOU | 1 | 124 | 3 |00:00:00.01 | 500 | 4 |
|* 8 | INDEX RANGE SCAN | IDX_COMR_CLERK_VOU | 1 | 280 | 522 |00:00:00.01 | 5 | 4 |
|* 9 | TABLE ACCESS FULL | COMC_BOX | 3 | 12 | 102 |00:00:00.01 | 37 | 0 |
|* 10 | TABLE ACCESS BY INDEX ROWID | AUTO_COMC_CLERK | 102 | 1 | 3 |00:00:00.01 | 169 | 0 |
|* 11 | INDEX UNIQUE SCAN | AUTO_COMC_CLERK_INX | 102 | 1 | 93 |00:00:00.01 | 105 | 0 |
|* 12 | TABLE ACCESS BY INDEX ROWID | COMR_CIFBINFO | 3 | 100 | 7901 |00:00:00.84 | 18108 | 0 |
|* 13 | INDEX RANGE SCAN | CIFB_IDX2 | 3 | 101 | 7901 |00:00:00.81 | 10319 | 0 |
|* 14 | INDEX RANGE SCAN | CIFACCTNO_IDX2 | 7021 | 2110K| 7020 |00:00:00.02 | 7194 | 0 |
----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<2)
7 - filter(("C"."VOU_STATUS" LIKE '%0%' AND "C"."VOU_STATUS" IS NOT NULL AND "C"."VOU_START_NO" IS NOT NULL AND
"C"."VOU_STATUS" IS NOT NULL))
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")
57 rows selected.
select count(*) from COMR_CLERK_VOU
--268601
select count(*) from COMR_CLERK_VOU C where ("C"."VOU_TYPE"=11 AND "C"."SUB_VOU_TYPE"=0)
--34308
select count(*) from COMR_CLERK_VOU C where ("C"."VOU_TYPE"=11 AND "C"."SUB_VOU_TYPE"=0)
and (("C"."VOU_STATUS" LIKE '%0%' AND "C"."VOU_STATUS" IS NOT NULL AND "C"."VOU_START_NO" IS NOT NULL AND
"C"."VOU_STATUS" IS NOT NULL))
--538
分页语句创建索引:
按where 条件列+order by 列创建索引