SQL开发知识：Oracle 12c sql 查询转换之临时表转换详解
前言
大家都知道在12CR2中出现一种新的查询转换技术临时表转换， 在下面的例子中，数据库对customers表上的子查询结果物化到一个临时表中:
SQL> show parameter star_transformation_enabled
star_transformation_enabled string FALSE
SQL> alter session set star_transformation_enabled=’true’;
Session altered.
SQL> SELECT c.cust_city,
2 t.calendar_quarter_desc,
3 SUM(s.amount_sold) sales_amount
4 FROM sales s,
5 times t,
6 customers c,
7 channels ch
8 WHERE s.time_id = t.time_id
9 AND s.cust_id = c.cust_id
10 AND s.channel_id = ch.channel_id
11 AND c.cust_state_province = ‘CA’
12 AND ch.channel_desc = ‘Internet’
13 AND t.calendar_quarter_desc IN (‘1999-01′,’1999-02’)
14 GROUP BY c.cust_city, t.calendar_quarter_desc;
Montara 1999-02 1618.01
Pala 1999-01 3263.93
Cloverdale 1999-01 52.64
Cloverdale 1999-02 266.28
San Francisco 1999-01 3058.27
San Mateo 1999-01 8754.59
Los Angeles 1999-01 1886.19
San Mateo 1999-02 21399.42
Pala 1999-02 936.62
El Sobrante 1999-02 3744.03
El Sobrante 1999-01 5392.34
Quartzhill 1999-01 987.3
Legrand 1999-01 26.32
Pescadero 1999-01 26.32
Arbuckle 1999-02 241.2
Quartzhill 1999-02 412.83
Montara 1999-01 289.07
Arbuckle 1999-01 270.08
San Francisco 1999-02 11257
Los Angeles 1999-02 2128.59
Pescadero 1999-02 298.44
Legrand 1999-02 18.66
22 rows selected.
优化器使用临时表SYS_TEMP_0FD9D6893_63D6F82来代替customers表，并且使用临时表中的相关列来替换所引用的列cust_id和cust_city。数据库创建带有两列(c0 number,c1 varchar2(30))的临时表(从执行计划中的 6 – (rowset=256) “C0″[NUMBER,22], “C1″[VARCHAR2,30]也可以看到)。这些列关联到customers表中的cust_id和cust_city列。
在下面的执行计划中的1,2,3行物化customers子查询到临时表中，在第6行，数据库扫描临时表(代替子查询)来从事实表中构建位图。第27行扫描临时表执行连接返回代替扫描customers表。数据库不用对临时表应用customer表上的过滤条件，因为在物化临时表时已经应用了过滤条件。
SQL> select * from table(dbms_xplan.display_cursor(null,null,’advanced allstats last runstats_last peeked_binds’));
SQL_ID a069wzk60bbqd, child number 2
————————————-
SELECT c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold)
sales_amount FROM sales s, times t, customers c, channels ch WHERE
s.time_id = t.time_id AND s.cust_id = c.cust_id AND s.channel_id =
ch.channel_id AND c.cust_state_province = ‘CA’ AND ch.channel_desc =
‘Internet’ AND t.calendar_quarter_desc IN (‘1999-01′,’1999-02’) GROUP
BY c.cust_city, t.calendar_quarter_desc
Plan hash value: 2164696140
————————————————————————————————————————————————————————————————————————
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem |
————————————————————————————————————————————————————————————————————————
| 0 | SELECT STATEMENT | | 1 | | | 1177 (100)| | | | 22 |00:00:00.25 | 9080 | 86 | 10 | | | |
| 1 | TEMP TABLE TRANSFORMATION | | 1 | | | | | | | 22 |00:00:00.25 | 9080 | 86 | 10 | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6893_63D6F82 | 1 | | | | | | | 0 |00:00:00.04 | 1535 | 0 | 10 | 1042K| 1042K| |
|* 3 | TABLE ACCESS FULL | CUSTOMERS | 1 | 3341 | 86866 | 423 (1)| 00:00:01 | | | 3341 |00:00:00.01 | 1522 | 0 | 0 | | | |
| 4 | HASH GROUP BY | | 1 | 877 | 49989 | 754 (1)| 00:00:01 | | | 22 |00:00:00.20 | 7538 | 85 | 0 | 1022K| 1022K| 1349K (0)|
|* 5 | HASH JOIN | | 1 | 14534 | 809K| 753 (1)| 00:00:01 | | | 964 |00:00:00.20 | 7538 | 85 | 0 | 1572K| 1572K| 1696K (0)|
| 6 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6893_63D6F82 | 1 | 3341 | 50115 | 4 (0)| 00:00:01 | | | 3341 |00:00:00.01 | 18 | 10 | 0 | | | |
|* 7 | HASH JOIN | | 1 | 14534 | 596K| 749 (1)| 00:00:01 | | | 964 |00:00:00.19 | 7520 | 75 | 0 | 1538K| 1538K| 1685K (0)|
|* 8 | TABLE ACCESS FULL | TIMES | 1 | 181 | 2896 | 18 (0)| 00:00:01 | | | 181 |00:00:00.01 | 65 | 0 | 0 | | | |
| 9 | VIEW | VW_ST_A3F94988 | 1 | 14534 | 369K| 731 (1)| 00:00:01 | | | 964 |00:00:00.18 | 7455 | 75 | 0 | | | |
| 10 | NESTED LOOPS | | 1 | 14534 | 809K| 706 (1)| 00:00:01 | | | 964 |00:00:00.18 | 7455 | 75 | 0 | | | |
| 11 | PARTITION RANGE SUBQUERY | | 1 | 14534 | 397K| 353 (0)| 00:00:01 |KEY(SQ)|KEY(SQ)| 964 |00:00:00.17 | 7271 | 75 | 0 | | | |
| 12 | BITMAP CONVERSION TO ROWIDS| | 2 | 14534 | 397K| 353 (0)| 00:00:01 | | | 964 |00:00:00.16 | 7204 | 75 | 0 | | | |
| 13 | BITMAP AND | | 2 | | | | | | | 2 |00:00:00.16 | 7204 | 75 | 0 | | | |
| 14 | BITMAP MERGE | | 2 | | | | | | | 2 |00:00:00.02 | 15 | 5 | 0 | 1024K| 512K| 4096 (0)|
| 15 | BITMAP KEY ITERATION | | 2 | | | | | | | 2 |00:00:00.02 | 15 | 5 | 0 | | | |
| 16 | BUFFER SORT | | 2 | | | | | | | 2 |00:00:00.01 | 9 | 0 | 0 | 73728 | 73728 | |
|* 17 | TABLE ACCESS FULL | CHANNELS | 1 | 1 | 13 | 3 (0)| 00:00:01 | | | 1 |00:00:00.01 | 9 | 0 | 0 | | | |
|* 18 | BITMAP INDEX RANGE SCAN| SALES_CHANNEL_BIX | 2 | | | | |KEY(SQ)|KEY(SQ)| 2 |00:00:00.02 | 6 | 5 | 0 | | | |
| 19 | BITMAP MERGE | | 2 | | | | | | | 2 |00:00:00.02 | 445 | 9 | 0 | 1024K| 512K|39936 (0)|
| 20 | BITMAP KEY ITERATION | | 2 | | | | | | | 181 |00:00:00.02 | 445 | 9 | 0 | | | |
| 21 | BUFFER SORT | | 2 | | | | | | | 362 |00:00:00.01 | 65 | 0 | 0 | 73728 | 73728 | |
|* 22 | TABLE ACCESS FULL | TIMES | 1 | 181 | 2896 | 18 (0)| 00:00:01 | | | 181 |00:00:00.01 | 65 | 0 | 0 | | | |
|* 23 | BITMAP INDEX RANGE SCAN| SALES_TIME_BIX | 362 | | | | |KEY(SQ)|KEY(SQ)| 181 |00:00:00.02 | 380 | 9 | 0 | | | |
| 24 | BITMAP MERGE | | 2 | | | | | | | 2 |00:00:00.13 | 6744 | 61 | 0 | 1024K| 512K|45056 (0)|
| 25 | BITMAP KEY ITERATION | | 2 | | | | | | | 403 |00:00:00.12 | 6744 | 61 | 0 | | | |
| 26 | BUFFER SORT | | 2 | | | | | | | 6682 |00:00:00.01 | 18 | 0 | 0 | 5512K| 964K| 174K (0)|
| 27 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6893_63D6F82 | 1 | 3341 | 16705 | 4 (0)| 00:00:01 | | | 3341 |00:00:00.01 | 18 | 0 | 0 | | | |
|* 28 | BITMAP INDEX RANGE SCAN| SALES_CUST_BIX | 6682 | | | | |KEY(SQ)|KEY(SQ)| 403 |00:00:00.10 | 6726 | 61 | 0 | | | |
| 29 | TABLE ACCESS BY USER ROWID | SALES | 964 | 1 | 29 | 378 (0)| 00:00:01 | ROWID | ROWID | 964 |00:00:00.01 | 184 | 0 | 0 | | | |
————————————————————————————————————————————————————————————————————————
Query Block Name / Object Alias (identified by operation id):
————————————————————-
1 – SEL$D5EF7599
2 – SEL$F6045C7B
3 – SEL$F6045C7B / C@SEL$F6045C7B
6 – SEL$D5EF7599 / T1@SEL$9C741BEB
8 – SEL$D5EF7599 / T@SEL$1
9 – SEL$5E9A798F / VW_ST_A3F94988@SEL$D5EF7599
10 – SEL$5E9A798F
12 – SEL$5E9A798F / S@SEL$1
17 – SEL$6EE793B7 / CH@SEL$6EE793B7
22 – SEL$ACF30367 / T@SEL$ACF30367
27 – SEL$E1F9C76C / T1@SEL$E1F9C76C
29 – SEL$5E9A798F / SYS_CP_S@SEL$5E9A798F
Outline Data
————-
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE(‘12.2.0.1’)
DB_VERSION(‘12.2.0.1’)
OPT_PARAM(‘star_transformation_enabled’ ‘true’)
ALL_ROWS
NO_PARALLEL
OUTLINE_LEAF(@”SEL$F6045C7B”)
OUTLINE_LEAF(@”SEL$ACF30367″)
OUTLINE_LEAF(@”SEL$6EE793B7″)
OUTLINE_LEAF(@”SEL$E1F9C76C”)
OUTLINE_LEAF(@”SEL$5E9A798F”)
TABLE_LOOKUP_BY_NL(@”SEL$0E028FD0″ “S”@”SEL$1″)
OUTLINE_LEAF(@”SEL$D5EF7599″)
OUTLINE(@”SEL$1″)
OUTLINE(@”SEL$0E028FD0″)
OUTLINE(@”SEL$C3AF6D21″)
ELIMINATE_JOIN(@”SEL$1” “CH”@”SEL$1″)
OUTLINE(@”SEL$5208623C”)
STAR_TRANSFORMATION(@”SEL$1″ “S”@”SEL$1” SUBQUERIES((“T”@”SEL$1”) (“CH”@”SEL$1”) TEMP_TABLE(“C”@”SEL$1″)))
FULL(@”SEL$D5EF7599” “T”@”SEL$1″)
NO_ACCESS(@”SEL$D5EF7599” “VW_ST_A3F94988″@”SEL$D5EF7599″)
FULL(@”SEL$D5EF7599” “T1″@”SEL$9C741BEB”)
LEADING(@”SEL$D5EF7599″ “T”@”SEL$1” “VW_ST_A3F94988″@”SEL$D5EF7599” “T1″@”SEL$9C741BEB”)
USE_HASH(@”SEL$D5EF7599″ “VW_ST_A3F94988″@”SEL$D5EF7599″)
USE_HASH(@”SEL$D5EF7599” “T1″@”SEL$9C741BEB”)
SWAP_JOIN_INPUTS(@”SEL$D5EF7599″ “T1″@”SEL$9C741BEB”)
USE_HASH_AGGREGATION(@”SEL$D5EF7599″)
BITMAP_AND(@”SEL$5E9A798F” “S”@”SEL$1” (“SALES”.”CHANNEL_ID”) 1)
BITMAP_AND(@”SEL$5E9A798F” “S”@”SEL$1” (“SALES”.”TIME_ID”) 2)
BITMAP_AND(@”SEL$5E9A798F” “S”@”SEL$1” (“SALES”.”CUST_ID”) 3)
ROWID(@”SEL$5E9A798F” “SYS_CP_S”@”SEL$5E9A798F”)
LEADING(@”SEL$5E9A798F” “S”@”SEL$1” “SYS_CP_S”@”SEL$5E9A798F”)
SUBQUERY_PRUNING(@”SEL$5E9A798F” “S”@”SEL$1″ PARTITION)
USE_NL(@”SEL$5E9A798F” “SYS_CP_S”@”SEL$5E9A798F”)
FULL(@”SEL$E1F9C76C” “T1″@”SEL$E1F9C76C”)
SEMIJOIN_DRIVER(@”SEL$E1F9C76C”)
FULL(@”SEL$6EE793B7″ “CH”@”SEL$6EE793B7″)
SEMIJOIN_DRIVER(@”SEL$6EE793B7″)
FULL(@”SEL$ACF30367” “T”@”SEL$ACF30367″)
SEMIJOIN_DRIVER(@”SEL$ACF30367″)
FULL(@”SEL$F6045C7B” “C”@”SEL$F6045C7B”)
SEMIJOIN_DRIVER(@”SEL$F6045C7B”)
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
—————————————————
3 – filter(“C”.”CUST_STATE_PROVINCE”=’CA’)
5 – access(“ITEM_1″=”C0”)
7 – access(“ITEM_2″=”T”.”TIME_ID”)
8 – filter((“T”.”CALENDAR_QUARTER_DESC”=’1999-01′ OR “T”.”CALENDAR_QUARTER_DESC”=’1999-02′))
17 – filter(“CH”.”CHANNEL_DESC”=’Internet’)
18 – access(“S”.”CHANNEL_ID”=”CH”.”CHANNEL_ID”)
22 – filter((“T”.”CALENDAR_QUARTER_DESC”=’1999-01′ OR “T”.”CALENDAR_QUARTER_DESC”=’1999-02′))
23 – access(“S”.”TIME_ID”=”T”.”TIME_ID”)
28 – access(“S”.”CUST_ID”=”C0″)
Column Projection Information (identified by operation id):
———————————————————–
1 – “C1″[VARCHAR2,30], “T”.”CALENDAR_QUARTER_DESC”[CHARACTER,7], SUM(“ITEM_3”)[22]
2 – SYSDEF[4], SYSDEF[0], SYSDEF[1], SYSDEF[120], SYSDEF[0]
3 – “C”.”CUST_ID”[NUMBER,22], “C”.”CUST_CITY”[VARCHAR2,30], “C”.”CUST_STATE_PROVINCE”[VARCHAR2,40]
4 – “C1″[VARCHAR2,30], “T”.”CALENDAR_QUARTER_DESC”[CHARACTER,7], SUM(“ITEM_3”)[22]
5 – (#keys=1; rowset=256) “C0″[NUMBER,22], “ITEM_1″[NUMBER,22], “C1″[VARCHAR2,30], “T”.”TIME_ID”[DATE,7], “ITEM_2″[DATE,7], “T”.”CALENDAR_QUARTER_DESC”[CHARACTER,7], “ITEM_3″[NUMBER,22]
6 – (rowset=256) “C0″[NUMBER,22], “C1″[VARCHAR2,30]
7 – (#keys=1; rowset=256) “T”.”TIME_ID”[DATE,7], “ITEM_2″[DATE,7], “T”.”CALENDAR_QUARTER_DESC”[CHARACTER,7], “ITEM_1″[NUMBER,22], “ITEM_3″[NUMBER,22]
8 – (rowset=256) “T”.”TIME_ID”[DATE,7], “T”.”CALENDAR_QUARTER_DESC”[CHARACTER,7]
9 – “ITEM_1″[NUMBER,22], “ITEM_2″[DATE,7], “ITEM_3″[NUMBER,22]
10 – ROWID[ROWID,10], ROWID[ROWID,10], “S”.”CUST_ID”[NUMBER,22], “S”.”TIME_ID”[DATE,7], “S”.”AMOUNT_SOLD”[NUMBER,22]
11 – ROWID[ROWID,10]
12 – ROWID[ROWID,10]
13 – STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496]
14 – STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496]
15 – STRDEF[10], STRDEF[10], STRDEF[7920], “S”.”CHANNEL_ID”[NUMBER,22]
16 – (#keys=2) “CH”.”CHANNEL_ID”[NUMBER,22], “CH”.”CHANNEL_DESC”[VARCHAR2,20]
17 – (rowset=256) “CH”.”CHANNEL_ID”[NUMBER,22], “CH”.”CHANNEL_DESC”[VARCHAR2,20]
18 – STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7920], “S”.”CHANNEL_ID”[NUMBER,22]
19 – STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496]
20 – STRDEF[10], STRDEF[10], STRDEF[7920], “S”.”TIME_ID”[DATE,7]
21 – (#keys=2) “T”.”TIME_ID”[DATE,7], “T”.”CALENDAR_QUARTER_DESC”[CHARACTER,7]
22 – (rowset=256) “T”.”TIME_ID”[DATE,7], “T”.”CALENDAR_QUARTER_DESC”[CHARACTER,7]
23 – STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7920], “S”.”TIME_ID”[DATE,7]
24 – STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496]
25 – STRDEF[10], STRDEF[10], STRDEF[7920], “S”.”CUST_ID”[NUMBER,22]
26 – (#keys=1) “C0″[NUMBER,22]
27 – (rowset=256) “C0″[NUMBER,22]
28 – STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7920], “S”.”CUST_ID”[NUMBER,22]
29 – ROWID[ROWID,10], “S”.”CUST_ID”[NUMBER,22], “S”.”TIME_ID”[DATE,7], “S”.”AMOUNT_SOLD”[NUMBER,22]
Note
—–
– automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
– cbqt star transformation used for this statement
– this is an adaptive plan
总结
前言
大家都知道在12CR2中出现一种新的查询转换技术临时表转换， 在下面的例子中，数据库对customers表上的子查询结果物化到一个临时表中:
SQL> show parameter star_transformation_enabled
star_transformation_enabled string FALSE
SQL> alter session set star_transformation_enabled=’true’;
Session altered.
SQL> SELECT c.cust_city,
2 t.calendar_quarter_desc,
3 SUM(s.amount_sold) sales_amount
4 FROM sales s,
5 times t,
6 customers c,
7 channels ch
8 WHERE s.time_id = t.time_id
9 AND s.cust_id = c.cust_id
10 AND s.channel_id = ch.channel_id
11 AND c.cust_state_province = ‘CA’
12 AND ch.channel_desc = ‘Internet’
13 AND t.calendar_quarter_desc IN (‘1999-01′,’1999-02’)
14 GROUP BY c.cust_city, t.calendar_quarter_desc;
Montara 1999-02 1618.01
Pala 1999-01 3263.93
Cloverdale 1999-01 52.64
Cloverdale 1999-02 266.28
San Francisco 1999-01 3058.27
San Mateo 1999-01 8754.59
Los Angeles 1999-01 1886.19
San Mateo 1999-02 21399.42
Pala 1999-02 936.62
El Sobrante 1999-02 3744.03
El Sobrante 1999-01 5392.34
Quartzhill 1999-01 987.3
Legrand 1999-01 26.32
Pescadero 1999-01 26.32
Arbuckle 1999-02 241.2
Quartzhill 1999-02 412.83
Montara 1999-01 289.07
Arbuckle 1999-01 270.08
San Francisco 1999-02 11257
Los Angeles 1999-02 2128.59
Pescadero 1999-02 298.44
Legrand 1999-02 18.66
22 rows selected.
优化器使用临时表SYS_TEMP_0FD9D6893_63D6F82来代替customers表，并且使用临时表中的相关列来替换所引用的列cust_id和cust_city。数据库创建带有两列(c0 number,c1 varchar2(30))的临时表(从执行计划中的 6 – (rowset=256) “C0″[NUMBER,22], “C1″[VARCHAR2,30]也可以看到)。这些列关联到customers表中的cust_id和cust_city列。
在下面的执行计划中的1,2,3行物化customers子查询到临时表中，在第6行，数据库扫描临时表(代替子查询)来从事实表中构建位图。第27行扫描临时表执行连接返回代替扫描customers表。数据库不用对临时表应用customer表上的过滤条件，因为在物化临时表时已经应用了过滤条件。
SQL> select * from table(dbms_xplan.display_cursor(null,null,’advanced allstats last runstats_last peeked_binds’));
SQL_ID a069wzk60bbqd, child number 2
————————————-
SELECT c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold)
sales_amount FROM sales s, times t, customers c, channels ch WHERE
s.time_id = t.time_id AND s.cust_id = c.cust_id AND s.channel_id =
ch.channel_id AND c.cust_state_province = ‘CA’ AND ch.channel_desc =
‘Internet’ AND t.calendar_quarter_desc IN (‘1999-01′,’1999-02’) GROUP
BY c.cust_city, t.calendar_quarter_desc
Plan hash value: 2164696140
————————————————————————————————————————————————————————————————————————
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem |
————————————————————————————————————————————————————————————————————————
| 0 | SELECT STATEMENT | | 1 | | | 1177 (100)| | | | 22 |00:00:00.25 | 9080 | 86 | 10 | | | |
| 1 | TEMP TABLE TRANSFORMATION | | 1 | | | | | | | 22 |00:00:00.25 | 9080 | 86 | 10 | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6893_63D6F82 | 1 | | | | | | | 0 |00:00:00.04 | 1535 | 0 | 10 | 1042K| 1042K| |
|* 3 | TABLE ACCESS FULL | CUSTOMERS | 1 | 3341 | 86866 | 423 (1)| 00:00:01 | | | 3341 |00:00:00.01 | 1522 | 0 | 0 | | | |
| 4 | HASH GROUP BY | | 1 | 877 | 49989 | 754 (1)| 00:00:01 | | | 22 |00:00:00.20 | 7538 | 85 | 0 | 1022K| 1022K| 1349K (0)|
|* 5 | HASH JOIN | | 1 | 14534 | 809K| 753 (1)| 00:00:01 | | | 964 |00:00:00.20 | 7538 | 85 | 0 | 1572K| 1572K| 1696K (0)|
| 6 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6893_63D6F82 | 1 | 3341 | 50115 | 4 (0)| 00:00:01 | | | 3341 |00:00:00.01 | 18 | 10 | 0 | | | |
|* 7 | HASH JOIN | | 1 | 14534 | 596K| 749 (1)| 00:00:01 | | | 964 |00:00:00.19 | 7520 | 75 | 0 | 1538K| 1538K| 1685K (0)|
|* 8 | TABLE ACCESS FULL | TIMES | 1 | 181 | 2896 | 18 (0)| 00:00:01 | | | 181 |00:00:00.01 | 65 | 0 | 0 | | | |
| 9 | VIEW | VW_ST_A3F94988 | 1 | 14534 | 369K| 731 (1)| 00:00:01 | | | 964 |00:00:00.18 | 7455 | 75 | 0 | | | |
| 10 | NESTED LOOPS | | 1 | 14534 | 809K| 706 (1)| 00:00:01 | | | 964 |00:00:00.18 | 7455 | 75 | 0 | | | |
| 11 | PARTITION RANGE SUBQUERY | | 1 | 14534 | 397K| 353 (0)| 00:00:01 |KEY(SQ)|KEY(SQ)| 964 |00:00:00.17 | 7271 | 75 | 0 | | | |
| 12 | BITMAP CONVERSION TO ROWIDS| | 2 | 14534 | 397K| 353 (0)| 00:00:01 | | | 964 |00:00:00.16 | 7204 | 75 | 0 | | | |
| 13 | BITMAP AND | | 2 | | | | | | | 2 |00:00:00.16 | 7204 | 75 | 0 | | | |
| 14 | BITMAP MERGE | | 2 | | | | | | | 2 |00:00:00.02 | 15 | 5 | 0 | 1024K| 512K| 4096 (0)|
| 15 | BITMAP KEY ITERATION | | 2 | | | | | | | 2 |00:00:00.02 | 15 | 5 | 0 | | | |
| 16 | BUFFER SORT | | 2 | | | | | | | 2 |00:00:00.01 | 9 | 0 | 0 | 73728 | 73728 | |
|* 17 | TABLE ACCESS FULL | CHANNELS | 1 | 1 | 13 | 3 (0)| 00:00:01 | | | 1 |00:00:00.01 | 9 | 0 | 0 | | | |
|* 18 | BITMAP INDEX RANGE SCAN| SALES_CHANNEL_BIX | 2 | | | | |KEY(SQ)|KEY(SQ)| 2 |00:00:00.02 | 6 | 5 | 0 | | | |
| 19 | BITMAP MERGE | | 2 | | | | | | | 2 |00:00:00.02 | 445 | 9 | 0 | 1024K| 512K|39936 (0)|
| 20 | BITMAP KEY ITERATION | | 2 | | | | | | | 181 |00:00:00.02 | 445 | 9 | 0 | | | |
| 21 | BUFFER SORT | | 2 | | | | | | | 362 |00:00:00.01 | 65 | 0 | 0 | 73728 | 73728 | |
|* 22 | TABLE ACCESS FULL | TIMES | 1 | 181 | 2896 | 18 (0)| 00:00:01 | | | 181 |00:00:00.01 | 65 | 0 | 0 | | | |
|* 23 | BITMAP INDEX RANGE SCAN| SALES_TIME_BIX | 362 | | | | |KEY(SQ)|KEY(SQ)| 181 |00:00:00.02 | 380 | 9 | 0 | | | |
| 24 | BITMAP MERGE | | 2 | | | | | | | 2 |00:00:00.13 | 6744 | 61 | 0 | 1024K| 512K|45056 (0)|
| 25 | BITMAP KEY ITERATION | | 2 | | | | | | | 403 |00:00:00.12 | 6744 | 61 | 0 | | | |
| 26 | BUFFER SORT | | 2 | | | | | | | 6682 |00:00:00.01 | 18 | 0 | 0 | 5512K| 964K| 174K (0)|
| 27 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6893_63D6F82 | 1 | 3341 | 16705 | 4 (0)| 00:00:01 | | | 3341 |00:00:00.01 | 18 | 0 | 0 | | | |
|* 28 | BITMAP INDEX RANGE SCAN| SALES_CUST_BIX | 6682 | | | | |KEY(SQ)|KEY(SQ)| 403 |00:00:00.10 | 6726 | 61 | 0 | | | |
| 29 | TABLE ACCESS BY USER ROWID | SALES | 964 | 1 | 29 | 378 (0)| 00:00:01 | ROWID | ROWID | 964 |00:00:00.01 | 184 | 0 | 0 | | | |
————————————————————————————————————————————————————————————————————————
Query Block Name / Object Alias (identified by operation id):
————————————————————-
1 – SEL$D5EF7599
2 – SEL$F6045C7B
3 – SEL$F6045C7B / C@SEL$F6045C7B
6 – SEL$D5EF7599 / T1@SEL$9C741BEB
8 – SEL$D5EF7599 / T@SEL$1
9 – SEL$5E9A798F / VW_ST_A3F94988@SEL$D5EF7599
10 – SEL$5E9A798F
12 – SEL$5E9A798F / S@SEL$1
17 – SEL$6EE793B7 / CH@SEL$6EE793B7
22 – SEL$ACF30367 / T@SEL$ACF30367
27 – SEL$E1F9C76C / T1@SEL$E1F9C76C
29 – SEL$5E9A798F / SYS_CP_S@SEL$5E9A798F
Outline Data
————-
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE(‘12.2.0.1’)
DB_VERSION(‘12.2.0.1’)
OPT_PARAM(‘star_transformation_enabled’ ‘true’)
ALL_ROWS
NO_PARALLEL
OUTLINE_LEAF(@”SEL$F6045C7B”)
OUTLINE_LEAF(@”SEL$ACF30367″)
OUTLINE_LEAF(@”SEL$6EE793B7″)
OUTLINE_LEAF(@”SEL$E1F9C76C”)
OUTLINE_LEAF(@”SEL$5E9A798F”)
TABLE_LOOKUP_BY_NL(@”SEL$0E028FD0″ “S”@”SEL$1″)
OUTLINE_LEAF(@”SEL$D5EF7599″)
OUTLINE(@”SEL$1″)
OUTLINE(@”SEL$0E028FD0″)
OUTLINE(@”SEL$C3AF6D21″)
ELIMINATE_JOIN(@”SEL$1” “CH”@”SEL$1″)
OUTLINE(@”SEL$5208623C”)
STAR_TRANSFORMATION(@”SEL$1″ “S”@”SEL$1” SUBQUERIES((“T”@”SEL$1”) (“CH”@”SEL$1”) TEMP_TABLE(“C”@”SEL$1″)))
FULL(@”SEL$D5EF7599” “T”@”SEL$1″)
NO_ACCESS(@”SEL$D5EF7599” “VW_ST_A3F94988″@”SEL$D5EF7599″)
FULL(@”SEL$D5EF7599” “T1″@”SEL$9C741BEB”)
LEADING(@”SEL$D5EF7599″ “T”@”SEL$1” “VW_ST_A3F94988″@”SEL$D5EF7599” “T1″@”SEL$9C741BEB”)
USE_HASH(@”SEL$D5EF7599″ “VW_ST_A3F94988″@”SEL$D5EF7599″)
USE_HASH(@”SEL$D5EF7599” “T1″@”SEL$9C741BEB”)
SWAP_JOIN_INPUTS(@”SEL$D5EF7599″ “T1″@”SEL$9C741BEB”)
USE_HASH_AGGREGATION(@”SEL$D5EF7599″)
BITMAP_AND(@”SEL$5E9A798F” “S”@”SEL$1” (“SALES”.”CHANNEL_ID”) 1)
BITMAP_AND(@”SEL$5E9A798F” “S”@”SEL$1” (“SALES”.”TIME_ID”) 2)
BITMAP_AND(@”SEL$5E9A798F” “S”@”SEL$1” (“SALES”.”CUST_ID”) 3)
ROWID(@”SEL$5E9A798F” “SYS_CP_S”@”SEL$5E9A798F”)
LEADING(@”SEL$5E9A798F” “S”@”SEL$1” “SYS_CP_S”@”SEL$5E9A798F”)
SUBQUERY_PRUNING(@”SEL$5E9A798F” “S”@”SEL$1″ PARTITION)
USE_NL(@”SEL$5E9A798F” “SYS_CP_S”@”SEL$5E9A798F”)
FULL(@”SEL$E1F9C76C” “T1″@”SEL$E1F9C76C”)
SEMIJOIN_DRIVER(@”SEL$E1F9C76C”)
FULL(@”SEL$6EE793B7″ “CH”@”SEL$6EE793B7″)
SEMIJOIN_DRIVER(@”SEL$6EE793B7″)
FULL(@”SEL$ACF30367” “T”@”SEL$ACF30367″)
SEMIJOIN_DRIVER(@”SEL$ACF30367″)
FULL(@”SEL$F6045C7B” “C”@”SEL$F6045C7B”)
SEMIJOIN_DRIVER(@”SEL$F6045C7B”)
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
—————————————————
3 – filter(“C”.”CUST_STATE_PROVINCE”=’CA’)
5 – access(“ITEM_1″=”C0”)
7 – access(“ITEM_2″=”T”.”TIME_ID”)
8 – filter((“T”.”CALENDAR_QUARTER_DESC”=’1999-01′ OR “T”.”CALENDAR_QUARTER_DESC”=’1999-02′))
17 – filter(“CH”.”CHANNEL_DESC”=’Internet’)
18 – access(“S”.”CHANNEL_ID”=”CH”.”CHANNEL_ID”)
22 – filter((“T”.”CALENDAR_QUARTER_DESC”=’1999-01′ OR “T”.”CALENDAR_QUARTER_DESC”=’1999-02′))
23 – access(“S”.”TIME_ID”=”T”.”TIME_ID”)
28 – access(“S”.”CUST_ID”=”C0″)
Column Projection Information (identified by operation id):
———————————————————–
1 – “C1″[VARCHAR2,30], “T”.”CALENDAR_QUARTER_DESC”[CHARACTER,7], SUM(“ITEM_3”)[22]
2 – SYSDEF[4], SYSDEF[0], SYSDEF[1], SYSDEF[120], SYSDEF[0]
3 – “C”.”CUST_ID”[NUMBER,22], “C”.”CUST_CITY”[VARCHAR2,30], “C”.”CUST_STATE_PROVINCE”[VARCHAR2,40]
4 – “C1″[VARCHAR2,30], “T”.”CALENDAR_QUARTER_DESC”[CHARACTER,7], SUM(“ITEM_3”)[22]
5 – (#keys=1; rowset=256) “C0″[NUMBER,22], “ITEM_1″[NUMBER,22], “C1″[VARCHAR2,30], “T”.”TIME_ID”[DATE,7], “ITEM_2″[DATE,7], “T”.”CALENDAR_QUARTER_DESC”[CHARACTER,7], “ITEM_3″[NUMBER,22]
6 – (rowset=256) “C0″[NUMBER,22], “C1″[VARCHAR2,30]
7 – (#keys=1; rowset=256) “T”.”TIME_ID”[DATE,7], “ITEM_2″[DATE,7], “T”.”CALENDAR_QUARTER_DESC”[CHARACTER,7], “ITEM_1″[NUMBER,22], “ITEM_3″[NUMBER,22]
8 – (rowset=256) “T”.”TIME_ID”[DATE,7], “T”.”CALENDAR_QUARTER_DESC”[CHARACTER,7]
9 – “ITEM_1″[NUMBER,22], “ITEM_2″[DATE,7], “ITEM_3″[NUMBER,22]
10 – ROWID[ROWID,10], ROWID[ROWID,10], “S”.”CUST_ID”[NUMBER,22], “S”.”TIME_ID”[DATE,7], “S”.”AMOUNT_SOLD”[NUMBER,22]
11 – ROWID[ROWID,10]
12 – ROWID[ROWID,10]
13 – STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496]
14 – STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496]
15 – STRDEF[10], STRDEF[10], STRDEF[7920], “S”.”CHANNEL_ID”[NUMBER,22]
16 – (#keys=2) “CH”.”CHANNEL_ID”[NUMBER,22], “CH”.”CHANNEL_DESC”[VARCHAR2,20]
17 – (rowset=256) “CH”.”CHANNEL_ID”[NUMBER,22], “CH”.”CHANNEL_DESC”[VARCHAR2,20]
18 – STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7920], “S”.”CHANNEL_ID”[NUMBER,22]
19 – STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496]
20 – STRDEF[10], STRDEF[10], STRDEF[7920], “S”.”TIME_ID”[DATE,7]
21 – (#keys=2) “T”.”TIME_ID”[DATE,7], “T”.”CALENDAR_QUARTER_DESC”[CHARACTER,7]
22 – (rowset=256) “T”.”TIME_ID”[DATE,7], “T”.”CALENDAR_QUARTER_DESC”[CHARACTER,7]
23 – STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7920], “S”.”TIME_ID”[DATE,7]
24 – STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496]
25 – STRDEF[10], STRDEF[10], STRDEF[7920], “S”.”CUST_ID”[NUMBER,22]
26 – (#keys=1) “C0″[NUMBER,22]
27 – (rowset=256) “C0″[NUMBER,22]
28 – STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7920], “S”.”CUST_ID”[NUMBER,22]
29 – ROWID[ROWID,10], “S”.”CUST_ID”[NUMBER,22], “S”.”TIME_ID”[DATE,7], “S”.”AMOUNT_SOLD”[NUMBER,22]
Note
—–
– automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
– cbqt star transformation used for this statement
– this is an adaptive plan
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对的支持。
