Oracle分区索引 - 数据库编程

I:SS', 'NLS_CALENDAR=GREGORIA BIG_TABLE_2012 TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-M TBS1 M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA --删除索引 SQL> drop index bita_created_date_i; --指定索引分区名表空间名创建索引 SQL> CREATE INDEX bita_created_date_i 2 ON big_table (created_date) 3 LOCAL ( 4 PARTITION idx_2012 TABLESPACE idx1, 5 PARTITION idx_2013 TABLESPACE idx2, 6 PARTITION idx_2014 TABLESPACE idx3) 7 PARALLEL 3; Index created. SQL> select partition_name, high_value, tablespace_name from user_ind_partitions; PARTITION_NAME HIGH_VALUE TABLESPACE_NAME ------------------------------ ---------------------------------------- ------------------------------ IDX_2014 MAXVALUE IDX3 IDX_2013 TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-M IDX2 M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 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';

INDEX_NAME                     INDEX_TYPE                  TABLESPACE_NAME                GLO PAR
------------------------------ --------------------------- ------------------------------ --- ---
BIG_TABLE_PK                   NORMAL                      TBS_TMP                        YES NO

SQL> set autot trace exp;                                                                                          
SQL> select * from big_table where id=1412;                                                                        
                                                                                                                   
Execution Plan                                                                                                     
----------------------------------------------------------                                                         
Plan hash value: 2662411593                                                                                        
                                                                                                                   
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |              |     1 |    62 |     2   (0)| 00:00:01 |       |       |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| BIG_TABLE    |     1 |    62 |     2   (0)| 00:00:01 | ROWID | ROWID |
|*  2 |   INDEX UNIQUE SCAN                | BIG_TABLE_PK |     1 |       |     1   (0)| 00:00:01 |       |       |
-------------------------------------------------------------------------------------------------------------------
--如上，在其执行计划中，Pstart与Pstop都为ROWID
--出现了GLOBAL INDEX ROWID，我们添加主键时并未指定Global，但其执行计划表明执行了全局索引访问
--这个地方有待证实，对于分区

Oracle分区索引(二)