Oracle 12CR2查詢轉換之表擴充套件
在表擴充套件中，對於讀取一個分割槽表部分資料時最佳化器會生成使用索引的執行計劃。基於索引執行計劃可以提高效能，但索引維護會增加開鎖。在許多資料庫中，DML隻影響小部分資料。對於頻繁更新的表表擴充套件使用基於索引的執行計劃。你可以在以讀取為主的資料上建立一個索引，在以頻繁變化的資料上消除索引開銷。透過這種方式，表擴充套件在避免索引維護的同時提高了效能。
表擴充套件工作原理
表分割槽使用表擴充套件成為可能。如果在一個分割槽表上建立一個本地索引，那麼最佳化器可能會標記索引對於特定的分割槽不可使用。實際有些分割槽沒有建立索引。在表擴充套件中，最佳化器將查詢轉換為一個union all語句，讓一些子查詢訪問建立索引的分割槽，一些子查詢訪問沒有建立索引的分割槽。最佳化器可以為每個分割槽選擇最有效的訪問路徑，而不管它是否存在於查詢所要訪問的所有分割槽中。
最佳化器不總是會選擇表擴充套件
.表擴充套件是基於成本
當資料庫訪問擴充套件表的每個分割槽只會跨越union all的所有分支一次，資料庫所連線的任何表都是在分支中被訪問。
.語義問題可能導致表擴充套件無效
例如，一個表出現在一個外連線的右邊對於表擴充套件來說是無效的。
可以使用expand_table hint來控制表擴充套件。這個hint會覆蓋基於成本的決策，但不會覆蓋語義檢查。
表擴充套件使用場景
最佳化器基於查詢中出現的謂詞條件對每個表必須被訪問的分割槽保持跟蹤。分割槽裁剪能讓最佳化器使用表擴充套件來生成更有效的執行計劃。
下面的例子假設滿足以下條件:
.想要對sh.sales表執行星型查詢，表sh.sales是基於time_id列進行範圍分割槽的一個分割槽表。
.想要禁用特定分割槽上的索引來檢視錶擴充套件的優點。
操作步驟如下:
1.以sh使用者登入資料庫
[oracle@jytest1 ~]$ sqlplus sh/*****@jypdb SQL*Plus: Release 12.2.0.1.0 Production on Wed Oct 31 18:09:54 2018 Copyright (c) 1982, 2016, Oracle. All rights reserved. Last Successful login time: Wed Oct 24 2018 17:00:11 +08:00 Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL>
2.執行以下查詢
SQL> select * from sales where time_id >= to_date('2000-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') and prod_id = 38;
...........
38 2470 24-DEC-01 2 999 1 31.47
38 13440 24-DEC-01 2 999 1 31.47
38 490 28-DEC-01 2 999 1 31.47
38 8406 28-DEC-01 2 999 1 31.47
38 1466 31-DEC-01 3 351 1 31.47
38 4340 31-DEC-01 3 351 1 31.47
38 10658 31-DEC-01 3 351 1 31.47
38 11390 31-DEC-01 3 351 1 31.47
38 23226 31-DEC-01 3 351 1 31.47
4224 rows selected.
3.查詢執行計劃
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced allstats last runstats_last peeked_binds'));
SQL_ID 214qgysqqz0k8, child number 0
-------------------------------------
select * from sales where time_id >= to_date('2000-01-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss') and prod_id = 38
Plan hash value: 2342444420
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 224 (100)| | | | 4224 |00:00:00.03 | 334 |
| 1 | PARTITION RANGE ITERATOR | | 1 | 5078 | 143K| 224 (0)| 00:00:01 | 13 | 28 | 4224 |00:00:00.03 | 334 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| SALES | 16 | 5078 | 143K| 224 (0)| 00:00:01 | 13 | 28 | 4224 |00:00:00.02 | 334 |
| 3 | BITMAP CONVERSION TO ROWIDS | | 8 | | | | | | | 4224 |00:00:00.01 | 24 |
|* 4 | BITMAP INDEX SINGLE VALUE | SALES_PROD_BIX | 8 | | | | | 13 | 28 | 8 |00:00:00.01 | 24 |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / SALES@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
DB_VERSION('12.2.0.1')
ALL_ROWS
NO_PARALLEL
OUTLINE_LEAF(@"SEL$1")
BITMAP_TREE(@"SEL$1" "SALES"@"SEL$1" AND(("SALES"."PROD_ID")))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "SALES"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("PROD_ID"=38)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "PROD_ID"[NUMBER,22], "SALES"."CUST_ID"[NUMBER,22], "SALES"."TIME_ID"[DATE,7], "SALES"."CHANNEL_ID"[NUMBER,22], "SALES"."PROMO_ID"[NUMBER,22],
"SALES"."QUANTITY_SOLD"[NUMBER,22], "SALES"."AMOUNT_SOLD"[NUMBER,22]
2 - "PROD_ID"[NUMBER,22], "SALES"."CUST_ID"[NUMBER,22], "SALES"."TIME_ID"[DATE,7], "SALES"."CHANNEL_ID"[NUMBER,22], "SALES"."PROMO_ID"[NUMBER,22],
"SALES"."QUANTITY_SOLD"[NUMBER,22], "SALES"."AMOUNT_SOLD"[NUMBER,22]
3 - "SALES".ROWID[ROWID,10], "PROD_ID"[NUMBER,22]
4 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7920], "PROD_ID"[NUMBER,22]
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
58 rows selected.
在執行計劃中的Pstart與Pstop列，顯示了最佳化器判斷只需要訪問表的13到28分割槽。在最佳化器已經判斷了被訪問的分割槽之後，它將考慮所有這些分割槽上可以使用的索引。在上面的執行計劃中，最佳化器選擇使用sales_prod_bix點陣圖索引
4.禁用sales表中sales_1995分割槽上的索引;
SQL> alter index sales_prod_bix modify partition sales_1995 unusable; Index altered.
5.再次執行之前的查詢語句，然後顯示執行計劃，可以看到執行計劃變成了由兩個子查詢組成的union all語句，第一個子查詢還是對13-28分割槽使用索引，第二個子查詢步驟對應的Pstart與Pstop為invalid，id=11的過濾條件為"PROD_ID"=38,id=9的過濾條件為"SALES"."TIME_ID"=TO_DATE(' 2000-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))這個過濾條件是為否的，所以過濾後的記錄為0，從對應的A-Rows列也可以看到記錄為0
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced allstats last runstats_last peeked_binds'));
SQL_ID 214qgysqqz0k8, child number 0
-------------------------------------
select * from sales where time_id >= to_date('2000-01-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss') and prod_id = 38
Plan hash value: 238952339
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 224 (100)| | | | 4224 |00:00:00.05 | 334 |
| 1 | VIEW | VW_TE_2 | 1 | 5079 | 431K| 224 (0)| 00:00:01 | | | 4224 |00:00:00.05 | 334 |
| 2 | UNION-ALL | | 1 | | | | | | | 4224 |00:00:00.05 | 334 |
| 3 | PARTITION RANGE ITERATOR | | 1 | 5078 | 143K| 224 (0)| 00:00:01 | 13 | 28 | 4224 |00:00:00.03 | 334 |
| 4 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | SALES | 16 | 5078 | 143K| 224 (0)| 00:00:01 | 13 | 28 | 4224 |00:00:00.02 | 334 |
| 5 | BITMAP CONVERSION TO ROWIDS | | 8 | | | | | | | 4224 |00:00:00.01 | 24 |
|* 6 | BITMAP INDEX SINGLE VALUE | SALES_PROD_BIX | 8 | | | | | 13 | 28 | 8 |00:00:00.01 | 24 |
|* 7 | FILTER | | 1 | | | | | | | 0 |00:00:00.01 | 0 |
| 8 | PARTITION RANGE EMPTY | | 0 | 1 | 29 | 1 (0)| 00:00:01 |INVALID|INVALID| 0 |00:00:00.01 | 0 |
|* 9 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| SALES | 0 | 1 | 29 | 1 (0)| 00:00:01 |INVALID|INVALID| 0 |00:00:00.01 | 0 |
| 10 | BITMAP CONVERSION TO ROWIDS | | 0 | | | | | | | 0 |00:00:00.01 | 0 |
|* 11 | BITMAP INDEX SINGLE VALUE | SALES_PROD_BIX | 0 | | | | |INVALID|INVALID| 0 |00:00:00.01 | 0 |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SET$D0A14387 / VW_TE_2@SEL$0A5B0FFE
2 - SET$D0A14387
3 - SET$D0A14387_1
4 - SET$D0A14387_1 / SALES@SEL$1
7 - SET$D0A14387_2
9 - SET$D0A14387_2 / SALES@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
DB_VERSION('12.2.0.1')
ALL_ROWS
NO_PARALLEL
OUTLINE_LEAF(@"SET$D0A14387_2")
OUTLINE_LEAF(@"SET$D0A14387_1")
OUTLINE_LEAF(@"SET$D0A14387")
EXPAND_TABLE(@"SEL$1" "SALES"@"SEL$1")
OUTLINE_LEAF(@"SEL$0A5B0FFE")
OUTLINE(@"SET$D0A14387")
EXPAND_TABLE(@"SEL$1" "SALES"@"SEL$1")
OUTLINE(@"SEL$1")
NO_ACCESS(@"SEL$0A5B0FFE" "VW_TE_2"@"SEL$0A5B0FFE")
BITMAP_TREE(@"SET$D0A14387_1" "SALES"@"SEL$1" AND(("SALES"."PROD_ID")))
BATCH_TABLE_ACCESS_BY_ROWID(@"SET$D0A14387_1" "SALES"@"SEL$1")
BITMAP_TREE(@"SET$D0A14387_2" "SALES"@"SEL$1" AND(("SALES"."PROD_ID")))
BATCH_TABLE_ACCESS_BY_ROWID(@"SET$D0A14387_2" "SALES"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("PROD_ID"=38)
7 - filter(NULL IS NOT NULL)
9 - filter(("SALES"."TIME_ID"=TO_DATE(' 2000-01-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss')))
11 - access("PROD_ID"=38)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "ITEM_1"[NUMBER,22], "ITEM_2"[NUMBER,22], "ITEM_3"[DATE,7], "ITEM_4"[NUMBER,22], "ITEM_5"[NUMBER,22], "ITEM_6"[NUMBER,22], "ITEM_7"[NUMBER,22]
2 - STRDEF[22], STRDEF[22], STRDEF[7], STRDEF[22], STRDEF[22], STRDEF[22], STRDEF[22]
3 - "SALES"."PROD_ID"[NUMBER,22], "SALES"."CUST_ID"[NUMBER,22], "SALES"."TIME_ID"[DATE,7], "SALES"."CHANNEL_ID"[NUMBER,22], "SALES"."PROMO_ID"[NUMBER,22],
"SALES"."QUANTITY_SOLD"[NUMBER,22], "SALES"."AMOUNT_SOLD"[NUMBER,22]
4 - "SALES"."PROD_ID"[NUMBER,22], "SALES"."CUST_ID"[NUMBER,22], "SALES"."TIME_ID"[DATE,7], "SALES"."CHANNEL_ID"[NUMBER,22], "SALES"."PROMO_ID"[NUMBER,22],
"SALES"."QUANTITY_SOLD"[NUMBER,22], "SALES"."AMOUNT_SOLD"[NUMBER,22]
5 - "SALES".ROWID[ROWID,10], "SALES"."PROD_ID"[NUMBER,22]
6 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7920], "SALES"."PROD_ID"[NUMBER,22]
7 - "SALES"."PROD_ID"[NUMBER,22], "SALES"."CUST_ID"[NUMBER,22], "SALES"."TIME_ID"[DATE,7], "SALES"."CHANNEL_ID"[NUMBER,22], "SALES"."PROMO_ID"[NUMBER,22],
"SALES"."QUANTITY_SOLD"[NUMBER,22], "SALES"."AMOUNT_SOLD"[NUMBER,22]
8 - "SALES"."PROD_ID"[NUMBER,22], "SALES"."CUST_ID"[NUMBER,22], "SALES"."TIME_ID"[DATE,7], "SALES"."CHANNEL_ID"[NUMBER,22], "SALES"."PROMO_ID"[NUMBER,22],
"SALES"."QUANTITY_SOLD"[NUMBER,22], "SALES"."AMOUNT_SOLD"[NUMBER,22]
9 - "SALES"."PROD_ID"[NUMBER,22], "SALES"."CUST_ID"[NUMBER,22], "SALES"."TIME_ID"[DATE,7], "SALES"."CHANNEL_ID"[NUMBER,22], "SALES"."PROMO_ID"[NUMBER,22],
"SALES"."QUANTITY_SOLD"[NUMBER,22], "SALES"."AMOUNT_SOLD"[NUMBER,22]
10 - "SALES".ROWID[ROWID,10], "SALES"."PROD_ID"[NUMBER,22]
11 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7920], "SALES"."PROD_ID"[NUMBER,22]
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
93 rows selected.
6.禁用分割槽28上的索引(sales_q4_2003),它是查詢需要訪問的一個分割槽:
SQL> alter index sales_prod_bix modify partition sales_q4_2003 unusable; Index altered. SQL> alter index sales_time_bix modify partition sales_q4_2003 unusable; Index altered.
透過禁用查詢需要訪問分割槽上的索引，查詢將不能再使用這些索引。
7.再次執行查詢語句，其執行計劃如下,執行計劃變成了由三個子查詢組成的union all語句，相比之前查詢多的第三個子查詢對錶sales的第28個分割槽執行全表掃描，這裡沒有索引可用，因為已經禁用28分割槽上的索引了。
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced allstats last runstats_last peeked_binds'));
SQL_ID 214qgysqqz0k8, child number 0
-------------------------------------
select * from sales where time_id >= to_date('2000-01-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss') and prod_id = 38
Plan hash value: 3857158179
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | Reads |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 225 (100)| | | | 4224 |00:00:00.20 | 334 | 44 |
| 1 | VIEW | VW_TE_2 | 1 | 5080 | 431K| 225 (0)| 00:00:01 | | | 4224 |00:00:00.20 | 334 | 44 |
| 2 | UNION-ALL | | 1 | | | | | | | 4224 |00:00:00.19 | 334 | 44 |
| 3 | PARTITION RANGE ITERATOR | | 1 | 5078 | 143K| 223 (0)| 00:00:01 | 13 | 27 | 4224 |00:00:00.17 | 334 | 44 |
| 4 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | SALES | 15 | 5078 | 143K| 223 (0)| 00:00:01 | 13 | 27 | 4224 |00:00:00.16 | 334 | 44 |
| 5 | BITMAP CONVERSION TO ROWIDS | | 8 | | | | | | | 4224 |00:00:00.03 | 24 | 16 |
|* 6 | BITMAP INDEX SINGLE VALUE | SALES_PROD_BIX | 8 | | | | | 13 | 27 | 8 |00:00:00.03 | 24 | 16 |
|* 7 | FILTER | | 1 | | | | | | | 0 |00:00:00.01 | 0 | 0 |
| 8 | PARTITION RANGE EMPTY | | 0 | 1 | 29 | 1 (0)| 00:00:01 |INVALID|INVALID| 0 |00:00:00.01 | 0 | 0 |
|* 9 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| SALES | 0 | 1 | 29 | 1 (0)| 00:00:01 |INVALID|INVALID| 0 |00:00:00.01 | 0 | 0 |
| 10 | BITMAP CONVERSION TO ROWIDS | | 0 | | | | | | | 0 |00:00:00.01 | 0 | 0 |
|* 11 | BITMAP INDEX SINGLE VALUE | SALES_PROD_BIX | 0 | | | | |INVALID|INVALID| 0 |00:00:00.01 | 0 | 0 |
| 12 | PARTITION RANGE SINGLE | | 1 | 1 | 87 | 2 (0)| 00:00:01 | 28 | 28 | 0 |00:00:00.01 | 0 | 0 |
|* 13 | TABLE ACCESS FULL | SALES | 1 | 1 | 87 | 2 (0)| 00:00:01 | 28 | 28 | 0 |00:00:00.01 | 0 | 0 |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SET$D0A14387 / VW_TE_2@SEL$0A5B0FFE
2 - SET$D0A14387
3 - SET$D0A14387_1
4 - SET$D0A14387_1 / SALES@SEL$1
7 - SET$D0A14387_2
9 - SET$D0A14387_2 / SALES@SEL$1
12 - SET$D0A14387_3
13 - SET$D0A14387_3 / SALES@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
DB_VERSION('12.2.0.1')
ALL_ROWS
NO_PARALLEL
OUTLINE_LEAF(@"SET$D0A14387_3")
OUTLINE_LEAF(@"SET$D0A14387_2")
OUTLINE_LEAF(@"SET$D0A14387_1")
OUTLINE_LEAF(@"SET$D0A14387")
EXPAND_TABLE(@"SEL$1" "SALES"@"SEL$1")
OUTLINE_LEAF(@"SEL$0A5B0FFE")
OUTLINE(@"SET$D0A14387")
EXPAND_TABLE(@"SEL$1" "SALES"@"SEL$1")
OUTLINE(@"SEL$1")
NO_ACCESS(@"SEL$0A5B0FFE" "VW_TE_2"@"SEL$0A5B0FFE")
BITMAP_TREE(@"SET$D0A14387_1" "SALES"@"SEL$1" AND(("SALES"."PROD_ID")))
BATCH_TABLE_ACCESS_BY_ROWID(@"SET$D0A14387_1" "SALES"@"SEL$1")
BITMAP_TREE(@"SET$D0A14387_2" "SALES"@"SEL$1" AND(("SALES"."PROD_ID")))
BATCH_TABLE_ACCESS_BY_ROWID(@"SET$D0A14387_2" "SALES"@"SEL$1")
FULL(@"SET$D0A14387_3" "SALES"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("PROD_ID"=38)
7 - filter(NULL IS NOT NULL)
9 - filter(("SALES"."TIME_ID"=TO_DATE(' 2000-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
11 - access("PROD_ID"=38)
13 - filter("PROD_ID"=38)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "ITEM_1"[NUMBER,22], "ITEM_2"[NUMBER,22], "ITEM_3"[DATE,7], "ITEM_4"[NUMBER,22], "ITEM_5"[NUMBER,22], "ITEM_6"[NUMBER,22], "ITEM_7"[NUMBER,22]
2 - STRDEF[22], STRDEF[22], STRDEF[7], STRDEF[22], STRDEF[22], STRDEF[22], STRDEF[22]
3 - "SALES"."PROD_ID"[NUMBER,22], "SALES"."CUST_ID"[NUMBER,22], "SALES"."TIME_ID"[DATE,7], "SALES"."CHANNEL_ID"[NUMBER,22], "SALES"."PROMO_ID"[NUMBER,22],
"SALES"."QUANTITY_SOLD"[NUMBER,22], "SALES"."AMOUNT_SOLD"[NUMBER,22]
4 - "SALES"."PROD_ID"[NUMBER,22], "SALES"."CUST_ID"[NUMBER,22], "SALES"."TIME_ID"[DATE,7], "SALES"."CHANNEL_ID"[NUMBER,22], "SALES"."PROMO_ID"[NUMBER,22],
"SALES"."QUANTITY_SOLD"[NUMBER,22], "SALES"."AMOUNT_SOLD"[NUMBER,22]
5 - "SALES".ROWID[ROWID,10], "SALES"."PROD_ID"[NUMBER,22]
6 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7920], "SALES"."PROD_ID"[NUMBER,22]
7 - "SALES"."PROD_ID"[NUMBER,22], "SALES"."CUST_ID"[NUMBER,22], "SALES"."TIME_ID"[DATE,7], "SALES"."CHANNEL_ID"[NUMBER,22], "SALES"."PROMO_ID"[NUMBER,22],
"SALES"."QUANTITY_SOLD"[NUMBER,22], "SALES"."AMOUNT_SOLD"[NUMBER,22]
8 - "SALES"."PROD_ID"[NUMBER,22], "SALES"."CUST_ID"[NUMBER,22], "SALES"."TIME_ID"[DATE,7], "SALES"."CHANNEL_ID"[NUMBER,22], "SALES"."PROMO_ID"[NUMBER,22],
"SALES"."QUANTITY_SOLD"[NUMBER,22], "SALES"."AMOUNT_SOLD"[NUMBER,22]
9 - "SALES"."PROD_ID"[NUMBER,22], "SALES"."CUST_ID"[NUMBER,22], "SALES"."TIME_ID"[DATE,7], "SALES"."CHANNEL_ID"[NUMBER,22], "SALES"."PROMO_ID"[NUMBER,22],
"SALES"."QUANTITY_SOLD"[NUMBER,22], "SALES"."AMOUNT_SOLD"[NUMBER,22]
10 - "SALES".ROWID[ROWID,10], "SALES"."PROD_ID"[NUMBER,22]
11 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7920], "SALES"."PROD_ID"[NUMBER,22]
12 - "SALES"."PROD_ID"[NUMBER,22], "SALES"."CUST_ID"[NUMBER,22], "SALES"."TIME_ID"[DATE,7], "SALES"."CHANNEL_ID"[NUMBER,22], "SALES"."PROMO_ID"[NUMBER,22],
"SALES"."QUANTITY_SOLD"[NUMBER,22], "SALES"."AMOUNT_SOLD"[NUMBER,22]
13 - "SALES"."PROD_ID"[NUMBER,22], "SALES"."CUST_ID"[NUMBER,22], "SALES"."TIME_ID"[DATE,7], "SALES"."CHANNEL_ID"[NUMBER,22], "SALES"."PROMO_ID"[NUMBER,22],
"SALES"."QUANTITY_SOLD"[NUMBER,22], "SALES"."AMOUNT_SOLD"[NUMBER,22]
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
103 rows selected.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26015009/viewspace-2218218/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle 12CR2查詢轉換之臨時錶轉換Oracle
- Oracle 12CR2查詢轉換之cursor-duration臨時表Oracle
- Oracle 12CR2查詢轉換之謂詞推送Oracle
- Oracle 12CR2查詢轉換之星型轉換Oracle
- Oracle 12CR2查詢轉換之檢視合併Oracle
- 【SQL】Oracle查詢轉換之 OR用法SQLOracle
- Oracle 查詢轉換Oracle
- ORACLE 擴充套件Oracle套件
- 【SQL】Oracle查詢轉換之謂詞推送SQLOracle
- 【SQL】Oracle查詢轉換之物化檢視查詢重寫SQLOracle
- Mybatis之map操作使用者和模糊查詢擴充套件MyBatis套件
- Oracle 查詢轉換-01 or expansionOracle
- 【SQL】Oracle查詢轉換之檢視合併SQLOracle
- Oracle 查詢轉換-02 View MergingOracleView
- Oracle 查詢轉換-03 Predicate PushingOracle
- Oracle 查詢轉換-04 Subquery UnnestingOracle
- Java SE（12） 擴充套件——進位制轉換Java套件
- 並查集擴充套件並查集套件
- ASP.NET Core擴充套件庫之Http通用擴充套件ASP.NET套件HTTP
- 在EF Core 中使用AsQueryable擴充套件方法的拼接查詢套件
- kotlin 擴充套件（擴充套件函式和擴充套件屬性）Kotlin套件函式
- 並查集擴充套件應用並查集套件
- 開源 - Ideal庫 - 常用時間轉換擴充套件方法（二）Idea套件
- 開源 - Ideal庫 - 常用時間轉換擴充套件方法（一）Idea套件
- Scrapy之pipeline&擴充套件套件
- centos安裝php的oracle擴充套件CentOSPHPOracle套件
- PostgreSQL 原始碼解讀（98）- 分割槽表#4（資料查詢路由#1-“擴充套件”分割槽表）SQL原始碼路由套件
- 表單驗證使用擴充套件套件
- [擴充套件推薦]簡體轉繁體/繁體轉簡體 OpenCC-PHP 擴充套件套件PHP
- 開源 - Ideal庫 - 常用時間轉換擴充套件方法（二）QvIdea套件
- 記一次詭異的Oracle查詢轉換Oracle
- js陣列擴充套件用lamada表示式實現查詢是否存在JS陣列套件
- CSS——讓這些“展示”有更好的擴充套件性——媒體查詢CSS套件
- 【Kotlin】擴充套件屬性、擴充套件函式Kotlin套件函式
- PHP 操作 Redis 之 phpredis 擴充套件PHPRedis套件
- burpsuite擴充套件開發之PythonUI套件Python
- Dubbo系列之 （一）SPI擴充套件套件
- DOM擴充套件之HTML5套件HTML