ORACLE全局索引、分区索引

create table test7 as select * from dba_objects;
create table TEST
(
  OWNER          VARCHAR2(30),
  OBJECT_NAME    VARCHAR2(128),
  SUBOBJECT_NAME VARCHAR2(30),
  OBJECT_ID      NUMBER,
  DATA_OBJECT_ID NUMBER,
  OBJECT_TYPE    VARCHAR2(19),
  CREATED        DATE,
  LAST_DDL_TIME  DATE,
  TIMESTAMP      VARCHAR2(19),
  STATUS         VARCHAR2(7),
  TEMPORARY      VARCHAR2(1),
  GENERATED      VARCHAR2(1),
  SECONDARY      VARCHAR2(1)
)
partition by range(OBJECT_ID)
(
  partition p1 values less than(10000),
  partition p2 values less than(20000),
  partition p3 values less than(30000),
  partition p4 values less than(40000),
  partition p5 values less than(maxvalue)
)
insert into test  select * from test7;
以相同的形式建立test1,test2

一、建立全局非分区索引

create index i_object_id on test(object_id);

SQL> select segment_name,partition_name,segment_type
      from user_segments s
        where s.segment_name = 'I_OBJECT_ID';

SEGMENT_NAME         PARTITION_NAME  SEGMENT_TYPE
-------------------- --------------- --------------------
I_OBJECT_ID                          INDEX

SQL> select * from test where object_id=100;
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart|stop |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |             |     1 |    84 |     2   (0)| 00:00:01 |    |  |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| TEST        |     1 |    84 |     2   (0)| 00:00:01 |     1 |  1 |
|*  2 |   INDEX RANGE SCAN                 | I_OBJECT_ID |     6 |       |     1   (0)| 00:00:01 |    |  |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"=100)
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
       1206  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
二、建立本地分区索引
create index l_object_id on test1(object_id) local;

SQL> select segment_name,partition_name,segment_type
      from user_segments s
     where s.segment_name = 'L_OBJECT_ID';

SEGMENT_NAME         PARTITION_NAME  SEGMENT_TYPE
-------------------- --------------- --------------------
L_OBJECT_ID          P1              INDEX PARTITION
L_OBJECT_ID          P2              INDEX PARTITION
L_OBJECT_ID          P3              INDEX PARTITION
L_OBJECT_ID          P4              INDEX PARTITION
L_OBJECT_ID          P5              INDEX PARTITION

SQL> select * from test1 where object_id=100;
------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |             |     1 |    84 |     2   (0)| 00:00:01 |    |  |
|   1 |  PARTITION RANGE SINGLE            |             |     1 |    84 |     2   (0)| 00:00:01 |     1 |  1 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| TEST1       |     1 |    84 |     2   (0)| 00:00:01 |     1 |  1 |
|*  3 |    INDEX RANGE SCAN                | L_OBJECT_ID |     1 |       |     1   (0)| 00:00:01 |     1 |  1 |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("OBJECT_ID"=100)
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
       1206  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

三、建立全局分区索引
CREATE index g_object_id ON test2(object_id)
  GLOBAL PARTITION BY RANGE(object_id)
(
  partition p1 values less than(10000),
  partition p2 values less than(20000),
  partition p3 values less than(30000),
  partition p4 values less than(40000),
  partition p5 values less than(maxvalue)
);

SQL> select segment_name,partition_name,segment_type
      from user_segments s
        where s.segment_name = 'G_OBJECT_ID';

SEGMENT_NAME         PARTITION_NAME  SEGMENT_TYPE
-------------------- --------------- --------------------
G_OBJECT_ID          P1              INDEX PARTITION
G_OBJECT_ID          P2              INDEX PARTITION
G_OBJECT_ID          P3              INDEX PARTITION
G_OBJECT_ID          P4              INDEX PARTITION
G_OBJECT_ID          P5              INDEX PARTITION

SQL> select * from test2 where object_id=100;
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart|Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |             |     1 |    84 |     2   (0)| 00:00:01 |    ||
|   1 |  PARTITION RANGE SINGLE             |             |     1 |    84 |     2   (0)| 00:00:01 |  1 |1 |
|   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| TEST2       |     1 |    84 |     2   (0)| 00:00:01 |  1 |1 |
|*  3 |    INDEX RANGE SCAN                 | G_OBJECT_ID |     1 |       |     1   (0)| 00:00:01 |  1 |1 |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("OBJECT_ID"=100)
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
       1206  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

--分区索引的概要统计信息，可以得知每个表上有哪些分区索引，分区索引的类型(local/global)
select * from  DBA_PART_INDEXES s where s.owner='TEST';
--每个分区索引的分区级统计信息
select * from  Dba_ind_partitions s where s.index_owner='TEST'; 
--可以得到每个表上有哪些非分区索引
select s.table_name,s.index_name from  Dba_indexes s where s.owner='TEST' minus
select s.table_name,s.index_name from  dba_part_indexes s where s.owner='TEST'; 

图片来自于：http://hi.baidu.com/jsshm/item/cbfed8491d3863ee1e19bc3e