Oracle12CR2查询转换之表扩展的示例分析

2025-03-24 技术教程

这篇文章给大家分享的是有关Oracle12CR2查询转换之表扩展的示例分析的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。

表扩展工作原理

表分区使用表扩展成为可能。如果在一个分区表上创建一个本地索引，那么优化器可能会标记索引对于特定的分区不可使用。实际有些分区没有创建索引。在表扩展中，优化器将查询转换为一个union all语句，让一些子查询访问创建索引的分区，一些子查询访问没有创建索引的分区。优化器可以为每个分区选择最有效的访问路径，而不管它是否存在于查询所要访问的所有分区中。

优化器不总是会选择表扩展

.表扩展是基于成本

当数据库访问扩展表的每个分区只会跨越union all的所有分支一次，数据库所连接的任何表都是在分支中被访问。

.语义问题可能导致表扩展无效

例如，一个表出现在一个外连接的右边对于表扩展来说是无效的。

可以使用expand_table hint来控制表扩展。这个hint会覆盖基于成本的决策，但不会覆盖语义检查。

表扩展使用场景

优化器基于查询中出现的谓词条件对每个表必须被访问的分区保持跟踪。分区裁剪能让优化器使用表扩展来生成更有效的执行计划。

下面的例子假设满足以下条件:

.想要对sh.sales表执行星型查询，表sh.sales是基于time_id列进行范围分区的一个分区表。

.想要禁用特定分区上的索引来查看表扩展的优点。

操作步骤如下:

1.以sh用户登录数据库

