Oracle 12CR2查詢轉換之星型轉換
星型轉換是一種最佳化轉換它用來避免對星型方案中的事實表進行全表掃描。一個星型方案將資料分成事實與維度表。事實是對一個事件比如銷售的測量通常是數字。維度是標識事實的分類，比如日期，位置與產品。一個事實表有一個由方案中維度表主鍵所組成的複合鍵。維度表實際上充當查詢或引用表能讓你選擇你查詢所要請求的值。
星型轉換的目的
在連線事實表與維度表時，星型轉換可能避免對事實表執行完全掃描。星型轉換透過只獲取連線到約束維度行記錄的相關事實行記錄來提高效能。在有些情況下，查詢已經在維度表的其它列上有限制性過濾了。過濾組合可以大大減少資料庫從事實表中要處理的資料集大小。
星型轉換的工作原理
星型轉換增加了子查詢謂詞，叫作點陣圖半連線謂詞，關聯到約束維度表。當在實際連線列上存在索引時最佳化器執行轉換。透過驅動點陣圖and和or來操作由子查詢所提供的鍵值，資料庫只需要從事實表中檢索相關行記錄。如果維度表上的謂詞過濾掉了大量資料，那麼星型轉換比對事實表完全掃描更有效。
在資料庫從事實表中檢索相關行記錄之後，資料庫可能需要使用原始謂詞連線這些行記錄回維度表。當以下條件滿足時資料庫可以消除連線回維度表:
.維度表上的所有謂詞是半連線子查詢謂詞的一部分
.從子查詢中所選擇的列具有唯一性
.維度列不在select列，group by子句中等等
控制星型轉換
star_transformation_enabled引數控制著星型轉換。這個引數有以下引數值:
.true
最佳化器透過自動識別事實與約束維度表來執行星型轉換。只有轉換後的執行計劃成本比原始執行計劃成本低時最佳化器才執行星型轉換。當物化提高效能時最佳化器也會嘗試臨時錶轉換。
.false(預設值)
最佳化器不執行星型轉換
.temp_disable
這個值與true相同，只是最佳化器不會嘗試臨時錶轉換
星型轉換:應用場景
下面的查詢找出1999年Q1和Q2季度在California的所有城市中的總的網路銷售額:
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;
示例輸出如下:
SQL> show parameter star_transformation_enabled
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
star_transformation_enabled string FALSE
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;
CUST_CITY CALENDA SALES_AMOUNT
------------------------------ ------- ------------
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.
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced allstats last runstats_last peeked_binds'));
SQL_ID a069wzk60bbqd, child number 1
-------------------------------------
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: 1865285285
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 957 (100)| | | | 22 |00:00:00.18 | 1812 | | | |
| 1 | HASH GROUP BY | | 1 | 22 | 1672 | 957 (2)| 00:00:01 | | | 22 |00:00:00.18 | 1812 | 1022K| 1022K| 1382K (0)|
|* 2 | HASH JOIN | | 1 | 138 | 10488 | 956 (2)| 00:00:01 | | | 964 |00:00:00.14 | 1812 | 1538K| 1538K| 1588K (0)|
| 3 | PART JOIN FILTER CREATE | :BF0000 | 1 | 183 | 2928 | 18 (0)| 00:00:01 | | | 181 |00:00:00.01 | 65 | | | |
|* 4 | TABLE ACCESS FULL | TIMES | 1 | 183 | 2928 | 18 (0)| 00:00:01 | | | 181 |00:00:00.01 | 65 | | | |
|* 5 | HASH JOIN | | 1 | 964 | 57840 | 938 (2)| 00:00:01 | | | 964 |00:00:00.11 | 1747 | 1448K| 1448K| 1521K (0)|
| 6 | MERGE JOIN CARTESIAN | | 1 | 3341 | 127K| 426 (1)| 00:00:01 | | | 3341 |00:00:00.02 | 1531 | | | |
|* 7 | TABLE ACCESS FULL | CHANNELS | 1 | 1 | 13 | 3 (0)| 00:00:01 | | | 1 |00:00:00.01 | 9 | | | |
| 8 | BUFFER SORT | | 1 | 3341 | 86866 | 423 (1)| 00:00:01 | | | 3341 |00:00:00.02 | 1522 | 178K| 178K| 158K (0)|
|* 9 | TABLE ACCESS FULL | CUSTOMERS | 1 | 3341 | 86866 | 423 (1)| 00:00:01 | | | 3341 |00:00:00.01 | 1522 | | | |
| 10 | PARTITION RANGE JOIN-FILTER| | 1 | 819K| 16M| 510 (2)| 00:00:01 |:BF0000|:BF0000| 118K|00:00:00.02 | 216 | | | |
| 11 | TABLE ACCESS FULL | SALES | 2 | 819K| 16M| 510 (2)| 00:00:01 |:BF0000|:BF0000| 118K|00:00:00.02 | 216 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
從上面的執行中可以看到，在沒有使用星型轉換時id=11，對錶sales執行的是全表掃描。在這個例子中，sales表是事實表，並且其它的表是維度表。sales表對於每一個銷售的產品都有一行記錄，因此它可以包含上十億行銷售記錄。然而，只有少量產品在指定的季度透過網路銷售到了California的客戶手中。
使用星形轉換
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.
從10053跟蹤檔案中找到的星型轉換後的語句如下:
ST: Query after star xformation:******* UNPARSED QUERY IS ******* SELECT /*+ CACHE (T1) */ T1.C1 CUST_CITY, T.CALENDAR_QUARTER_DESC CALENDAR_QUARTER_DESC, SUM(S.AMOUNT_SOLD) SALES_AMOUNT FROM SH.SALES S, SH.TIMES T, SYS.SYS_TEMP_0FD9D6684_63D6F82 T1 WHERE S.CUST_ID = ANY (SELECT /*+ SEMIJOIN_DRIVER CACHE (T1) */ T1.C0 C0 FROM SYS.SYS_TEMP_0FD9D6684_63D6F82 T1) AND S.CHANNEL_ID = ANY (SELECT /*+ SEMIJOIN_DRIVER */ CH.CHANNEL_ID ITEM_1 FROM SH.CHANNELS CH WHERE CH.CHANNEL_DESC = 'Internet') AND S.TIME_ID = ANY (SELECT /*+ SEMIJOIN_DRIVER */ T.TIME_ID ITEM_1 FROM SH.TIMES T WHERE T.CALENDAR_QUARTER_DESC = '1999-01' OR T.CALENDAR_QUARTER_DESC = '1999-02') AND S.TIME_ID = T.TIME_ID AND S.CUST_ID = T1.C0 AND (T.CALENDAR_QUARTER_DESC = '1999-01' OR T.CALENDAR_QUARTER_DESC = '1999-02') GROUP BY T1.C1, T.CALENDAR_QUARTER_DESC
其執行計劃如下:
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced allstats last runstats_last peeked_binds'));
SQL_ID a069wzk60bbqd, child number 3
-------------------------------------
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 | | | 573 (100)| | | | 22 |00:00:01.41 | 9083 | 96 | 10 | | | |
| 1 | TEMP TABLE TRANSFORMATION | | 1 | | | | | | | 22 |00:00:01.41 | 9083 | 96 | 10 | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D667F_63D6F82 | 1 | | | | | | | 0 |00:00:01.07 | 1539 | 2 | 10 | 1042K| 1042K| |
|* 3 | TABLE ACCESS FULL | CUSTOMERS | 1 | 383 | 9958 | 423 (1)| 00:00:01 | | | 3341 |00:00:00.01 | 1522 | 0 | 0 | | | |
| 4 | HASH GROUP BY | | 1 | 542 | 30894 | 150 (1)| 00:00:01 | | | 22 |00:00:00.33 | 7538 | 93 | 0 | 1022K| 1022K| 1346K (0)|
|* 5 | HASH JOIN | | 1 | 1681 | 95817 | 149 (0)| 00:00:01 | | | 964 |00:00:00.31 | 7538 | 93 | 0 | 1572K| 1572K| 1677K (0)|
| 6 | TABLE ACCESS FULL | SYS_TEMP_0FD9D667F_63D6F82 | 1 | 383 | 5745 | 2 (0)| 00:00:01 | | | 3341 |00:00:00.01 | 18 | 10 | 0 | | | |
|* 7 | HASH JOIN | | 1 | 1681 | 70602 | 147 (0)| 00:00:01 | | | 964 |00:00:00.29 | 7520 | 83 | 0 | 1538K| 1538K| 1686K (0)|
|* 8 | TABLE ACCESS FULL | TIMES | 1 | 183 | 2928 | 18 (0)| 00:00:01 | | | 181 |00:00:00.01 | 65 | 0 | 0 | | | |
| 9 | VIEW | VW_ST_A3F94988 | 1 | 1685 | 43810 | 129 (0)| 00:00:01 | | | 964 |00:00:00.23 | 7455 | 83 | 0 | | | |
| 10 | NESTED LOOPS | | 1 | 1685 | 96045 | 106 (0)| 00:00:01 | | | 964 |00:00:00.23 | 7455 | 83 | 0 | | | |
| 11 | PARTITION RANGE SUBQUERY | | 1 | 1684 | 47167 | 52 (0)| 00:00:01 |KEY(SQ)|KEY(SQ)| 964 |00:00:00.22 | 7271 | 83 | 0 | | | |
| 12 | BITMAP CONVERSION TO ROWIDS| | 2 | 1684 | 47167 | 52 (0)| 00:00:01 | | | 964 |00:00:00.21 | 7204 | 83 | 0 | | | |
| 13 | BITMAP AND | | 2 | | | | | | | 2 |00:00:00.21 | 7204 | 83 | 0 | | | |
| 14 | BITMAP MERGE | | 2 | | | | | | | 2 |00:00:00.05 | 15 | 10 | 0 | 1024K| 512K| 4096 (0)|
| 15 | BITMAP KEY ITERATION | | 2 | | | | | | | 2 |00:00:00.04 | 15 | 10 | 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.04 | 6 | 10 | 0 | | | |
| 19 | BITMAP MERGE | | 2 | | | | | | | 2 |00:00:00.03 | 445 | 10 | 0 | 1024K| 512K|39936 (0)|
| 20 | BITMAP KEY ITERATION | | 2 | | | | | | | 181 |00:00:00.02 | 445 | 10 | 0 | | | |
| 21 | BUFFER SORT | | 2 | | | | | | | 362 |00:00:00.01 | 65 | 0 | 0 | 73728 | 73728 | |
|* 22 | TABLE ACCESS FULL | TIMES | 1 | 183 | 2928 | 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.01 | 380 | 10 | 0 | | | |
| 24 | BITMAP MERGE | | 2 | | | | | | | 2 |00:00:00.14 | 6744 | 63 | 0 | 1024K| 512K|45056 (0)|
| 25 | BITMAP KEY ITERATION | | 2 | | | | | | | 403 |00:00:00.14 | 6744 | 63 | 0 | | | |
| 26 | BUFFER SORT | | 2 | | | | | | | 6682 |00:00:00.01 | 18 | 0 | 0 | 5512K| 964K| 174K (0)|
| 27 | TABLE ACCESS FULL | SYS_TEMP_0FD9D667F_63D6F82 | 1 | 383 | 1915 | 2 (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 | 63 | 0 | | | |
| 29 | TABLE ACCESS BY USER ROWID | SALES | 964 | 1 | 29 | 77 (2)| 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
從Note部分的cbqt star transformation used for this statement資訊可知執行了星型轉換，從執行計劃中的ID=29這個步驟可知對錶sales使用了索引掃描而不是全表掃描。對於子查詢中的times(第22行),customers(第3行),channels（第17行）表中的每個鍵值，資料庫使用事實表sales(第23,28,18行)上索引檢索點陣圖。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26015009/viewspace-2217536/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle 12CR2查詢轉換之臨時錶轉換Oracle
- Oracle 12CR2查詢轉換之謂詞推送Oracle
- Oracle 查詢轉換Oracle
- Oracle 12CR2查詢轉換之檢視合併Oracle
- Oracle 12CR2查詢轉換之表擴充套件Oracle套件
- Oracle 查詢轉換初探Oracle
- Oracle 12CR2查詢轉換之cursor-duration臨時表Oracle
- Oracle 查詢轉換-01 or expansionOracle
- 查詢轉換
- 【SQL】Oracle查詢轉換之 OR用法SQLOracle
- Oracle查詢轉換（五）子查詢展開Oracle
- Oracle XQuery查詢、構建和轉換XML(1)(轉)OracleXML
- Oracle 查詢轉換-04 Subquery UnnestingOracle
- Oracle 查詢轉換-02 View MergingOracleView
- Oracle 查詢轉換-03 Predicate PushingOracle
- 【SQL】Oracle查詢轉換之謂詞推送SQLOracle
- 使用 Oracle XQuery 查詢、構建和轉換 XMLOracleXML
- 【SQL】Oracle查詢轉換之物化檢視查詢重寫SQLOracle
- Mysql 查詢時間轉換MySql
- 【SQL】Oracle查詢轉換之檢視合併SQLOracle
- Oracle查詢轉換（四）連線謂詞推入Oracle
- 怎樣把Oracle查詢轉換為SQL ServerOracleSQLServer
- GP詭異的查詢轉換
- long查詢結果轉換為varchar2型別型別
- 記一次詭異的Oracle查詢轉換Oracle
- Oracle查詢轉換（一）簡單檢視合併Oracle
- Oracle查詢轉換（二）複雜檢視合併Oracle
- oracle 資料型別轉換Oracle資料型別
- java型別轉換與強制型別轉換(轉)Java型別
- PHP 型別轉換&&型別強制轉換PHP型別
- Oracle查詢轉換（三）外連線檢視合併Oracle
- 型別轉換型別
- (轉)SQL查詢案例：多行轉換為一行SQL
- Oracle Long型別轉換為Clob型別Oracle型別
- oracle行列轉換-多行轉換成字串Oracle字串
- 一條SQL語句查詢塊分解及查詢轉換SQL
- D4.玩轉查詢與替換
- oracle行列轉換-字串轉換成多列Oracle字串