filter 优化
来源：互联网 发布：手机电子书制作软件 编辑：程序博客网 时间：2024/06/04 19:13
SELECT COUNT(*) FROM T18_TASKLIST t1 WHERE TASKTYPE || '-' || BUSINESSKEY || '-' || GRANULARITY NOT IN (SELECT TASKTYPE || '-' || BUSINESSKEY || '-' || GRANULARITY FROM T18_TASK_FACT t2 WHERE STATISTICDATE = '2017-01-13') and t1.GRANULARITY not in ('4', '5', '7');---高级执行计划:11G：set linesize 200;set pagesize 200;alter session set statistics_level=all; ---再运行SQLselect * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));SQL> SELECT COUNT(*) FROM T18_TASKLIST t1 WHERE TASKTYPE || '-' || BUSINESSKEY || '-' || GRANULARITY NOT IN (SELECT TASKTYPE || '-' || BUSINESSKEY || '-' || GRANULARITY FROM T18_TASK_FACT t2 WHERE STATISTICDATE = '2017-01-13') and t1.GRANULARITY not in ('4', '5', '7'); 2 3 4 5 6 7 COUNT(*)---------- 1SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID 7v213p9c6vq3a, child number 0-------------------------------------SELECT COUNT(*) FROM T18_TASKLIST t1 WHERE TASKTYPE || '-' ||BUSINESSKEY || '-' || GRANULARITY NOT IN (SELECT TASKTYPE || '-'|| BUSINESSKEY || '-' || GRANULARITY FROM T18_TASK_FACT t2 WHERE STATISTICDATE = '2017-01-13') and t1.GRANULARITY not in('4', '5', '7')Plan hash value: 2085375507-------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |-------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 1 |00:01:59.88 | 6558K| 1 || 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:01:59.88 | 6558K| 1 ||* 2 | FILTER | | 1 | | 1 |00:01:59.88 | 6558K| 1 ||* 3 | INDEX FAST FULL SCAN| PK_T18_TASKLIST | 1 | 690 | 692 |00:00:00.02 | 29 | 1 ||* 4 | TABLE ACCESS FULL | T18_TASK_FACT | 692 | 2 | 691 |00:01:59.84 | 6558K| 0 |-------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - filter( IS NULL) 3 - filter(("T1"."GRANULARITY"<>'4' AND "T1"."GRANULARITY"<>'5' AND "T1"."GRANULARITY"<>'7')) 4 - filter(("STATISTICDATE"='2017-01-13' AND LNNVL(:B1||'-'||:B2||'-'||:B3<>"TASKTYPE"||'-'||"BUSI NESSKEY"||'-'||"GRANULARITY")))28 rows selected.T18_TASK_FACT 大小为80MB，访问了692次SQL> explain plan for SELECT COUNT(*) 2 FROM T18_TASKLIST t1 WHERE TASKTYPE || '-' || BUSINESSKEY || '-' || GRANULARITY NOT IN (SELECT TASKTYPE || '-' || BUSINESSKEY || '-' || GRANULARITY FROM T18_TASK_FACT t2 WHERE STATISTICDATE = '2017-01-13') and t1.GRANULARITY not in ('4', '5', '7'); 3 4 5 6 7 Explained.SQL> select * from table(dbms_xplan.display());PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Plan hash value: 2085375507------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 25 | 2086 (1)| 00:00:26 || 1 | SORT AGGREGATE | | 1 | 25 | | ||* 2 | FILTER | | | | | ||* 3 | INDEX FAST FULL SCAN| PK_T18_TASKLIST | 690 | 17250 | 3 (0)| 00:00:01 ||* 4 | TABLE ACCESS FULL | T18_TASK_FACT | 2 | 72 | 6 (0)| 00:00:01 |------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - filter( NOT EXISTS (SELECT 0 FROM "T18_TASK_FACT" "T2" WHERE "STATISTICDATE"='2017-01-13' AND LNNVL(:B1||'-'||:B2||'-'||:B3<>"TASKTYPE"||'-'||" BUSINESSKEY"||'-'||"GRANULARITY"))) 3 - filter("T1"."GRANULARITY"<>'4' AND "T1"."GRANULARITY"<>'5' AND "T1"."GRANULARITY"<>'7') 4 - filter("STATISTICDATE"='2017-01-13' AND LNNVL(:B1||'-'||:B2||'-'||:B3<>"TASKTYPE"||'-'||"BUSINESSKEY"||'-'||"GRANULARITY") )23 rows selected.加上索引:create index T18_TASK_FACT_IDX1 on T18_TASK_FACT(STATISTICDATE) tablespace TSIND01改成with as 后:SQL> WITH D as 2 (SELECT /*+ materialize*/ TASKTYPE, BUSINESSKEY, GRANULARITY FROM T18_TASK_FACT t2 WHERE t2.STATISTICDATE = '2017-01-13')select COUNT(*) FROM T18_TASKLIST t1 WHERE TASKTYPE || '-' || BUSINESSKEY || '-' || GRANULARITY NOT IN (select TASKTYPE || '-' || BUSINESSKEY || '-' || GRANULARITY from D) and t1.GRANULARITY not in ('4', '5', '7'); 3 4 5 6 7 8 9 COUNT(*)---------- 1SQL> SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID cm1dhrhqcp04y, child number 0------------------------------------- WITH D as (SELECT /*+ materialize*/ TASKTYPE, BUSINESSKEY,GRANULARITY FROM T18_TASK_FACT t2 WHERE t2.STATISTICDATE ='2017-01-13') select COUNT(*) FROM T18_TASKLIST t1 WHERE TASKTYPE ||'-' || BUSINESSKEY || '-' || GRANULARITY NOT IN (select TASKTYPE|| '-' || BUSINESSKEY || '-' || GRANULARITY from D) andt1.GRANULARITY not in ('4', '5', '7')Plan hash value: 3694718676-----------------------------------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem |-----------------------------------------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.78 | 12303 | 3 | 3 | | | || 1 | TEMP TABLE TRANSFORMATION | | 1 | | 1 |00:00:00.78 | 12303 | 3 | 3 | | | || 2 | LOAD AS SELECT | | 1 | | 0 |00:00:00.29 | 10119 | 0 | 3 | 270K| 270K| 270K (0)||* 3 | TABLE ACCESS FULL | T18_TASK_FACT | 1 | 679 | 691 |00:00:00.19 | 10113 | 0 | 0 | | | || 4 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.50 | 2181 | 3 | 0 | | | ||* 5 | FILTER | | 1 | | 1 |00:00:00.50 | 2181 | 3 | 0 | | | ||* 6 | INDEX FAST FULL SCAN | PK_T18_TASKLIST | 1 | 690 | 692 |00:00:00.01 | 29 | 0 | 0 | | | ||* 7 | VIEW | | 692 | 679 | 691 |00:00:00.49 | 2152 | 3 | 0 | | | || 8 | TABLE ACCESS FULL | SYS_TEMP_0FD9D676D_C5B66925 | 692 | 679 | 239K|00:00:00.07 | 2152 | 3 | 0 | | | |-----------------------------------------------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 3 - filter("T2"."STATISTICDATE"='2017-01-13') 5 - filter( IS NULL) 6 - filter(("T1"."GRANULARITY"<>'4' AND "T1"."GRANULARITY"<>'5' AND "T1"."GRANULARITY"<>'7')) 7 - filter(LNNVL(:B1||'-'||:B2||'-'||:B3<>"TASKTYPE"||'-'||"BUSINESSKEY"||'-'||"GRANULARITY"))33 rows selected.SQL> explain plan for WITH D as 2 (SELECT /*+ materialize*/ TASKTYPE, BUSINESSKEY, GRANULARITY FROM T18_TASK_FACT t2 WHERE t2.STATISTICDATE = '2017-01-13')select COUNT(*) FROM T18_TASKLIST t1 WHERE TASKTYPE || '-' || BUSINESSKEY || '-' || GRANULARITY NOT IN (select TASKTYPE || '-' || BUSINESSKEY || '-' || GRANULARITY from D) and t1.GRANULARITY not in ('4', '5', '7'); 3 4 5 6 7 8 9 Explained.SQL> select * from table(dbms_xplan.display());PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Plan hash value: 3694718676----------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 25 | 3798 (1)| 00:00:46 || 1 | TEMP TABLE TRANSFORMATION | | | | | || 2 | LOAD AS SELECT | SYS_TEMP_0FD9D676E_C5B66925 | | | | ||* 3 | TABLE ACCESS FULL | T18_TASK_FACT | 679 | 24444 | 2757 (1)| 00:00:34 || 4 | SORT AGGREGATE | | 1 | 25 | | ||* 5 | FILTER | | | | | ||* 6 | INDEX FAST FULL SCAN | PK_T18_TASKLIST | 690 | 17250 | 3 (0)| 00:00:01 ||* 7 | VIEW | | 679 | 42777 | 3 (0)| 00:00:01 || 8 | TABLE ACCESS FULL | SYS_TEMP_0FD9D676E_C5B66925 | 679 | 16975 | 3 (0)| 00:00:01 |----------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 3 - filter("T2"."STATISTICDATE"='2017-01-13') 5 - filter( NOT EXISTS (SELECT 0 FROM (SELECT /*+ CACHE_TEMP_TABLE ("T1") */ "C0" "TASKTYPE","C1" "BUSINESSKEY","C2" "GRANULARITY" FROM "SYS"."SYS_TEMP_0FD9D676E_C5B66925" "T1") "D" WHERE LNNVL(:B1||'-'||:B2||'-'||:B3<>"TASKTYPE"||'-'||"BUSINESSKEY"||'-'||"GRANULARITY"))) 6 - filter("T1"."GRANULARITY"<>'4' AND "T1"."GRANULARITY"<>'5' AND "T1"."GRANULARITY"<>'7') 7 - filter(LNNVL(:B1||'-'||:B2||'-'||:B3<>"TASKTYPE"||'-'||"BUSINESSKEY"||'-'||"GRANULARITY"))25 rows selected. 0 0
- FILTER优化
- filter 优化
- FILTER 优化
- 操作FILTER 的优化
- 算子优化 filter + coalesce
- 利用WITH AS 优化FILTER
- 利用FILTER特性优化SQL
- 使用with as优化sql解决filter
- 使用gzip优化web应用(filter实现)
- 第四节 常用组件 之 Filter 优化
- 一次性能优化将filter转换
- 使用gzip优化web应用(filter实现)
- 使用gzip优化web应用(filter实现)
- 使用gzip优化web应用(filter实现)
- 使用gzip优化web应用(filter实现)
- 使用gzip优化web应用(filter实现)
- Filter
- Filter
- 对 Select 的各种操作（JQuery）
- hive时间操作函数
- 简单讨论一下 jQuery 事件
- 游戏核心之固定流水线
- Java 使用httpclient Post与cxf 发布的Webservice通信
- filter 优化
- JS —— 轮播图中的缓动函数的封装
- SpringMVC ----从 配置文件开始
- JavaScript的事件机制
- mysql安装配置
- STL源码剖析学习笔记（二）
- Leetcode 112. Path Sum
- leetcode - 1 two sum
- Jquery选择器篇导图
