.查看SQL信息:
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;
对应的执行计划为:
Plan hash value: 2629361789
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 85 | | 16722 (2)| 00:03:21 |
|* 1 | COUNT STOPKEY | | | | | | |
| 2 | VIEW | | 266 | 22610 | | 16722 (2)| 00:03:21 |
|* 3 | SORT ORDER BY STOPKEY | | 266 | 68096 | | 16722 (2)| 00:03:21 |
|* 4 | HASH JOIN | | 266 | 68096 | | 16721 (2)| 00:03:21 |
| 5 | TABLE ACCESS FULL | COMC_BOX | 1013 | 23299 | | 5 (0)| 00:00:01 |
|* 6 | HASH JOIN | | 21563 | 4906K| | 16715 (2)| 00:03:21 |
|* 7 | TABLE ACCESS FULL | AUTO_COMC_CLERK | 642 | 19902 | | 9 (0)| 00:00:01 |
|* 8 | HASH JOIN | | 3594 | 708K| | 16705 (2)| 00:03:21 |
|* 9 | TABLE ACCESS FULL | COMR_CLERK_VOU | 124 | 11904 | | 1278 (2)| 00:00:16 |
|* 10 | HASH JOIN ANTI | | 289 | 30634 | 2488K| 15427 (2)| 00:03:06 |
|* 11 | TABLE ACCESS FULL | COMR_CIFBINFO | 28899 | 2144K| | 7735 (2)| 00:01:33 |
| 12 | 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_TYPE"=11 AND "C"."VOU_STATUS" LIKE '%0%' AND "C"."VOU_STATUS" IS NOT
NULL AND "C"."SUB_VOU_TYPE"=0)
10 - access("TA"."CUST_NO"="A"."CUST_NO")
11 - 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')
此SQL是典型的分页SQL,按照COMR_CLERK_VOU的to_char(c.vou_start_no + instr(c.vou_status, '0') - 1, 'fm00000000') as vou进行排序,这个走全表扫描,则会对整个表进行排序在取一条,实际上取一条不需要对整个表排序,分页语句不能出现SORT ORDER BY STOPKEY。
3.创建索引和添加hints：
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'))
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.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;
此时查看执行计划:
Plan hash value: 2236318696
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 85 | 790 (1)| 00:00:10 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 1 | 85 | 790 (1)| 00:00:10 |
| 3 | NESTED LOOPS ANTI | | 1 | 256 | 790 (1)| 00:00:10 |
| 4 | NESTED LOOPS | | 100 | 22600 | 589 (1)| 00:00:08 |
| 5 | NESTED LOOPS | | 1 | 150 | 361 (1)| 00:00:05 |
| 6 | NESTED LOOPS | | 168 | 19992 | 284 (1)| 00:00:04 |
|* 7 | TABLE ACCESS BY INDEX ROWID| COMR_CLERK_VOU | 124 | 11904 | 232 (0)| 00:00:03 |
|* 8 | INDEX RANGE SCAN | IDX_COMR_CLERK_VOU | 280 | | 3 (0)| 00:00:01 |
|* 9 | TABLE ACCESS FULL | COMC_BOX | 12 | 276 | 4 (0)| 00:00:01 |
|* 10 | TABLE ACCESS BY INDEX ROWID | AUTO_COMC_CLERK | 1 | 31 | 1 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | AUTO_COMC_CLERK_INX | 1 | | 0 (0)| 00:00:01 |
|* 12 | TABLE ACCESS BY INDEX ROWID | COMR_CIFBINFO | 100 | 7600 | 229 (1)| 00:00:03 |
|* 13 | INDEX RANGE SCAN | CIFB_IDX2 | 101 | | 128 (2)| 00:00:02 |
|* 14 | INDEX RANGE SCAN | CIFACCTNO_IDX2 | 2110K| 60M| 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<2)
7 - filter("C"."VOU_STATUS" LIKE '%0%' 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(("E"."POST_NO"='12002' OR "E"."POST_NO"='12402') 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")
此时SORT ORDER BY STOPKEY消失,通过索引访问最理想的情况只需要访问一条数据就停止索引扫描,这样节省了大量的排序工作。