oracle非前缀索引,局部前缀索引和局部非前缀索引

继上篇继续讨论：如果是分区表的局部前缀索引和局部非前缀索引会是什么样的情况呢？

SQL> drop table t;

Table dropped.

SQL> create table t(x int,y int)

2 partition by range(x)

3 (partition p1 values less than(500),

4 partition p2 values less than(1000));

Table created.

--在表t上建(x,y)分区键前缀分区索引，(y)分区键非前缀分区索引

SQL> create index idx_t_1 on t(x,y) local;

Index created.

SQL> create index idx_t_2 on t(y) local;

Index created.

SQL> insert into t select rownum rn,rownum+100 pad from dual connect by level <1000;

999 rows created.

SQL> commit;

Commit complete.

SQL> begin

2 dbms_stats.gather_table_stats(user,'T',cascade=>true);

3 end;

4 /

PL/SQL procedure successfully completed.

SQL> explain plan for select * from t where x=1 and y=1;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------

Plan hash value: 4057250928

--------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |

--------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 7 | 1 (0)| 00:00:01 | | |

| 1 |PARTITION RANGE SINGLE| | 1 | 7 | 1 (0)| 00:00:01 | 1 | 1 |

|* 2 | INDEX RANGE SCAN |IDX_T_1| 1 | 7 | 1 (0)| 00:00:01 | 1 | 1 |

--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------

2 - access("X"=1 AND "Y"=1)

14 rows selected.

SQL> delete plan_table;

6 rows deleted.

SQL> set linesize 140

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------------

Plan hash value: 422203977

----------------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |

----------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 7 | 2 (0)| 00:00:01 | | |

| 1 |PARTITION RANGE ALL| | 1 | 7 | 2 (0)| 00:00:01 | 1 | 2 |

| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| T | 1 | 7 | 2 (0)| 00:00:01 | 1 | 2 |

|* 3 | INDEX RANGE SCAN |IDX_T_2| 1 | | 1 (0)| 00:00:01 | 1 | 2 |

-----------------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - access("Y"=1)

15 rows selected.

右上面可以看到，谓词中包含分区键时会自动分区消除，而谓词中不包含分区键的却需要扫描所有的分区(没有用分区消除)

TOM在9i/10g数据库体系结构中说，建(y,x)的非分区键前缀索引，可以让上面两种情况都可以使用分区消除，但是我测试谓词只有y时是这样的：

--下面将idx_t_2索引删除，建一个(y,x)的非分区键前缀的分区索引

SQL> drop index idx_t_2;

Index dropped.

SQL> create index idx_t_3 on t(y,x) local;

Index created.

SQL> analyze table t compute statistics;

Table analyzed.

SQL> delete from plan_table;

7 rows deleted.

SQL> explain plan for select * from t where y=1;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

-----------------------------------------------------------------------------------------------

Plan hash value: 497340753

-----------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |

-----------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 7 | 3 (0)| 00:00:01 | | |

| 1 |PARTITION RANGE ALL| | 1 | 7 | 3 (0)| 00:00:01 | 1 | 2 |

|* 2 | INDEX RANGE SCAN |IDX_T_3| 1 | 7 | 3 (0)| 00:00:01 | 1 | 2 |

-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------

---------------------------------------------------

2 - access("Y"=1)

14 rows selected.

疑惑，tom的测试是在什么环境下呢？

SQL> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

TNS for Linux: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production