在表扩展中，优化器生成一个计划，该计划在分区表的只读部分上使用索引，而不在表的活动部分上使用索引。
5.10.1表扩展的目的
基于索引的计划可以提高性能，但是索引维护会产生开销。在许多数据库中，DML仅影响一小部分数据。
表扩展对高更新表使用基于索引的计划。您可以仅在只读数据上创建索引，从而消除了活动数据上的索引开销。这样，表扩展在提高性能的同时避免了索引维护。
5.10.2表扩展如何工作
表分区使表扩展成为可能。
如果分区表上存在本地索引，则优化器可以将索引标记为不适用于特定分区。实际上，某些分区未建立索引。
在表扩展中，优化器将查询转换为UNION ALL语句，其中一些子查询访问索引分区，而其他子查询访问未索引分区。无论查询中访问的所有分区是否存在，优化器都可以选择可用于该分区的最有效的访问方法。
优化器并不总是选择表扩展：
- 表扩展是基于成本的。
虽然数据库在的所有分支中仅访问扩展表的每个分区一次
UNION ALL，但在每个分支中都访问数据库所联接到的任何表。 - 语义问题可能会使扩展无效。
例如，出现在外部联接右侧的表对于表扩展无效。
您可以使用提示EXPAND_TABLE提示控制表扩展。提示会覆盖基于成本的决策，但不会覆盖语义检查。
也可以看看：
- “ 通过提示影响优化器 ”
- Oracle Database SQL语言参考，以了解有关SQL提示的更多信息
5.10.3表扩展：方案
优化器根据查询中出现的谓词，跟踪必须从每个表访问哪些分区。分区修剪使优化器可以使用表扩展来生成更多最佳计划。
假设条件
此方案假定以下条件：
- 您想对
sh.sales表进行星型查询，该表在time_id列上进行了分区。 - 您想要禁用特定分区上的索引以查看表扩展的好处。
要使用表扩展：
- 以
sh用户身份登录数据库。 - 运行以下查询：
SELECT * FROM sales WHERE time_id >= TO_DATE('2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS') AND prod_id = 38; - 通过查询来解释该计划
DBMS_XPLAN：SET LINESIZE 150 SET PAGESIZE 0 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'BASIC,PARTITION'));如以下计划中的
Pstart和Pstop列所示，优化器从过滤器确定必须访问表中28个分区中的16个：Plan hash value: 3087065703 -------------------------------------------------------------------------- |Id| Operation | Name |Pstart|Pstop| -------------------------------------------------------------------------- | 0| SELECT STATEMENT | | | | | 1| PARTITION RANGE ITERATOR | | 13 | 28 | | 2| TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| SALES | 13 | 28 | | 3| BITMAP CONVERSION TO ROWIDS | | | | |*4| BITMAP INDEX SINGLE VALUE |SALES_PROD_BIX| 13 | 28 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("PROD_ID"=38)优化器确定要访问的分区后，它将考虑所有这些分区上可用的任何索引。在前面的计划中，优化器选择使用
sales_prod_bix位图索引。 - 禁用表sales
SALES_1995分区上的索引：i前面的DDL禁用分区1上的索引，该分区包含1996年之前的所有销售额。
注意：
您可以通过查询
USER_IND_PARTITIONS视图来获取分区信息。 - 再次执行销售查询，然后查询
DBMS_XPLAN以获取计划。输出显示该计划没有更改：
Plan hash value: 3087065703 --------------------------------------------------------------------------- |Id| Operation | Name |Pstart|Pstop --------------------------------------------------------------------------- | 0| SELECT STATEMENT | | | | | 1| PARTITION RANGE ITERATOR | | 13 | 28 | | 2| TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| SALES | 13 | 28 | | 3| BITMAP CONVERSION TO ROWIDS | | | | |*4| BITMAP INDEX SINGLE VALUE | SALES_PROD_BIX| 13 | 28 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("PROD_ID"=38)该计划是相同的，因为禁用的索引分区与查询无关。如果查询访问的所有分区都已建立索引，则数据库可以使用索引来回答查询。由于查询仅访问分区16到28，因此禁用分区1上的索引不会影响计划。
- 禁用分区28（
SALES_Q4_2003）的索引，分区28 是查询需要访问的分区：ALTER INDEX sales_prod_bix MODIFY PARTITION sales_q4_2003 UNUSABLE; ALTER INDEX sales_time_bix MODIFY PARTITION sales_q4_2003 UNUSABLE;通过禁用查询确实需要访问的分区上的索引，查询将不再使用此索引（无需表扩展）。
- 使用查询计划
DBMS_XPLAN。如以下计划所示，优化器不使用索引：
Plan hash value: 3087065703 --------------------------------------------------------------------------- | Id| Operation | Name |Pstart|Pstop --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | 1 | PARTITION RANGE ITERATOR | | 13 | 28 | |*2 | TABLE ACCESS FULL | SALES | 13 | 28 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("PROD_ID"=38)在前面的示例中，查询访问16个分区。在这些分区中的15个分区上，有一个索引可用，但对于最终分区没有索引可用。由于优化器必须选择一个访问路径或另一个访问路径，因此优化器无法在任何分区上使用索引。
- 通过表扩展，优化器将重写原始查询，如下所示：
SELECT * FROM sales WHERE time_id >= TO_DATE('2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS') AND time_id < TO_DATE('2003-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS') AND prod_id = 38 UNION ALL SELECT * FROM sales WHERE time_id >= TO_DATE('2003-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS') AND time_id < TO_DATE('2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS') AND prod_id = 38;在前面的查询中，第一个查询块
UNION ALL访问已索引的分区，而第二个查询块访问未索引的分区。如果这两个子查询比使用对所有已访问分区的表扫描更为理想，则优化器可以选择使用第一个查询块中的索引。 - 使用查询计划
DBMS_XPLAN。该计划如下所示：
Plan hash value: 2120767686 --------------------------------------------------------------------------- |Id| Operation | Name |Pstart|Pstop| --------------------------------------------------------------------------- | 0| SELECT STATEMENT | | | | | 1| VIEW | VW_TE_2 | | | | 2| UNION-ALL | | | | | 3| PARTITION RANGE ITERATOR | | 13| 27| | 4| TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| SALES | 13| 27| | 5| BITMAP CONVERSION TO ROWIDS | | | | |*6| BITMAP INDEX SINGLE VALUE | SALES_PROD_BIX| 13| 27| | 7| PARTITION RANGE SINGLE | | 28| 28| |*8| TABLE ACCESS FULL | SALES | 28| 28| --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 6 - access("PROD_ID"=38) 8 - filter("PROD_ID"=38)如之前的计划所示，优化器将a
UNION ALL用于两个查询块（步骤2）。优化器选择索引以访问第一个查询块中的分区13到27（步骤6）。因为没有索引可用于分区28，所以优化器在第二个查询块中选择全表扫描（步骤8）。
5.10.4表扩展和星型转换：方案
星型转换支持特定类型的查询，以避免访问大型事实表的大部分。
星型转换需要定义多个索引，而这些索引在主动更新的表中可能会有开销。通过表扩展，您可以仅在非活动分区上定义索引，以便优化程序可以仅在表的索引部分考虑星形转换。
假设条件
此方案假定以下条件：
- 您查询“ 明星转型：方案 ”中使用的相同模式。
- 它的最后一个分区
sales正在积极地进行更新，这通常是按时间划分的表。 - 您希望优化器利用表扩展的优势。
要在星型查询中利用表扩展：
- 禁用最后一个分区上的索引，如下所示：
ALTER INDEX sales_channel_bix MODIFY PARTITION sales_q4_2003 UNUSABLE; ALTER INDEX sales_cust_bix MODIFY PARTITION sales_q4_2003 UNUSABLE; - 执行以下星型查询：
SELECT t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount FROM sales s, times t, customers c, channels ch WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id AND s.channel_id = ch.channel_id AND c.cust_state_province = 'CA' AND ch.channel_desc = 'Internet' AND t.calendar_quarter_desc IN ('1999-01','1999-02') GROUP BY t.calendar_quarter_desc; - 使用来查询游标
DBMS_XPLAN，该游标显示以下计划：--------------------------------------------------------------------------- |Id| Operation | Name | Pstart| Pstop | --------------------------------------------------------------------------- | 0| SELECT STATEMENT | | | | | 1| HASH GROUP BY | | | | | 2| VIEW |VW_TE_14 | | | | 3| UNION-ALL | | | | | 4| HASH JOIN | | | | | 5| TABLE ACCESS FULL |TIMES | | | | 6| VIEW |VW_ST_1319B6D8 | | | | 7| NESTED LOOPS | | | | | 8| PARTITION RANGE SUBQUERY | |KEY(SQ)|KEY(SQ)| | 9| BITMAP CONVERSION TO ROWIDS| | | | |10| BITMAP AND | | | | |11| BITMAP MERGE | | | | |12| BITMAP KEY ITERATION | | | | |13| BUFFER SORT | | | | |14| TABLE ACCESS FULL |CHANNELS | | | |15| BITMAP INDEX RANGE SCAN|SALES_CHANNEL_BIX|KEY(SQ)|KEY(SQ)| |16| BITMAP MERGE | | | | |17| BITMAP KEY ITERATION | | | | |18| BUFFER SORT | | | | |19| TABLE ACCESS FULL |TIMES | | | |20| BITMAP INDEX RANGE SCAN|SALES_TIME_BIX |KEY(SQ)|KEY(SQ)| |21| BITMAP MERGE | | | | |22| BITMAP KEY ITERATION | | | | |23| BUFFER SORT | | | | |24| TABLE ACCESS FULL |CUSTOMERS | | | |25| BITMAP INDEX RANGE SCAN|SALES_CUST_BIX |KEY(SQ)|KEY(SQ)| |26| TABLE ACCESS BY USER ROWID |SALES | ROWID | ROWID | |27| NESTED LOOPS | | | | |28| NESTED LOOPS | | | | |29| NESTED LOOPS | | | | |30| NESTED LOOPS | | | | |31| PARTITION RANGE SINGLE | | 28 | 28 | |32| TABLE ACCESS FULL |SALES | 28 | 28 | |33| TABLE ACCESS BY INDEX ROWID|CHANNELS | | | |34| INDEX UNIQUE SCAN |CHANNELS_PK | | | |35| TABLE ACCESS BY INDEX ROWID |CUSTOMERS | | | |36| INDEX UNIQUE SCAN |CUSTOMERS_PK | | | |37| INDEX UNIQUE SCAN |TIMES_PK | | | |38| TABLE ACCESS BY INDEX ROWID |TIMES | | | ---------------------------------------------------------------------------上一个计划使用表扩展。
UNION ALL访问除最后一个分区以外的每个分区的分支使用星型转换。由于禁用了分区28上的索引，因此数据库使用全表扫描访问最终分区。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
