本帖最后由 flzhang 于 2015-10-15 10:59 编辑
如下SQL为什么三个表放一起连接 RW_BI_FT_LT就走全表扫描,而改写成只有T1和T2连接后,RW_BI_FT_LT就能走索引呢?
改写前
SELECT
T1.SERL_NO
,T1.SI_YMD
,T1.ST_YMD
,T1.ST2_YMD
,T1.SO_YMD
,T1.ACTU_DT FOTA_DT
,T2.SALE_YMD CHANEL_SO
,T2.REGI_DT CHANEL_SO_REGI_DT
FROMMCS_BI.RW_BI_FT_LT T1
,MCS_HQ.HI_SALE_CHNL T2
,MCS_HQ_READ.UP_LOAD_SERL10 T3
WHERET1.SERL_NO = T2.SERL_NO(+)
AND T3.SERL_NO = T1.SERL_NO(+);
执行计划
Execution Plan
----------------------------------------------------------
Plan hash value: 3150097070
--------------------------------------------------------------------------------
-----------------------------------------
| Id | Operation | Name | Rows | Bytes
| Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------
-----------------------------------------
| 0 | SELECT STATEMENT | | 63 | 8316
| 257K (1)| 00:51:31 | | |
| 1 | NESTED LOOPS OUTER | | 63 | 8316
| 257K (1)| 00:51:31 | | |
|* 2 | HASH JOIN OUTER | | 63 | 6237
| 257K (1)| 00:51:30 | | |
| 3 | TABLE ACCESS FULL | UP_LOAD_SERL10 | 63 | 3276
| 3 (0)| 00:00:01 | | |
| 4 | PARTITION RANGE ALL | | 38M| 1725M
| 257K (1)| 00:51:27 | 1 | 122 |
| 5 | TABLE ACCESS FULL | RW_BI_FT_LT | 38M| 1725M
| 257K (1)| 00:51:27 | 1 | 122 |
| 6 | TABLE ACCESS BY GLOBAL INDEX ROWID| HI_SALE_CHNL | 1 | 33
| 2 (0)| 00:00:01 | ROWID | ROWID |
|* 7 | INDEX UNIQUE SCAN | UX_HI_SALE_CHNL_1 | 1 |
| 1 (0)| 00:00:01 | | |
--------------------------------------------------------------------------------
-----------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T3"."SERL_NO"="T1"."SERL_NO"(+))
7 - access("T1"."SERL_NO"="T2"."SERL_NO"(+))
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
1171799 consistent gets
1171265 physical reads
196 redo size
3070 bytes sent via SQL*Net to client
268 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
63 rows processed
实际执行计划
Plan hash value: 3150097070
----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 63 |00:01:35.15 | 1173K| 1173K|
| 1 | NESTED LOOPS OUTER | | 1 | 63 | 63 |00:01:35.15 | 1173K| 1173K|
|* 2 | HASH JOIN OUTER | | 1 | 63 | 63 |00:01:34.96 | 1173K| 1173K|
| 3 | TABLE ACCESS FULL | UP_LOAD_SERL10 | 1 | 63 | 63 |00:00:00.01 | 7 | 0 |
| 4 | PARTITION RANGE ALL | | 1 | 38M| 38M|00:01:13.83 | 1173K| 1173K|
| 5 | TABLE ACCESS FULL | RW_BI_FT_LT | 122 | 38M| 38M|00:01:03.25 | 1173K| 1173K|
| 6 | TABLE ACCESS BY GLOBAL INDEX ROWID| HI_SALE_CHNL | 63 | 1 | 63 |00:00:00.19 | 191 | 24 |
|* 7 | INDEX UNIQUE SCAN | UX_HI_SALE_CHNL_1 | 63 | 1 | 63 |00:00:00.19 | 128 | 24 |
----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T3"."SERL_NO"="T1"."SERL_NO")
7 - access("T1"."SERL_NO"="T2"."SERL_NO")
Note
-----
- dynamic sampling used for this statement (level=2)
改写后SQL
WITH T_MAST AS (
SELECTT1.SERL_NO
,T1.SI_YMD
,T1.ST_YMD
,T1.ST2_YMD
,T1.SO_YMD
,T1.ACTU_DT FOTA_DT
,T2.SALE_YMD CHANEL_SO
,T2.REGI_DT CHANEL_SO_REGI_DT
FROMMCS_BI.RW_BI_FT_LT T1
,MCS_HQ.HI_SALE_CHNL T2
WHERET1.SERL_NO = T2.SERL_NO(+)
)
SELECTT_MAST.*
FROMT_MAST,
MCS_HQ_READ.UP_LOAD_SERL10 T2
WHERE T_MAST.SERL_NO(+) = T2.SERL_NO;
对应执行计划
Execution Plan
----------------------------------------------------------
Plan hash value: 2281879921
--------------------------------------------------------------------------------
-------------------------------------------
| Id | Operation | Name | Rows | Byte
s | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------
-------------------------------------------
| 0 | SELECT STATEMENT | | 63 | 768
6 | 25779 (1)| 00:05:10 | | |
| 1 | NESTED LOOPS OUTER | | 63 | 768
6 | 25779 (1)| 00:05:10 | | |
| 2 | TABLE ACCESS FULL | UP_LOAD_SERL10 | 63 | 327
6 | 2 (0)| 00:00:01 | | |
| 3 | VIEW PUSHED PREDICATE | | 1 | 7
0 | 409 (0)| 00:00:05 | | |
| 4 | NESTED LOOPS OUTER | | 1 | 8
0 | 409 (0)| 00:00:05 | | |
| 5 | PARTITION RANGE ALL | | 1 | 4
7 | 407 (0)| 00:00:05 | 1 | 122 |
| 6 | TABLE ACCESS BY LOCAL INDEX ROWID| RW_BI_FT_LT | 1 | 4
7 | 407 (0)| 00:00:05 | 1 | 122 |
|* 7 | INDEX SKIP SCAN | PK_RW_BI_FT_LT | 1 |
| 405 (0)| 00:00:05 | 1 | 122 |
| 8 | TABLE ACCESS BY GLOBAL INDEX ROWID| HI_SALE_CHNL | 1 | 3
3 | 2 (0)| 00:00:01 | ROWID | ROWID |
|* 9 | INDEX UNIQUE SCAN | UX_HI_SALE_CHNL_1 | 1 |
| 1 (0)| 00:00:01 | | |
--------------------------------------------------------------------------------
-------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("T1"."SERL_NO"="T2"."SERL_NO")
filter("T1"."SERL_NO"="T2"."SERL_NO")
9 - access("T2"."SERL_NO"(+)="T2"."SERL_NO")
filter("T1"."SERL_NO"="T2"."SERL_NO"(+))
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
20 recursive calls
2 db block gets
96259 consistent gets
2620 physical reads
0 redo size
2920 bytes sent via SQL*Net to client
268 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
实际执行计划
Plan hash value: 2281879921
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 63 |00:00:23.39 | 96374 | 2705 |
| 1 | NESTED LOOPS OUTER | | 1 | 63 | 63 |00:00:23.39 | 96374 | 2705 |
| 2 | TABLE ACCESS FULL | UP_LOAD_SERL10 | 1 | 63 | 63 |00:00:00.01 | 7 | 0 |
| 3 | VIEW PUSHED PREDICATE | | 63 | 1 | 63 |00:00:23.39 | 96367 | 2705 |
| 4 | NESTED LOOPS OUTER | | 63 | 1 | 63 |00:00:23.39 | 96367 | 2705 |
| 5 | PARTITION RANGE ALL | | 63 | 1 | 63 |00:00:23.17 | 96183 | 2679 |
| 6 | TABLE ACCESS BY LOCAL INDEX ROWID| RW_BI_FT_LT | 7686 | 1 | 63 |00:00:23.16 | 96183 | 2679 |
|* 7 | INDEX SKIP SCAN | PK_RW_BI_FT_LT | 7686 | 1 | 63 |00:00:22.93 | 96121 | 2632 |
| 8 | TABLE ACCESS BY GLOBAL INDEX ROWID| HI_SALE_CHNL | 63 | 1 | 63 |00:00:00.22 | 184 | 26 |
|* 9 | INDEX UNIQUE SCAN | UX_HI_SALE_CHNL_1 | 63 | 1 | 63 |00:00:00.22 | 121 | 26 |
------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("T1"."SERL_NO"="T2"."SERL_NO")
filter("T1"."SERL_NO"="T2"."SERL_NO")
9 - access("T2"."SERL_NO"="T2"."SERL_NO")
filter("T1"."SERL_NO"="T2"."SERL_NO")
Note
-----
- dynamic sampling used for this statement (level=2)