Oracle 12CR2查询转换教程之表扩展详解
前言
在表扩展中,对于读取一个分区表部分数据时优化器会生成使用索引的执行计划。基于索引执行计划可以提高性能,但索引维护会增加开锁。在许多数据库中,DML只影响小部分数据。对于频繁更新的表表扩展使用基于索引的执行计划。你可以在以读取为主的数据上创建一个索引,在以频繁变化的数据上消除索引开销。通过这种方式,表扩展在避免索引维护的同时提高了性能。
下面话不多说了,来一起看看详细的介绍吧
表扩展工作原理
表分区使用表扩展成为可能。如果在一个分区表上创建一个本地索引,那么优化器可能会标记索引对于特定的分区不可使用。实际有些分区没有创建索引。在表扩展中,优化器将查询转换为一个unionall语句,让一些子查询访问创建索引的分区,一些子查询访问没有创建索引的分区。优化器可以为每个分区选择最有效的访问路径,而不管它是否存在于查询所要访问的所有分区中。
优化器不总是会选择表扩展
.表扩展是基于成本
当数据库访问扩展表的每个分区只会跨越unionall的所有分支一次,数据库所连接的任何表都是在分支中被访问。
.语义问题可能导致表扩展无效
例如,一个表出现在一个外连接的右边对于表扩展来说是无效的。
可以使用expand_tablehint来控制表扩展。这个hint会覆盖基于成本的决策,但不会覆盖语义检查。
表扩展使用场景
优化器基于查询中出现的谓词条件对每个表必须被访问的分区保持跟踪。分区裁剪能让优化器使用表扩展来生成更有效的执行计划。
下面的例子假设满足以下条件:
.想要对sh.sales表执行星型查询,表sh.sales是基于time_id列进行范围分区的一个分区表。
.想要禁用特定分区上的索引来查看表扩展的优点。
操作步骤如下:
1.以sh用户登录数据库
[oracle@jytest1~]$sqlplussh/*****@jypdb SQL*Plus:Release12.2.0.1.0ProductiononWedOct3118:09:542018 Copyright(c)1982,2016,Oracle.Allrightsreserved. LastSuccessfullogintime:WedOct24201817:00:11+08:00 Connectedto: OracleDatabase12cEnterpriseEditionRelease12.2.0.1.0-64bitProduction SQL>
2.执行以下查询
SQL>select*fromsaleswheretime_id>=to_date('2000-01-0100:00:00','syyyy-mm-ddhh24:mi:ss')andprod_id=38; ........... 38247024-DEC-012999131.47 381344024-DEC-012999131.47 3849028-DEC-012999131.47 38840628-DEC-012999131.47 38146631-DEC-013351131.47 38434031-DEC-013351131.47 381065831-DEC-013351131.47 381139031-DEC-013351131.47 382322631-DEC-013351131.47 4224rowsselected.
3.查询执行计划
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-ddhh24:mi:ss')andprod_id=38 Planhashvalue:2342444420 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- |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.03|334| |1|PARTITIONRANGEITERATOR||1|5078|143K|224(0)|00:00:01|13|28|4224|00:00:00.03|334| |2|TABLEACCESSBYLOCALINDEXROWIDBATCHED|SALES|16|5078|143K|224(0)|00:00:01|13|28|4224|00:00:00.02|334| |3|BITMAPCONVERSIONTOROWIDS||8|||||||4224|00:00:00.01|24| |*4|BITMAPINDEXSINGLEVALUE|SALES_PROD_BIX|8|||||13|28|8|00:00:00.01|24| ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- QueryBlockName/ObjectAlias(identifiedbyoperationid): ------------------------------------------------------------- 1-SEL$1 2-SEL$1/SALES@SEL$1 OutlineData ------------- /*+ 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 */ PredicateInformation(identifiedbyoperationid): --------------------------------------------------- 4-access("PROD_ID"=38) ColumnProjectionInformation(identifiedbyoperationid): ----------------------------------------------------------- 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[BMVAR,10],STRDEF[BMVAR,10],STRDEF[BMVAR,7920],"PROD_ID"[NUMBER,22] Note ----- -automaticDOP:ComputedDegreeofParallelismis1becauseofparallelthreshold 58rowsselected.
在执行计划中的Pstart与Pstop列,显示了优化器判断只需要访问表的13到28分区。在优化器已经判断了被访问的分区之后,它将考虑所有这些分区上可以使用的索引。在上面的执行计划中,优化器选择使用sales_prod_bix位图索引
4.禁用sales表中sales_1995分区上的索引;
SQL>alterindexsales_prod_bixmodifypartitionsales_1995unusable; Indexaltered.
5.再次执行之前的查询语句,然后显示执行计划,可以看到执行计划变成了由两个子查询组成的unionall语句,第一个子查询还是对13-28分区使用索引,第二个子查询步骤对应的Pstart与Pstop为invalid,id=11的过滤条件为”PROD_ID”=38,id=9的过滤条件为”SALES”.”TIME_ID”=TO_DATE(‘2000-01-0100:00:00',‘syyyy-mm-ddhh24: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-ddhh24:mi:ss')andprod_id=38 Planhashvalue: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$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 OutlineData ------------- /*+ 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 */ PredicateInformation(identifiedbyoperationid): --------------------------------------------------- 6-access("PROD_ID"=38) 7-filter(NULLISNOTNULL) 9-filter(("SALES"."TIME_ID"=TO_DATE('2000-01-0100:00:00','syyyy-mm-dd hh24: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:ComputedDegreeofParallelismis1becauseofparallelthreshold 93rowsselected.
6.禁用分区28上的索引(sales_q4_2003),它是查询需要访问的一个分区:
SQL>alterindexsales_prod_bixmodifypartitionsales_q4_2003unusable; Indexaltered. SQL>alterindexsales_time_bixmodifypartitionsales_q4_2003unusable; Indexaltered.
通过禁用查询需要访问分区上的索引,查询将不能再使用这些索引。
7.再次执行查询语句,其执行计划如下,执行计划变成了由三个子查询组成的unionall语句,相比之前查询多的第三个子查询对表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-ddhh24:mi:ss')andprod_id=38 Planhashvalue: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$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 OutlineData ------------- /*+ 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 */ PredicateInformation(identifiedbyoperationid): --------------------------------------------------- 6-access("PROD_ID"=38) 7-filter(NULLISNOTNULL) 9-filter(("SALES"."TIME_ID"=TO_DATE('2000-01-0100:00:00','syyyy-mm-ddhh24: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:ComputedDegreeofParallelismis1becauseofparallelthreshold 103rowsselected.
总结
以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,如果有疑问大家可以留言交流,谢谢大家对毛票票的支持。