explain plan for select * from (select a.*, rownum num from
( select b.sumAmt,b.totalCnt,b.CHL_MER_ID as merId,base.MCHT_NAME as merName from (
select sum(TXN_AMT) as sumAmt,count(1) as totalCnt,CHL_MER_ID from BTH_MER_IN_ACC_DTL
WHERE STLM_DATE <= '20190917' AND STLM_DATE >= '20190901'
AND TXN_AMT >0 GROUP BY CHL_MER_ID order by CHL_MER_ID
) b
left join PBS_MCHT_BASE_INFO base on b.CHL_MER_ID = base.MCHT_ID
left join PBS_MCHT_CONTRACT_INFO con on base.MCHT_ID = con.MCHT_ID
where base.MCHT_AMR_NO = '004562'
and con.CON_STATE = '00' order by b.CHL_MER_ID) a
where rownum <= '66' ) where num >= '0';
select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
Plan hash value: 3164069451
------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 66 | 10494 | 6550 (2)| 00:00:01 | | |
|* 1 | VIEW | | 66 | 10494 | 6550 (2)| 00:00:01 | | |
|* 2 | COUNT STOPKEY | | | | | | | |
| 3 | VIEW | | 77 | 11242 | 6550 (2)| 00:00:01 | | |
| 4 | NESTED LOOPS | | 77 | 6930 | 6550 (2)| 00:00:01 | | |
| 5 | NESTED LOOPS | | 3 | 192 | 5407 (1)| 00:00:01 | | |
|* 6 | TABLE ACCESS BY INDEX ROWID | PBS_MCHT_BASE_INFO | 26 | 1144 | 5400 (1)| 00:00:01 | | |
| 7 | INDEX FULL SCAN | PK_PBS_MCHT_BASE_INFO | 8111 | | 44 (0)| 00:00:01 | | |
|* 8 | TABLE ACCESS BY INDEX ROWID | PBS_MCHT_CONTRACT_INFO | 1 | 20 | 3 (0)| 00:00:01 | | |
|* 9 | INDEX RANGE SCAN | PBS_MCHT_CONTRACT_INFO_INDEX | 1 | | 2 (0)| 00:00:01 | | |
| 10 | VIEW PUSHED PREDICATE | | 1 | 26 | 381 (8)| 00:00:01 | | |
|* 11 | FILTER | | | | | | | |
| 12 | SORT AGGREGATE | | 1 | 32 | | | | |
|* 13 | TABLE ACCESS BY GLOBAL INDEX ROWID| BTH_MER_IN_ACC_DTL | 7 | 224 | 438 (8)| 00:00:01 | 3 | 3 |
|* 14 | INDEX SKIP SCAN | PK_BTH_MER_IN_ACC_DTL | 15 | | 362 (9)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NUM">=0)
2 - filter(ROWNUM<=66)
6 - filter("BASE"."MCHT_AMR_NO"='004562')
8 - filter("CON"."CON_STATE"='00')
9 - access("BASE"."MCHT_ID"="CON"."MCHT_ID")
11 - filter(COUNT(*)>0)
13 - filter(TO_NUMBER("TXN_AMT")>0)
14 - access("STLM_DATE">='20190901' AND "CHL_MER_ID"="BASE"."MCHT_ID" AND "STLM_DATE"<='20190917')
filter("CHL_MER_ID"="BASE"."MCHT_ID")
select sql_id,
version_count,
loads,
hash_value,
address,
plan_hash_value,
old_hash_value,
last_active_child_address
from v$sqlarea a
where a.sql_id='agr2ygd91rkhc'
SQL_ID agr2ygd91rkhc
VERSION_COUNT 10
LOADS 15
HASH_VALUE 1377552908
ADDRESS 0000000D78C4FF98
PLAN_HASH_VALUE 4211696137
OLD_HASH_VALUE 260218348
LAST_ACTIVE_CHILD_ADDRESS 0000000E61DBF5E8
select sql_id,
a.loaded_versions,
hash_value,
address,
a.old_hash_value,
a.plan_hash_value,
a.child_number,
a.child_address
from v$sql a
where a.sql_id='agr2ygd91rkhc'
SQL_ID LOADED_VERSIONS HASH_VALUE ADDRESS OLD_HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER CHILD_ADDRESS
1 agr2ygd91rkhc 1 1377552908 0000000D78C4FF98 260218348 4211696137 0 0000000E61F8CF90
2 agr2ygd91rkhc 1 1377552908 0000000D78C4FF98 260218348 4211696137 1 0000000F27B1EAB0
3 agr2ygd91rkhc 1 1377552908 0000000D78C4FF98 260218348 4211696137 2 0000000D5AF5D568
4 agr2ygd91rkhc 1 1377552908 0000000D78C4FF98 260218348 4211696137 3 0000000D7EC272B0
5 agr2ygd91rkhc 1 1377552908 0000000D78C4FF98 260218348 4211696137 4 0000000E644A4338
6 agr2ygd91rkhc 1 1377552908 0000000D78C4FF98 260218348 4211696137 5 0000000E647017F8
7 agr2ygd91rkhc 1 1377552908 0000000D78C4FF98 260218348 4211696137 6 0000000E8216C9D0
8 agr2ygd91rkhc 1 1377552908 0000000D78C4FF98 260218348 4211696137 7 0000000D0B965268
9 agr2ygd91rkhc 1 1377552908 0000000D78C4FF98 260218348 4211696137 8 0000000E83870D40
10 agr2ygd91rkhc 1 1377552908 0000000D78C4FF98 260218348 4211696137 9 0000000E61DBF5E8
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('agr2ygd91rkhc',9));
PLAN_TABLE_OUTPUT
SQL_ID agr2ygd91rkhc, child number 9
-------------------------------------
select * from (select a.*, rownum num from ( select
b.sumAmt,b.totalCnt,b.CHL_MER_ID as merId,base.MCHT_NAME as merName
from ( select sum(TXN_AMT) as sumAmt,count(1) as
totalCnt,CHL_MER_ID from BTH_MER_IN_ACC_DTL WHERE STLM_DATE <=
:1 AND STLM_DATE >= :2 AND TXN_AMT >0 GROUP BY CHL_MER_ID
order by CHL_MER_ID ) b left join PBS_MCHT_BASE_INFO base on
b.CHL_MER_ID = base.MCHT_ID left join PBS_MCHT_CONTRACT_INFO con on
base.MCHT_ID = con.MCHT_ID where base.MCHT_AMR_NO = :3 and
con.CON_STATE = '00' order by b.CHL_MER_ID) a where rownum <= :4 )
where num >= :5
Plan hash value: 4211696137
-------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 27061 (100)| | | |
|* 1 | VIEW | | 1248 | 193K| 27061 (1)| 00:00:02 | | |
|* 2 | COUNT STOPKEY | | | | | | | |
| 3 | VIEW | | 1248 | 177K| 27061 (1)| 00:00:02 | | |
|* 4 | SORT GROUP BY STOPKEY | | 1248 | 146K| 27061 (1)| 00:00:02 | | |
| 5 | NESTED LOOPS | | 1248 | 146K| 27060 (1)| 00:00:02 | | |
| 6 | NESTED LOOPS | | 1248 | 146K| 27060 (1)| 00:00:02 | | |
| 7 | NESTED LOOPS | | 19 | 1672 | 1334 (1)| 00:00:01 | | |
|* 8 | TABLE ACCESS FULL | PBS_MCHT_BASE_INFO | 19 | 1064 | 1277 (1)| 00:00:01 | | |
|* 9 | TABLE ACCESS BY INDEX ROWID BATCHED| PBS_MCHT_CONTRACT_INFO | 1 | 32 | 3 (0)| 00:00:01 | | |
|* 10 | INDEX RANGE SCAN | PBS_MCHT_CONTRACT_INFO_INDEX | 1 | | 2 (0)| 00:00:01 | | |
|* 11 | INDEX RANGE SCAN | PK_BTH_MER_IN_ACC_DTL | 2 | | 1352 (1)| 00:00:01 | | |
|* 12 | TABLE ACCESS BY GLOBAL INDEX ROWID | BTH_MER_IN_ACC_DTL | 66 | 2112 | 1354 (1)| 00:00:01 | ROWID | ROWID |
-------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NUM">=TO_NUMBER(:5))
2 - filter(ROWNUM<=TO_NUMBER(:4))
4 - filter(ROWNUM<=TO_NUMBER(:4))
8 - filter("BASE"."MCHT_AMR_NO"=:3)
9 - filter("CON"."CON_STATE"='00')
10 - access("BASE"."MCHT_ID"="CON"."MCHT_ID")
11 - access("STLM_DATE">=:2 AND "CHL_MER_ID"="BASE"."MCHT_ID" AND "STLM_DATE"<=:1)
filter("CHL_MER_ID"="BASE"."MCHT_ID")
12 - filter(TO_NUMBER("TXN_AMT")>0)
19. /*+MERGE(TABLE)*/
能够对视图的各个查询进行相应的合并.
select *
from (select a.*, rownum num
from (select /*+merge(b)*/ index( b.sumAmt,
b.totalCnt,
b.CHL_MER_ID as merId,
base.MCHT_NAME as merName
from (select sum(TXN_AMT) as sumAmt,
count(1) as totalCnt,
CHL_MER_ID
from BTH_MER_IN_ACC_DTL
WHERE STLM_DATE <= '20190917'
AND STLM_DATE >= '20190901'
AND TXN_AMT > 0
GROUP BY CHL_MER_ID
order by CHL_MER_ID) b
left join PBS_MCHT_BASE_INFO base
on b.CHL_MER_ID = base.MCHT_ID
left join PBS_MCHT_CONTRACT_INFO con
on base.MCHT_ID = con.MCHT_ID
where base.MCHT_AMR_NO = '004562'
and con.CON_STATE = '00'
order by b.CHL_MER_ID) a
where rownum <= '66')
where num >= '0'
explain plan for select *
from (select a.*, rownum num
from (select /*+merge(b)*/ b.sumAmt,
b.totalCnt,
b.CHL_MER_ID as merId,
base.MCHT_NAME as merName
from (select /*+ index(BTH_MER_IN_ACC_DTL,PK_BTH_MER_IN_ACC_DTL)*/ sum(TXN_AMT) as sumAmt,
count(1) as totalCnt,
CHL_MER_ID
from BTH_MER_IN_ACC_DTL
WHERE STLM_DATE <= '20190917'
AND STLM_DATE >= '20190901'
AND TXN_AMT > 0
GROUP BY CHL_MER_ID
order by CHL_MER_ID) b
left join PBS_MCHT_BASE_INFO base
on b.CHL_MER_ID = base.MCHT_ID
left join PBS_MCHT_CONTRACT_INFO con
on base.MCHT_ID = con.MCHT_ID
where base.MCHT_AMR_NO = '004562'
and con.CON_STATE = '00'
order by b.CHL_MER_ID) a
where rownum <= '66')
where num >= '0';
select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
Plan hash value: 2242099092
-------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 66 | 10494 | 1418K (1)| 00:00:56 | | |
|* 1 | VIEW | | 66 | 10494 | 1418K (1)| 00:00:56 | | |
|* 2 | COUNT STOPKEY | | | | | | | |
| 3 | VIEW | | 4632 | 660K| 1418K (1)| 00:00:56 | | |
|* 4 | SORT GROUP BY STOPKEY | | 4632 | 542K| 1418K (1)| 00:00:56 | | |
| 5 | NESTED LOOPS | | 4632 | 542K| 1418K (1)| 00:00:56 | | |
| 6 | NESTED LOOPS | | 4632 | 542K| 1418K (1)| 00:00:56 | | |
| 7 | NESTED LOOPS | | 26 | 2288 | 1355 (1)| 00:00:01 | | |
|* 8 | TABLE ACCESS FULL | PBS_MCHT_BASE_INFO | 26 | 1456 | 1277 (1)| 00:00:01 | | |
|* 9 | TABLE ACCESS BY INDEX ROWID BATCHED| PBS_MCHT_CONTRACT_INFO | 1 | 32 | 3 (0)| 00:00:01 | | |
|* 10 | INDEX RANGE SCAN | PBS_MCHT_CONTRACT_INFO_INDEX | 1 | | 2 (0)| 00:00:01 | | |
|* 11 | INDEX RANGE SCAN | PK_BTH_MER_IN_ACC_DTL | 15 | | 54432 (1)| 00:00:03 | | |
|* 12 | TABLE ACCESS BY GLOBAL INDEX ROWID | BTH_MER_IN_ACC_DTL | 178 | 5696 | 54508 (1)| 00:00:03 | 3 | 3 |
-------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NUM">=0)
2 - filter(ROWNUM<=66)
4 - filter(ROWNUM<=66)
8 - filter("BASE"."MCHT_AMR_NO"='004562')
9 - filter("CON"."CON_STATE"='00')
10 - access("BASE"."MCHT_ID"="CON"."MCHT_ID")
11 - access("STLM_DATE">='20190901' AND "CHL_MER_ID"="BASE"."MCHT_ID" AND "STLM_DATE"<='20190917')
filter("CHL_MER_ID"="BASE"."MCHT_ID")
12 - filter(TO_NUMBER("TXN_AMT")>0)
select * from dba_indexes a where a.index_name='PK_BTH_MER_IN_ACC_DTL' 全局索引