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; ---再运行SQL
select * 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(*)
----------
1
SQL> 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(*)
----------
1
SQL> 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) and
t1.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.