ORACLE:执行计划中BITMAP AND导致sql慢
来源：互联网 发布：淘宝网的收入来源 编辑：程序博客网 时间：2024/05/30 02:22
原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, 2, 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秒 0 0
- SQlite数据库的增删改查
- Podfile文件详解
- 中图法分类号
- sql yog 连接不上
- [Eclipse] - 解决"Java was started but returned exit code = 13"问题
- ORACLE:执行计划中BITMAP AND导致sql慢
- (转)常见的HTTP状态码(HTTP Status Code)说明
- (3)linux系统的目录结构
- android数据库：增删改查
- MySql常用命令总结
- android SharedPeferences List<String>
- Android中的onActivityResult和setResult方法的使用
- listview的item中的子条目选中(递归选中)
- Jsoup解析HTML实例及文档方法详解
