explain plan for select c.oper_no, a.passbook_no, g.acct_no
from auto_savb_acct g,
auto_savb_acct a,
(select * from comc_branch where substr(ctrl_bit,7,1)='0' )b,
auto_comc_clerk c,
comc_clerk_post d
where a.media_type = '3'
and substr(a.draw_mode, 1, 1) = '1'
and substr(a.status, 1, 1) = '0'
and g.media_type = '1'
and a.flag = '0'
and g.flag = '0'
and c.flag = '0'
and a.cur_code = 1
and g.cur_code = 1
and g.open_bran_code = c.bran_code
and a.open_bran_code = c.bran_code
and substr(g.status, 1, 1) = '0'
and c.post_no = d.post_no
and d.trans_code = '104106'
and c.bran_code=b.bran_code
and rownum < 2 ;
select * from table(dbms_xplan.display());
Plan hash value: 3242562701
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 187 | 1593 (1)| 00:00:20 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | NESTED LOOPS | | 1 | 187 | 1593 (1)| 00:00:20 |
| 3 | NESTED LOOPS | | 1 | 175 | 1593 (1)| 00:00:20 |
|* 4 | HASH JOIN | | 3 | 429 | 1590 (1)| 00:00:20 |
|* 5 | HASH JOIN | | 7 | 616 | 800 (1)| 00:00:10 |
|* 6 | TABLE ACCESS FULL | AUTO_SAVB_ACCT | 1 | 57 | 790 (1)| 00:00:10 |
|* 7 | TABLE ACCESS FULL | AUTO_COMC_CLERK | 814 | 25234 | 9 (0)| 00:00:01 |
|* 8 | TABLE ACCESS FULL | AUTO_SAVB_ACCT | 31 | 1705 | 790 (1)| 00:00:10 |
|* 9 | TABLE ACCESS BY INDEX ROWID| COMC_BRANCH | 1 | 32 | 1 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | BRAN_IDX | 1 | | 0 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | POST_IDX | 1 | 12 | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<2)
4 - access("G"."OPEN_BRAN_CODE"="C"."BRAN_CODE")
5 - access("A"."OPEN_BRAN_CODE"="C"."BRAN_CODE")
6 - filter(SUBSTR("A"."DRAW_MODE",1,1)='1' AND SUBSTR("A"."STATUS",1,1)='0' AND
"A"."MEDIA_TYPE"='3' AND "A"."CUR_CODE"=1 AND "A"."FLAG"='0')
7 - filter("C"."FLAG"='0')
8 - filter(SUBSTR("G"."STATUS",1,1)='0' AND "G"."MEDIA_TYPE"='1' AND "G"."CUR_CODE"=1
AND "G"."FLAG"='0')
9 - filter(SUBSTR("CTRL_BIT",7,1)='0')
10 - access("C"."BRAN_CODE"="COMC_BRANCH"."BRAN_CODE")
11 - access("D"."POST_NO"=TO_NUMBER("C"."POST_NO") AND "D"."TRANS_CODE"='104106')
select count(*) from AUTO_SAVB_ACCT where SUBSTR("A"."DRAW_MODE",1,1)='1' AND SUBSTR("A"."STATUS",1,1)='0' AND
"A"."MEDIA_TYPE"='3' AND "A"."CUR_CODE"=1 AND "A"."FLAG"='0')
--4917
SQL> select count(*) from AUTO_SAVB_ACCT ;
COUNT(*)
----------
52132
SQL> select count(*) from AUTO_COMC_CLERK ;
COUNT(*)
----------
848
此时HASH JOIN的驱动表为AUTO_SAVB_ACCT,这个相比被驱动表AUTO_COMC_CLERK体积大,交换驱动表后
explain plan for select /*+ leading(c) */ c.oper_no, a.passbook_no, g.acct_no
from auto_savb_acct g,
auto_savb_acct a,
(select * from comc_branch where substr(ctrl_bit,7,1)='0' )b,
auto_comc_clerk c,
comc_clerk_post d
where a.media_type = '3'
and substr(a.draw_mode, 1, 1) = '1'
and substr(a.status, 1, 1) = '0'
and g.media_type = '1'
and a.flag = '0'
and g.flag = '0'
and c.flag = '0'
and a.cur_code = 1
and g.cur_code = 1
and g.open_bran_code = c.bran_code
and a.open_bran_code = c.bran_code
and substr(g.status, 1, 1) = '0'
and c.post_no = d.post_no
and d.trans_code = '104106'
and c.bran_code=b.bran_code
and rownum < 2 ;
Plan hash value: 2775533374
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 187 | 1593 (1)| 00:00:20 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | NESTED LOOPS | | 1 | 187 | 1593 (1)| 00:00:20 |
| 3 | NESTED LOOPS | | 1 | 175 | 1593 (1)| 00:00:20 |
|* 4 | HASH JOIN | | 3 | 429 | 1590 (1)| 00:00:20 |
|* 5 | HASH JOIN | | 7 | 616 | 800 (1)| 00:00:10 |
|* 6 | TABLE ACCESS FULL | AUTO_COMC_CLERK | 814 | 25234 | 9 (0)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | AUTO_SAVB_ACCT | 1 | 57 | 790 (1)| 00:00:10 |
|* 8 | TABLE ACCESS FULL | AUTO_SAVB_ACCT | 33 | 1815 | 790 (1)| 00:00:10 |
|* 9 | TABLE ACCESS BY INDEX ROWID| COMC_BRANCH | 1 | 32 | 1 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | BRAN_IDX | 1 | | 0 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | POST_IDX | 1 | 12 | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<2)
4 - access("G"."OPEN_BRAN_CODE"="C"."BRAN_CODE")
5 - access("A"."OPEN_BRAN_CODE"="C"."BRAN_CODE")
6 - filter("C"."FLAG"='0')
7 - filter(SUBSTR("A"."DRAW_MODE",1,1)='1' AND SUBSTR("A"."STATUS",1,1)='0' AND
"A"."MEDIA_TYPE"='3' AND "A"."CUR_CODE"=1 AND "A"."FLAG"='0')
8 - filter(SUBSTR("G"."STATUS",1,1)='0' AND "G"."MEDIA_TYPE"='1' AND "G"."CUR_CODE"=1
AND "G"."FLAG"='0')
9 - filter(SUBSTR("CTRL_BIT",7,1)='0')
10 - access("C"."BRAN_CODE"="COMC_BRANCH"."BRAN_CODE")
11 - access("D"."POST_NO"=TO_NUMBER("C"."POST_NO") AND "D"."TRANS_CODE"='104106')