原sql：
sql> explain plan for SELECT * 2 FROM crmdw.irsl_renew_user a,crmdw.irsl_renew_tmp_user b,crmdw.on_cor_table t 3 WHERE a.irsl_date = '2015-09-25' 4 AND b.irsl_date = '2015-09-25' 5 AND TO_CHAR (t.end_date,'yyyy-mm-dd') = '9999-12-31' 6 AND b.IS_BW = 1 7 AND b.seach_sign_comp IN (1,2,3) 8 AND a.providerid = b.providerid 9 AND t.providerid = a.providerid; PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 2509217529 --------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time | --------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 2145 | 88436 (4)| 00:17:42 | | 1 | TEMP TABLE TRANSFORMATION | | | | | | | 2 | LOAD AS SELECT | SYS_TEMP_0FD9D66A2_D73DA0B4 | | | | | |* 3 | TABLE ACCESS FULL | ON_COR_TABLE | 6242 | 10M| 34723 (1)| 00:06:57 | | 4 | LOAD AS SELECT | SYS_TEMP_0FD9D66A2_D73DA0B4 | | | | | | 5 | TABLE ACCESS BY INDEX ROWID | IRSL_RENEW_TMP_USER | 10803 | 2342K| 1801 (1)| 00:00:22 | |* 6 | INDEX RANGE SCAN | IND_RENEW_IRSLDATE_IB_SSC | 11359 | | 702 (1)| 00:00:09 | |* 7 | HASH JOIN | | 1 | 2145 | 51912 (7)| 00:10:23 | |* 8 | HASH JOIN | | 15 | 28620 | 51839 (7)| 00:10:23 | | 9 | TABLE ACCESS BY INDEX ROWID | IRSL_RENEW_USER | 292 | 48307 | 51556 (7)| 00:10:19 | | 10 | BITMAP CONVERSION TO ROWIDS | | | | | | | 11 | BITMAP AND | | | | | | | 12 | BITMAP CONVERSION FROM ROWIDS | | | | | | |* 13 | INDEX RANGE SCAN | IDX_RENEW_IRSLDATE | | | 707 (1)| 00:00:09 | | 14 | BITMAP MERGE | | | | | | | 15 | BITMAP KEY ITERATION | | | | | | | 16 | TABLE ACCESS FULL | SYS_TEMP_0FD9D66A2_D73DA0B4 | 1 | 13 | 2 (0)| 00:00:01 | | 17 | BITMAP CONVERSION FROM ROWIDS| | | | | | |* 18 | INDEX RANGE SCAN | IDX_RENEW_PROVIDERID | | | 3 (0)| 00:00:01 | | 19 | BITMAP MERGE | | | | | | | 20 | BITMAP KEY ITERATION | | | | | | | 21 | TABLE ACCESS FULL | SYS_TEMP_0FD9D66A1_D73DA0B4 | 1 | 13 | 2 (0)| 00:00:01 | | 22 | BITMAP CONVERSION FROM ROWIDS| | | | | | |* 23 | INDEX RANGE SCAN | IDX_RENEW_PROVIDERID | | | 3 (0)| 00:00:01 | | 24 | TABLE ACCESS FULL | SYS_TEMP_0FD9D66A1_D73DA0B4 | 6242 | 10M| 282 (1)| 00:00:04 | | 25 | TABLE ACCESS FULL | SYS_TEMP_0FD9D66A2_D73DA0B4 | 10803 | 2500K| 73 (2)| 00:00:01 | --------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter(TO_CHAR(INTERNAL_FUNCTION("T"."END_DATE"),'yyyy-mm-dd')='9999-12-31') 6 - access("B"."IRSL_DATE"='2015-09-25') filter(TO_NUMBER("B"."IS_BW")=1 AND (TO_NUMBER("B"."SEACH_SIGN_COMP")=2 OR TO_NUMBER("B"."SEACH_SIGN_COMP")=3 OR TO_NUMBER("B"."SEACH_SIGN_COMP")=1)) 7 - access("A"."PROVIDERID"="C0") 8 - access("C0"="A"."PROVIDERID") 13 - access("A"."IRSL_DATE"='2015-09-25') 18 - access("A"."PROVIDERID"="C0") 23 - access("A"."PROVIDERID"="C0") Note ----- - star transformation used for this statement需要5、6分钟跑完
改写后：
explain plan for select *
from (select /*+ no_merge */ b.*
from crmdw.irsl_renew_user a,crmdw.irsl_renew_tmp_user b
where a.irsl_date = '2015-09-25'
and a.providerid = b.providerid
and b.irsl_date = '2015-09-25'
and b.IS_BW = 1
and b.seach_sign_comp in (1,3)) ab,crmdw.on_cor_table t
where t.providerid = ab.providerid
and to_char(t.end_date,'yyyy-mm-dd') = '9999-12-31';
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%cpu)| Time |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 68569 (100)| |
|* 1 | HASH JOIN | | 6242 | 171M| 10M| 68569 (1)| 00:13:43 |
|* 2 | TABLE ACCESS FULL | ON_COR_TABLE | 6242 | 10M| | 34723 (1)| 00:06:57 |
| 3 | VIEW | | 19403 | 502M| | 8425 (1)| 00:01:42 |
|* 4 | HASH JOIN | | 19403 | 4566K| 2472K| 8425 (1)| 00:01:42 |
| 5 | TABLE ACCESS BY INDEX ROWID| IRSL_RENEW_TMP_USER | 10803 | 2342K| | 1801 (1)| 00:00:22 |
|* 6 | INDEX RANGE SCAN | IND_RENEW_IRSLDATE_IB_SSC | 11359 | | | 702 (1)| 00:00:09 |
| 7 | TABLE ACCESS BY INDEX ROWID| IRSL_RENEW_USER | 220K| 4088K| | 6177 (1)| 00:01:15 |
|* 8 | INDEX RANGE SCAN | IDX_RENEW_IRSLDATE | 228K| | | 707 (1)| 00:00:09 |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T"."PROVIDERID"="AB"."PROVIDERID")
2 - filter(TO_CHAR(INTERNAL_FUNCTION("T"."END_DATE"),'yyyy-mm-dd')='9999-12-31')
4 - access("A"."PROVIDERID"="B"."PROVIDERID")
6 - access("B"."IRSL_DATE"='2015-09-25')
filter((TO_NUMBER("B"."IS_BW")=1 AND (TO_NUMBER("B"."SEACH_SIGN_COMP")=2 OR
TO_NUMBER("B"."SEACH_SIGN_COMP")=3 OR TO_NUMBER("B"."SEACH_SIGN_COMP")=1)))
8 - access("A"."IRSL_DATE"='2015-09-25')
30秒