您好,登錄后才能下訂單哦!
這篇文章主要為大家展示了“Oracle 12CR2查詢(xún)轉(zhuǎn)換之星型轉(zhuǎn)換的方法”,內(nèi)容簡(jiǎn)而易懂,條理清晰,希望能夠幫助大家解決疑惑,下面讓小編帶領(lǐng)大家一起研究并學(xué)習(xí)一下“Oracle 12CR2查詢(xún)轉(zhuǎn)換之星型轉(zhuǎn)換的方法”這篇文章吧。
星型轉(zhuǎn)換是一種優(yōu)化轉(zhuǎn)換它用來(lái)避免對(duì)星型方案中的事實(shí)表進(jìn)行全表掃描。一個(gè)星型方案將數(shù)據(jù)分成事實(shí)與維度表。事實(shí)是對(duì)一個(gè)事件比如銷(xiāo)售的測(cè)量通常是數(shù)字。維度是標(biāo)識(shí)事實(shí)的分類(lèi),比如日期,位置與產(chǎn)品。一個(gè)事實(shí)表有一個(gè)由方案中維度表主鍵所組成的復(fù)合鍵。維度表實(shí)際上充當(dāng)查找或引用表能讓你選擇你查詢(xún)所要請(qǐng)求的值。
星型轉(zhuǎn)換的目的
在連接事實(shí)表與維度表時(shí),星型轉(zhuǎn)換可能避免對(duì)事實(shí)表執(zhí)行完全掃描。星型轉(zhuǎn)換通過(guò)只獲取連接到約束維度行記錄的相關(guān)事實(shí)行記錄來(lái)提高性能。在有些情況下,查詢(xún)已經(jīng)在維度表的其它列上有限制性過(guò)濾了。過(guò)濾組合可以大大減少數(shù)據(jù)庫(kù)從事實(shí)表中要處理的數(shù)據(jù)集大小。
星型轉(zhuǎn)換的工作原理
星型轉(zhuǎn)換增加了子查詢(xún)謂詞,叫作位圖半連接謂詞,關(guān)聯(lián)到約束維度表。當(dāng)在實(shí)際連接列上存在索引時(shí)優(yōu)化器執(zhí)行轉(zhuǎn)換。通過(guò)驅(qū)動(dòng)位圖and和or來(lái)操作由子查詢(xún)所提供的鍵值,數(shù)據(jù)庫(kù)只需要從事實(shí)表中檢索相關(guān)行記錄。如果維度表上的謂詞過(guò)濾掉了大量數(shù)據(jù),那么星型轉(zhuǎn)換比對(duì)事實(shí)表完全掃描更有效。
在數(shù)據(jù)庫(kù)從事實(shí)表中檢索相關(guān)行記錄之后,數(shù)據(jù)庫(kù)可能需要使用原始謂詞連接這些行記錄回維度表。當(dāng)以下條件滿(mǎn)足時(shí)數(shù)據(jù)庫(kù)可以消除連接回維度表:
.維度表上的所有謂詞是半連接子查詢(xún)謂詞的一部分
.從子查詢(xún)中所選擇的列具有唯一性
.維度列不在select列,group by子句中等等
控制星型轉(zhuǎn)換
star_transformation_enabled參數(shù)控制著星型轉(zhuǎn)換。這個(gè)參數(shù)有以下參數(shù)值:
.true
優(yōu)化器通過(guò)自動(dòng)識(shí)別事實(shí)與約束維度表來(lái)執(zhí)行星型轉(zhuǎn)換。只有轉(zhuǎn)換后的執(zhí)行計(jì)劃成本比原始執(zhí)行計(jì)劃成本低時(shí)優(yōu)化器才執(zhí)行星型轉(zhuǎn)換。當(dāng)物化提高性能時(shí)優(yōu)化器也會(huì)嘗試臨時(shí)表轉(zhuǎn)換。
.false(缺省值)
優(yōu)化器不執(zhí)行星型轉(zhuǎn)換
.temp_disable
這個(gè)值與true相同,只是優(yōu)化器不會(huì)嘗試臨時(shí)表轉(zhuǎn)換
星型轉(zhuǎn)換:應(yīng)用場(chǎng)景
下面的查詢(xún)找出1999年Q1和Q2季度在California的所有城市中的總的網(wǎng)絡(luò)銷(xiāo)售額:
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 | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
從上面的執(zhí)行中可以看到,在沒(méi)有使用星型轉(zhuǎn)換時(shí)id=11,對(duì)表sales執(zhí)行的是全表掃描。在這個(gè)例子中,sales表是事實(shí)表,并且其它的表是維度表。sales表對(duì)于每一個(gè)銷(xiāo)售的產(chǎn)品都有一行記錄,因此它可以包含上十億行銷(xiāo)售記錄。然而,只有少量產(chǎn)品在指定的季度通過(guò)網(wǎng)絡(luò)銷(xiāo)售到了California的客戶(hù)手中。
使用星形轉(zhuǎn)換
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跟蹤文件中找到的星型轉(zhuǎn)換后的語(yǔ)句如下:
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
其執(zhí)行計(jì)劃如下:
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信息可知執(zhí)行了星型轉(zhuǎn)換,從執(zhí)行計(jì)劃中的ID=29這個(gè)步驟可知對(duì)表sales使用了索引掃描而不是全表掃描。對(duì)于子查詢(xún)中的times(第22行),customers(第3行),channels(第17行)表中的每個(gè)鍵值,數(shù)據(jù)庫(kù)使用事實(shí)表sales(第23,28,18行)上索引檢索位圖。
以上是“Oracle 12CR2查詢(xún)轉(zhuǎn)換之星型轉(zhuǎn)換的方法”這篇文章的所有內(nèi)容,感謝各位的閱讀!相信大家都有了一定的了解,希望分享的內(nèi)容對(duì)大家有所幫助,如果還想學(xué)習(xí)更多知識(shí),歡迎關(guān)注億速云行業(yè)資訊頻道!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。