共计 9523 个字符，预计需要花费 24 分钟才能阅读完成。
这篇文章给大家分享的是有关 Oracle12CR2 查询转换之表扩展的示例分析的内容。丸趣 TV 小编觉得挺实用的，因此分享给大家做个参考，一起跟随丸趣 TV 小编过来看看吧。
表扩展工作原理
表分区使用表扩展成为可能。如果在一个分区表上创建一个本地索引，那么优化器可能会标记索引对于特定的分区不可使用。实际有些分区没有创建索引。在表扩展中，优化器将查询转换为一个 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
SQL2. 执行以下查询
SQL select * from sales where time_id = to_date(2000-01-01 00:00:00 , syyyy-mm-dd hh34: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 hh34: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]
-----
- 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 hh34: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 hh34: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
hh34: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]
-----
- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
正文完
