分区消除行为:如果查询首先访问索引,是否能消除分区完全取决于查询中的谓词。
SQL> create table test
2 (a int,
3 b int,
4 data char(20)
5 )
6 partition by range(a)
7 (partition part_1 values less than(2),
8 partition part_2 values less than(3)
9 )
10 /
表已创建。
SQL> create index local_pre on test(a,b) local;
索引已创建。
SQL> create index local_nopre on test(b) local;
索引已创建。
SQL> insert into test
2 select mod(rownum -1 ,2) +1, rownum, 'x'
3 from all_objects;
已创建11201行。
进行对象统计信息收集:
SQL> edit
已写入 file afiedt.buf
1 BEGIN
2 dbms_stats.gather_table_stats(
3 ownname => user,
4 tabname => 'TEST',
5 estimate_percent => 100,
6 method_opt => 'for all columns size skewonly',
7 cascade => TRUE
8 );
9* END;
SQL> /
PL/SQL 过程已成功完成。
SQL> set autot traceonly explain
SQL> select * from test where a=1 and b=1;
执行计划
----------------------------------------------------------
Plan hash value: 3724286461
--------------------------------------------------------------------------------
--------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%
CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------
--------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 2
(0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 28 | 2
(0)| 00:00:01 | 1 | 1 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| TEST | 1 | 28 | 2
(0)| 00:00:01 | 1 | 1 |
|* 3 | INDEX RANGE SCAN | LOCAL_PRE | 1 | | 1
(0)| 00:00:01 | 1 | 1 |
--------------------------------------------------------------------------------
--------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"=1 AND "B"=1)
通过查看执行计划,此查询通过索引local_pre做了分区消除。
SQL> select * from test where b =1;
执行计划
----------------------------------------------------------
Plan hash value: 3332543978
--------------------------------------------------------------------------------
----------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------
----------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 4
(0)| 00:00:01 | | |
| 1 | PARTITION RANGE ALL | | 1 | 28 | 4
(0)| 00:00:01 | 1 | 2 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| TEST | 1 | 28 | 4
(0)| 00:00:01 | 1 | 2 |
|* 3 | INDEX RANGE SCAN | LOCAL_NOPRE | 1 | | 3
(0)| 00:00:01 | 1 | 2 |
--------------------------------------------------------------------------------
----------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("B"=1)
通过local_nopre,oracle不能排除分区,两个分区都必须考虑,此查询未做分区消除。使用非前缀索引,要做到分区消除,必须使用一个允许分区消除的查询。
SQL> drop index local_pre;
索引已删除。
SQL> select * from test where a= 1 and b=1;
执行计划
----------------------------------------------------------
Plan hash value: 2334989887
--------------------------------------------------------------------------------
----------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------
----------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 2
(0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 28 | 2
(0)| 00:00:01 | 1 | 1 |
|* 2 | TABLE ACCESS BY LOCAL INDEX ROWID| TEST | 1 | 28 | 2
(0)| 00:00:01 | 1 | 1 |
|* 3 | INDEX RANGE SCAN | LOCAL_NOPRE | 1 | | 1
(0)| 00:00:01 | 1 | 1 |
--------------------------------------------------------------------------------
----------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("A"=1)
3 - access("B"=1)
通过查看执行计划,Oracle利用了分区消除。因为使用了条件a = 1,Oracle拥有了足够的信息,从而使用非前缀索引也能做到分区消除。
根据这个例子中创建的分区表,如果经常使用如下查询:
select * from test where a = 1 and b = 1;
select * from test where b = 1;
可以测试创建(b,a)索引的效果,此索引以上两个查询都可以使用。如果创建局部前缀索引(a ,b),很可能只对第一个查询有用,对第二个查询不能作分区消除,但能否用得上索引,需要测试,看执行计划是否走index skip scan。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23577591/viewspace-704078/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/23577591/viewspace-704078/