[oracle@jytest1~]$sqlplussh/*****@jypdbSQL*Plus:Release12.2.0.1.0ProductiononWedOct3118:09:542018Copyright(c)1982,2016,Oracle.Allrightsreserved.LastSuccessfullogintime:WedOct24201817:00:11+08:00Connectedto:OracleDatabase12cEnterpriseEditionRelease12.2.0.1.0-64bitProductionSQL>

2.执行以下查询

SQL>select*fromsaleswheretime_id>=to_date('2000-01-0100:00:00','syyyy-mm-ddhh34:mi:ss')andprod_id=38;...........38247024-DEC-012999131.47381344024-DEC-012999131.473849028-DEC-012999131.4738840628-DEC-012999131.4738146631-DEC-013351131.4738434031-DEC-013351131.47381065831-DEC-013351131.47381139031-DEC-013351131.47382322631-DEC-013351131.474224rowsselected.

3.查询执行计划

在执行计划中的Pstart与Pstop列，显示了优化器判断只需要访问表的13到28分区。在优化器已经判断了被访问的分区之后，它将考虑所有这些分区上可以使用的索引。在上面的执行计划中，优化器选择使用sales_prod_bix位图索引

4.禁用sales表中sales_1995分区上的索引;

SQL>alterindexsales_prod_bixmodifypartitionsales_1995unusable;Indexaltered.

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*fromtable(dbms_xplan.display_cursor(null,null,'advancedallstatslastrunstats_lastpeeked_binds'));SQL_ID214qgysqqz0k8,childnumber0-------------------------------------select*fromsaleswheretime_id>=to_date('2000-01-0100:00:00','syyyy-mm-ddhh34:mi:ss')andprod_id=38Planhashvalue:238952339--------------------------------------------------------------------------------------------------------------------------------------------------------------------------|Id|Operation|Name|Starts|E-Rows|E-Bytes|Cost(%CPU)|E-Time|Pstart|Pstop|A-Rows|A-Time|Buffers|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||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|PARTITIONRANGEITERATOR||1|5078|143K|224(0)|00:00:01|13|28|4224|00:00:00.03|334||4|TABLEACCESSBYLOCALINDEXROWIDBATCHED|SALES|16|5078|143K|224(0)|00:00:01|13|28|4224|00:00:00.02|334||5|BITMAPCONVERSIONTOROWIDS||8|||||||4224|00:00:00.01|24||*6|BITMAPINDEXSINGLEVALUE|SALES_PROD_BIX|8|||||13|28|8|00:00:00.01|24||*7|FILTER||1|||||||0|00:00:00.01|0||8|PARTITIONRANGEEMPTY||0|1|29|1(0)|00:00:01|INVALID|INVALID|0|00:00:00.01|0||*9|TABLEACCESSBYLOCALINDEXROWIDBATCHED|SALES|0|1|29|1(0)|00:00:01|INVALID|INVALID|0|00:00:00.01|0||10|BITMAPCONVERSIONTOROWIDS||0|||||||0|00:00:00.01|0||*11|BITMAPINDEXSINGLEVALUE|SALES_PROD_BIX|0|||||INVALID|INVALID|0|00:00:00.01|0|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------QueryBlockName/ObjectAlias(identifiedbyoperationid):-------------------------------------------------------------1-SET$D0A14387/VW_TE_2@SEL$0A5B0FFE2-SET$D0A143873-SET$D0A14387_14-SET$D0A14387_1/SALES@SEL$17-SET$D0A14387_29-SET$D0A14387_2/SALES@SEL$1OutlineData-------------/*+BEGIN_OUTLINE_DATAIGNORE_OPTIM_EMBEDDED_HINTSOPTIMIZER_FEATURES_ENABLE('12.2.0.1')DB_VERSION('12.2.0.1')ALL_ROWSNO_PARALLELOUTLINE_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*/PredicateInformation(identifiedbyoperationid):---------------------------------------------------6-access("PROD_ID"=38)7-filter(NULLISNOTNULL)9-filter(("SALES"."TIME_ID"=TO_DATE('2000-01-0100:00:00','syyyy-mm-ddhh34:mi:ss')))11-access("PROD_ID"=38)ColumnProjectionInformation(identifiedbyoperationid):-----------------------------------------------------------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[BMVAR,10],STRDEF[BMVAR,10],STRDEF[BMVAR,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[BMVAR,10],STRDEF[BMVAR,10],STRDEF[BMVAR,7920],"SALES"."PROD_ID"[NUMBER,22]Note------automaticDOP:ComputedDegreeofParallelismis1becauseofparallelthreshold93rowsselected.

6.禁用分区28上的索引(sales_q4_2003),它是查询需要访问的一个分区:

SQL>alterindexsales_prod_bixmodifypartitionsales_q4_2003unusable;Indexaltered.SQL>alterindexsales_time_bixmodifypartitionsales_q4_2003unusable;Indexaltered.

通过禁用查询需要访问分区上的索引，查询将不能再使用这些索引。

7.再次执行查询语句，其执行计划如下,执行计划变成了由三个子查询组成的union all语句，相比之前查询多的第三个子查询对表sales的第28个分区执行全表扫描，这里没有索引可用，因为已经禁用28分区上的索引了。

SQL>select*fromtable(dbms_xplan.display_cursor(null,null,'advancedallstatslastrunstats_lastpeeked_binds'));SQL_ID214qgysqqz0k8,childnumber0-------------------------------------select*fromsaleswheretime_id>=to_date('2000-01-0100:00:00','syyyy-mm-ddhh34:mi:ss')andprod_id=38Planhashvalue:3857158179-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|Id|Operation|Name|Starts|E-Rows|E-Bytes|Cost(%CPU)|E-Time|Pstart|Pstop|A-Rows|A-Time|Buffers|Reads|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||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|PARTITIONRANGEITERATOR||1|5078|143K|223(0)|00:00:01|13|27|4224|00:00:00.17|334|44||4|TABLEACCESSBYLOCALINDEXROWIDBATCHED|SALES|15|5078|143K|223(0)|00:00:01|13|27|4224|00:00:00.16|334|44||5|BITMAPCONVERSIONTOROWIDS||8|||||||4224|00:00:00.03|24|16||*6|BITMAPINDEXSINGLEVALUE|SALES_PROD_BIX|8|||||13|27|8|00:00:00.03|24|16||*7|FILTER||1|||||||0|00:00:00.01|0|0||8|PARTITIONRANGEEMPTY||0|1|29|1(0)|00:00:01|INVALID|INVALID|0|00:00:00.01|0|0||*9|TABLEACCESSBYLOCALINDEXROWIDBATCHED|SALES|0|1|29|1(0)|00:00:01|INVALID|INVALID|0|00:00:00.01|0|0||10|BITMAPCONVERSIONTOROWIDS||0|||||||0|00:00:00.01|0|0||*11|BITMAPINDEXSINGLEVALUE|SALES_PROD_BIX|0|||||INVALID|INVALID|0|00:00:00.01|0|0||12|PARTITIONRANGESINGLE||1|1|87|2(0)|00:00:01|28|28|0|00:00:00.01|0|0||*13|TABLEACCESSFULL|SALES|1|1|87|2(0)|00:00:01|28|28|0|00:00:00.01|0|0|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------QueryBlockName/ObjectAlias(identifiedbyoperationid):-------------------------------------------------------------1-SET$D0A14387/VW_TE_2@SEL$0A5B0FFE2-SET$D0A143873-SET$D0A14387_14-SET$D0A14387_1/SALES@SEL$17-SET$D0A14387_29-SET$D0A14387_2/SALES@SEL$112-SET$D0A14387_313-SET$D0A14387_3/SALES@SEL$1OutlineData-------------/*+BEGIN_OUTLINE_DATAIGNORE_OPTIM_EMBEDDED_HINTSOPTIMIZER_FEATURES_ENABLE('12.2.0.1')DB_VERSION('12.2.0.1')ALL_ROWSNO_PARALLELOUTLINE_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*/PredicateInformation(identifiedbyoperationid):---------------------------------------------------6-access("PROD_ID"=38)7-filter(NULLISNOTNULL)9-filter(("SALES"."TIME_ID"=TO_DATE('2000-01-0100:00:00','syyyy-mm-ddhh34:mi:ss')))11-access("PROD_ID"=38)13-filter("PROD_ID"=38)ColumnProjectionInformation(identifiedbyoperationid):-----------------------------------------------------------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[BMVAR,10],STRDEF[BMVAR,10],STRDEF[BMVAR,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[BMVAR,10],STRDEF[BMVAR,10],STRDEF[BMVAR,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------automaticDOP:ComputedDegreeofParallelismis1becauseofparallelthreshold103rowsselected.

感谢各位的阅读！关于“Oracle12CR2查询转换之表扩展的示例分析”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！