Oracle 12CR2查询转换教程之表扩展详解

前言

在表扩展中，对于读取一个分区表部分数据时优化器会生成使用索引的执行计划。基于索引执行计划可以提高性能，但索引维护会增加开锁。在许多数据库中，dml只影响小部分数据。对于频繁更新的表表扩展使用基于索引的执行计划。你可以在以读取为主的数据上创建一个索引，在以频繁变化的数据上消除索引开销。通过这种方式，表扩展在避免索引维护的同时提高了性能。

下面话不多说了，来一起看看详细的介绍吧

表扩展工作原理

表分区使用表扩展成为可能。如果在一个分区表上创建一个本地索引，那么优化器可能会标记索引对于特定的分区不可使用。实际有些分区没有创建索引。在表扩展中，优化器将查询转换为一个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

sql> 

2.执行以下查询

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.查询执行计划

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.

在执行计划中的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 hh24: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 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.禁用分区28上的索引(sales_q4_2003),它是查询需要访问的一个分区:

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.

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

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

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.

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对的支持。