Oracle 分区索引介绍和实例演示(3)
IDX_2012 TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-M IDX1
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SQL> select * from big_table where rownum<2;
ID CREATED_ LOOKUP_ID DATA
---------- -------- ---------- --------------------------------------------------
1413 20120625 2 This is some data for 1413
--查看local index是否被使用，从下面的执行计划中可知，索引被使用，支持分区消除
SQL> set autot trace exp;
SQL> select * from big_table where created_date=to_date('20120625','yyyymmdd');
Execution Plan
----------------------------------------------------------
Plan hash value: 2556877094
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 41 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 41 | 2 (0)| 00:00:01 | 1 | 1 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| BIG_TABLE | 1 | 41 | 2 (0)| 00:00:01 | 1 | 1 |
|* 3 | INDEX RANGE SCAN | BITA_CREATED_DATE_I | 1 | | 1 (0)| 00:00:01 | 1 | 1 |
--------------------------------------------------------------------------------------------------------------------------
3、全局分区索引演示
--为表添加主键
SQL> ALTER TABLE big_table ADD (
2 CONSTRAINT big_table_pk PRIMARY KEY (id)
3 );
Table altered.
SQL> select index_name,index_type,tablespace_name,global_stats,partitioned
2 from user_indexes where index_name='BIG_TABLE_PK';
