oracle 建分区索引_Oracle的分区表和Local索引创建与维护

Oracle的分区表和Local索引创建与维护

SQL> connect eygle/eygle

Connected.

SQL> CREATE TABLE dbobjs

2  (OBJECT_ID        NUMBER NOT NULL,

3  OBJECT_NAME      varchar2(128),

4  CREATED          DATE  NOT NULL

5  )

6  PARTITION BY RANGE (CREATED)

7  (PARTITION dbobjs_06 VALUES LESS THAN (TO_DATE('01/01/2007', 'DD/MM/YYYY')),

8  PARTITION dbobjs_07 VALUES LESS THAN (TO_DATE('01/01/2008', 'DD/MM/YYYY')));

Table created.

SQL> COL segment_name for a20

SQL> COL PARTITION_NAME for a20

SQL> SELECT segment_name, partition_name, tablespace_name

2    FROM dba_segments

3  WHERE segment_name = 'DBOBJS';

SEGMENT_NAME        PARTITION_NAME      TABLESPACE_NAME

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

DBOBJS              DBOBJS_06            EYGLE

DBOBJS              DBOBJS_07            EYGLE

创建一个Local索引，注意这里可以将不同分区的索引指定创建到不同的表空间：

SQL> CREATE INDEX dbobjs_idx ON dbobjs (created) LOCAL

2  (PARTITION dbobjs_06 TABLESPACE users,

3    PARTITION dbobjs_07 TABLESPACE users

4    );

Index created.

这个子句可以进一步调整为类似：

CREATE INDEX dbobjs_idx ON dbobjs (created) LOCAL

(PARTITION dbobjs_06 TABLESPACE users,

PARTITION dbobjs_07 TABLESPACE users

) TABLESPACE users;

通过统一的tablespace子句为索引指定表空间。

SQL> COL segment_name for a20

SQL> COL PARTITION_NAME f