好妈妈在线观看中文版,国产成年无码V片在线

主頁(yè) > 知識(shí)庫(kù) > Oracle 12CR2查詢(xún)轉(zhuǎn)換教程之表擴(kuò)展詳解

Oracle 12CR2查詢(xún)轉(zhuǎn)換教程之表擴(kuò)展詳解

前言

在表擴(kuò)展中，對(duì)于讀取一個(gè)分區(qū)表部分?jǐn)?shù)據(jù)時(shí)優(yōu)化器會(huì)生成使用索引的執(zhí)行計(jì)劃。基于索引執(zhí)行計(jì)劃可以提高性能，但索引維護(hù)會(huì)增加開(kāi)鎖。在許多數(shù)據(jù)庫(kù)中，DML只影響小部分?jǐn)?shù)據(jù)。對(duì)于頻繁更新的表表擴(kuò)展使用基于索引的執(zhí)行計(jì)劃。你可以在以讀取為主的數(shù)據(jù)上創(chuàng)建一個(gè)索引，在以頻繁變化的數(shù)據(jù)上消除索引開(kāi)銷(xiāo)。通過(guò)這種方式，表擴(kuò)展在避免索引維護(hù)的同時(shí)提高了性能。

下面話不多說(shuō)了，來(lái)一起看看詳細(xì)的介紹吧

表擴(kuò)展工作原理

表分區(qū)使用表擴(kuò)展成為可能。如果在一個(gè)分區(qū)表上創(chuàng)建一個(gè)本地索引，那么優(yōu)化器可能會(huì)標(biāo)記索引對(duì)于特定的分區(qū)不可使用。實(shí)際有些分區(qū)沒(méi)有創(chuàng)建索引。在表擴(kuò)展中，優(yōu)化器將查詢(xún)轉(zhuǎn)換為一個(gè)union all語(yǔ)句，讓一些子查詢(xún)?cè)L問(wèn)創(chuàng)建索引的分區(qū)，一些子查詢(xún)?cè)L問(wèn)沒(méi)有創(chuàng)建索引的分區(qū)。優(yōu)化器可以為每個(gè)分區(qū)選擇最有效的訪問(wèn)路徑，而不管它是否存在于查詢(xún)所要訪問(wèn)的所有分區(qū)中。

優(yōu)化器不總是會(huì)選擇表擴(kuò)展

.表擴(kuò)展是基于成本

當(dāng)數(shù)據(jù)庫(kù)訪問(wèn)擴(kuò)展表的每個(gè)分區(qū)只會(huì)跨越union all的所有分支一次，數(shù)據(jù)庫(kù)所連接的任何表都是在分支中被訪問(wèn)。

.語(yǔ)義問(wèn)題可能導(dǎo)致表擴(kuò)展無(wú)效

例如，一個(gè)表出現(xiàn)在一個(gè)外連接的右邊對(duì)于表擴(kuò)展來(lái)說(shuō)是無(wú)效的。

可以使用expand_table hint來(lái)控制表擴(kuò)展。這個(gè)hint會(huì)覆蓋基于成本的決策，但不會(huì)覆蓋語(yǔ)義檢查。

表擴(kuò)展使用場(chǎng)景

優(yōu)化器基于查詢(xún)中出現(xiàn)的謂詞條件對(duì)每個(gè)表必須被訪問(wèn)的分區(qū)保持跟蹤。分區(qū)裁剪能讓優(yōu)化器使用表擴(kuò)展來(lái)生成更有效的執(zhí)行計(jì)劃。

下面的例子假設(shè)滿足以下條件:

.想要對(duì)sh.sales表執(zhí)行星型查詢(xún)，表sh.sales是基于time_id列進(jìn)行范圍分區(qū)的一個(gè)分區(qū)表。

.想要禁用特定分區(qū)上的索引來(lái)查看表擴(kuò)展的優(yōu)點(diǎn)。

操作步驟如下:

1.以sh用戶登錄數(shù)據(jù)庫(kù)

[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í)行以下查詢(xún)

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.查詢(xún)執(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)化器判斷只需要訪問(wèn)表的13到28分區(qū)。在優(yōu)化器已經(jīng)判斷了被訪問(wèn)的分區(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í)行之前的查詢(xún)語(yǔ)句，然后顯示執(zhí)行計(jì)劃，可以看到執(zhí)行計(jì)劃變成了由兩個(gè)子查詢(xún)組成的union all語(yǔ)句，第一個(gè)子查詢(xún)還是對(duì)13-28分區(qū)使用索引，第二個(gè)子查詢(xún)步驟對(duì)應(yīng)的Pstart與Pstop為invalid，id=11的過(guò)濾條件為”P(pán)ROD_ID”=38,id=9的過(guò)濾條件為”SALES”.”TIME_ID”=TO_DATE(‘ 2000-01-01 00:00:00', ‘syyyy-mm-dd hh24:mi:ss')))這個(gè)過(guò)濾條件是為否的，所以過(guò)濾后的記錄為0，從對(duì)應(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),它是查詢(xún)需要訪問(wèn)的一個(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.

通過(guò)禁用查詢(xún)需要訪問(wèn)分區(qū)上的索引，查詢(xún)將不能再使用這些索引。

7.再次執(zhí)行查詢(xún)語(yǔ)句，其執(zhí)行計(jì)劃如下,執(zhí)行計(jì)劃變成了由三個(gè)子查詢(xún)組成的union all語(yǔ)句，相比之前查詢(xún)多的第三個(gè)子查詢(xún)對(duì)表sales的第28個(gè)分區(qū)執(zhí)行全表掃描，這里沒(méi)有索引可用，因?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é)

以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，如果有疑問(wèn)大家可以留言交流，謝謝大家對(duì)腳本之家的支持。

您可能感興趣的文章:

Oracle 12CR2查詢(xún)轉(zhuǎn)換教程之cursor-duration臨時(shí)表詳解
oracle 臨時(shí)表詳解及實(shí)例
對(duì)比Oracle臨時(shí)表和SQL Server臨時(shí)表的不同點(diǎn)
Oracle 12CR2查詢(xún)轉(zhuǎn)換教程之臨時(shí)表轉(zhuǎn)換詳解

標(biāo)簽：焦作郴州許昌合肥南充滁州遼源涼山

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《Oracle 12CR2查詢(xún)轉(zhuǎn)換教程之表擴(kuò)展詳解》，本文關(guān)鍵詞 Oracle,12CR2,查詢(xún),轉(zhuǎn)換,教程,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題，煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無(wú)關(guān)。