Oracle 12CR2查詢轉(zhuǎn)換教程之表擴(kuò)展詳解
更新時(shí)間：2018年11月01日 09:52:17 作者：jydba
Oracle 12cR2版本已經(jīng)發(fā)布有一段時(shí)間，下面這篇文章主要給大家介紹了關(guān)于Oracle 12CR2查詢轉(zhuǎn)換教程之表擴(kuò)展的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，需要的朋友可以參考借鑒，下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧
前言
在表擴(kuò)展中，對于讀取一個(gè)分區(qū)表部分?jǐn)?shù)據(jù)時(shí)優(yōu)化器會生成使用索引的執(zhí)行計(jì)劃?；谒饕龍?zhí)行計(jì)劃可以提高性能，但索引維護(hù)會增加開鎖。在許多數(shù)據(jù)庫中，DML只影響小部分?jǐn)?shù)據(jù)。對于頻繁更新的表表擴(kuò)展使用基于索引的執(zhí)行計(jì)劃。你可以在以讀取為主的數(shù)據(jù)上創(chuàng)建一個(gè)索引，在以頻繁變化的數(shù)據(jù)上消除索引開銷。通過這種方式，表擴(kuò)展在避免索引維護(hù)的同時(shí)提高了性能。
下面話不多說了，來一起看看詳細(xì)的介紹吧
表擴(kuò)展工作原理
表分區(qū)使用表擴(kuò)展成為可能。如果在一個(gè)分區(qū)表上創(chuàng)建一個(gè)本地索引，那么優(yōu)化器可能會標(biāo)記索引對于特定的分區(qū)不可使用。實(shí)際有些分區(qū)沒有創(chuàng)建索引。在表擴(kuò)展中，優(yōu)化器將查詢轉(zhuǎn)換為一個(gè)union all語句，讓一些子查詢訪問創(chuàng)建索引的分區(qū)，一些子查詢訪問沒有創(chuàng)建索引的分區(qū)。優(yōu)化器可以為每個(gè)分區(qū)選擇最有效的訪問路徑，而不管它是否存在于查詢所要訪問的所有分區(qū)中。
優(yōu)化器不總是會選擇表擴(kuò)展
.表擴(kuò)展是基于成本
當(dāng)數(shù)據(jù)庫訪問擴(kuò)展表的每個(gè)分區(qū)只會跨越union all的所有分支一次，數(shù)據(jù)庫所連接的任何表都是在分支中被訪問。
.語義問題可能導(dǎo)致表擴(kuò)展無效
例如，一個(gè)表出現(xiàn)在一個(gè)外連接的右邊對于表擴(kuò)展來說是無效的。
可以使用expand_table hint來控制表擴(kuò)展。這個(gè)hint會覆蓋基于成本的決策，但不會覆蓋語義檢查。
表擴(kuò)展使用場景
優(yōu)化器基于查詢中出現(xiàn)的謂詞條件對每個(gè)表必須被訪問的分區(qū)保持跟蹤。分區(qū)裁剪能讓優(yōu)化器使用表擴(kuò)展來生成更有效的執(zhí)行計(jì)劃。
下面的例子假設(shè)滿足以下條件:
.想要對sh.sales表執(zhí)行星型查詢，表sh.sales是基于time_id列進(jìn)行范圍分區(qū)的一個(gè)分區(qū)表。
.想要禁用特定分區(qū)上的索引來查看表擴(kuò)展的優(yōu)點(diǎn)。
操作步驟如下:
1.以sh用戶登錄數(shù)據(jù)庫
[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.執(zhí)行以下查詢
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.查詢執(zhí)行計(jì)劃
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.
在執(zhí)行計(jì)劃中的Pstart與Pstop列，顯示了優(yōu)化器判斷只需要訪問表的13到28分區(qū)。在優(yōu)化器已經(jīng)判斷了被訪問的分區(qū)之后，它將考慮所有這些分區(qū)上可以使用的索引。在上面的執(zhí)行計(jì)劃中，優(yōu)化器選擇使用sales_prod_bix位圖索引
4.禁用sales表中sales_1995分區(qū)上的索引;
SQL> alter index sales_prod_bix modify partition sales_1995 unusable; Index altered.
5.再次執(zhí)行之前的查詢語句，然后顯示執(zhí)行計(jì)劃，可以看到執(zhí)行計(jì)劃變成了由兩個(gè)子查詢組成的union all語句，第一個(gè)子查詢還是對13-28分區(qū)使用索引，第二個(gè)子查詢步驟對應(yīng)的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')))這個(gè)過濾條件是為否的，所以過濾后的記錄為0，從對應(yīng)的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.禁用分區(qū)28上的索引(sales_q4_2003),它是查詢需要訪問的一個(gè)分區(qū):
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.
通過禁用查詢需要訪問分區(qū)上的索引，查詢將不能再使用這些索引。
7.再次執(zhí)行查詢語句，其執(zhí)行計(jì)劃如下,執(zhí)行計(jì)劃變成了由三個(gè)子查詢組成的union all語句，相比之前查詢多的第三個(gè)子查詢對表sales的第28個(gè)分區(qū)執(zhí)行全表掃描，這里沒有索引可用，因?yàn)橐呀?jīng)禁用28分區(qū)上的索引了。
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.
總結(jié)
以上就是這篇文章的全部內(nèi)容了，希望本文的內(nèi)容對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，如果有疑問大家可以留言交流，謝謝大家對腳本之家的支持。
相關(guān)文章
深入探討:Oracle中如何查詢正鎖表的用戶以及釋放被鎖的表的方法
本篇文章是對Oracle中查詢正鎖表的用戶以及釋放被鎖的表的方法進(jìn)行了詳細(xì)的分析介紹，需要的朋友參考下2013-05-05
Oracle數(shù)據(jù)庫中保留小數(shù)點(diǎn)后兩位的問題解讀
在Oracle數(shù)據(jù)庫中,對數(shù)字和百分比進(jìn)行格式化,以保留兩位小數(shù),主要使用to_char()函數(shù),對于大數(shù)字如10000000.12,使用to_char(字段名, 'FM99999999999990.00')可確保保留兩位小數(shù)而無額外空格,對于百分比如86.63%2024-09-09
關(guān)于Oracle Dataguard 日志傳輸狀態(tài)監(jiān)控問題
ORACLE DATAGUARD的主備庫同步，主要是依靠日志傳輸?shù)絺鋷?，備庫?yīng)用日志或歸檔來實(shí)現(xiàn)。這篇文章主要給大家介紹了關(guān)于Oracle Dataguard 日志傳輸狀態(tài)監(jiān)控問題，感興趣的朋友跟隨小編一起看看吧2019-05-05
Oracle和DB2都是關(guān)系型數(shù)據(jù)庫管理系統(tǒng)(RDBMS),但它們的設(shè)計(jì)理念、特性及性能等方面存在一些顯著的差異,本文將詳細(xì)比較Oracle和DB2,幫助企業(yè)和數(shù)據(jù)庫管理員了解這兩個(gè)系統(tǒng)各自的優(yōu)勢和局限性,并指導(dǎo)用戶根據(jù)自己的業(yè)務(wù)需求作出合適的選擇2024-02-02
Oracle?19c創(chuàng)建數(shù)據(jù)庫的完整步驟(詳細(xì)明了)
Oracle19c可以用于創(chuàng)建數(shù)據(jù)庫與表空間,創(chuàng)建表空間特別慢是因?yàn)楹笈_進(jìn)程多,拉低進(jìn)程速度導(dǎo)致的,下面這篇文章主要給大家介紹了關(guān)于Oracle?19c創(chuàng)建數(shù)據(jù)庫的完整步驟,需要的朋友可以參考下2023-04-04
oracle數(shù)據(jù)庫中sql%notfound的用法詳解
SQL%NOTFOUND 是一個(gè)布爾值。下面通過本文給大家分享oracle數(shù)據(jù)庫中sql%notfound的用法，需要的的朋友參考下吧2017-06-06
oracle存儲過程創(chuàng)建表分區(qū)實(shí)例
二種oracle存儲過程創(chuàng)建表分區(qū)實(shí)例2013-11-11
distinct這個(gè)關(guān)鍵字來過濾掉多余的重復(fù)記錄只保留一條，但往往只用它來返回不重復(fù)記錄的條數(shù)，而不是用它來返回不重記錄的所有值。其原因是distinct只有用二重循環(huán)查詢來解決，而這樣對于一個(gè)數(shù)據(jù)量非常大的站來說，無疑是會直接影響到效率的。2015-09-09
巧妙解決Oracle NClob讀寫問題(經(jīng)驗(yàn)分享)
下面小編就為大家?guī)硪黄擅罱鉀QOracle NClob讀寫問題(經(jīng)驗(yàn)分享)。小編覺得挺不錯(cuò)的，現(xiàn)在就分享給大家，也給大家做個(gè)參考。一起跟隨小編過來看看吧2017-10-10
通過sql準(zhǔn)確查出一天數(shù)據(jù)的方法
之前做一個(gè)項(xiàng)目的時(shí)候想用sql查出某人一天做的數(shù)據(jù)，但怎么查都沒有準(zhǔn)確查出。最近通過查閱萬卷書籍終于知道了如何準(zhǔn)確查出一天的數(shù)據(jù)。所以想著總結(jié)出來分享給大家，或許對有需要的朋友們能帶來一定的幫助，下面來一起看看吧。2016-12-12