Oracle 12CR2查詢轉換教程之臨時表轉換詳解

2024-08-29 14:01:20

字體：大中小

來源：轉載

供稿：網友

前言

大家都知道在12CR2中出現一種新的查詢轉換技術臨時表轉換，在下面的例子中，數據庫對customers表上的子查詢結果物化到一個臨時表中:

SQL> show parameter star_transformation_enabledstar_transformation_enabled   string  FALSESQL> 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.01Pala       1999-01  3263.93Cloverdale      1999-01  52.64Cloverdale      1999-02  266.28San Francisco     1999-01  3058.27San Mateo      1999-01  8754.59Los Angeles     1999-01  1886.19San Mateo      1999-02  21399.42Pala       1999-02  936.62El Sobrante     1999-02  3744.03El Sobrante     1999-01  5392.34Quartzhill      1999-01  987.3Legrand      1999-01  26.32Pescadero      1999-01  26.32Arbuckle      1999-02  241.2Quartzhill      1999-02  412.83Montara      1999-01  289.07Arbuckle      1999-01  270.08San Francisco     1999-02  11257Los Angeles     1999-02  2128.59Pescadero      1999-02  298.44Legrand      1999-02  18.6622 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 WHEREs.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') GROUPBY c.cust_city, t.calendar_quarter_descPlan 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$5E9A798FOutline 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

總結

以上就是這篇文章的全部內容了，希望本文的內容對大家的學習或者工作具有一定的參考學習價值，如果有疑問大家可以留言交流，謝謝大家對VeVb武林網的支持。

注：相關教程知識閱讀請移步到oracle教程頻道。

上一篇：Oracle 12CR2查詢轉換教程之cursor-duration臨時表詳解

下一篇：Oracle中字符串截取常用方法總結【推薦】