在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

Oracle 12CR2查詢轉換之臨時錶轉換

相關文章