索引组织表OVERFLOW段的等同分区

和LOB段类似，索引组织表的OVERFLOW段也是采用等同分区的方式存储。

 

 

建立分区表的时候不明确指定OVERFLOW段存储信息：

SQL> SELECT * FROM TAB;

未选定行

SQL> SELECT DEFAULT_TABLESPACE
  2  FROM USER_USERS;

DEFAULT_TABLESPACE
------------------------------
YANGTK

SQL> CREATE TABLE T_IND_PART
  2  (ID NUMBER PRIMARY KEY,
  3  NAME VARCHAR2(30),
  4  CREATE_DATE DATE)
  5  ORGANIZATION INDEX
  6  INCLUDING NAME
  7  OVERFLOW
  8  PARTITION BY RANGE (ID)
  9  (PARTITION P1 VALUES LESS THAN (100) TABLESPACE TEST,
 10  PARTITION P2 VALUES LESS THAN (200),
 11  PARTITION P3 VALUES LESS THAN (300));

表已创建。

SQL> SELECT SEGMENT_NAME, SEGMENT_TYPE, PARTITION_NAME, TABLESPACE_NAME
  2  FROM USER_SEGMENTS;

SEGMENT_NAME                   SEGMENT_TYPE       PARTITION_NAME      TABLESPACE_NAME
------------------------------ ------------------ -------------------- ------------------
SYS_IOT_OVER_86415             TABLE PARTITION    P2                   YANGTK
SYS_IOT_OVER_86415             TABLE PARTITION    P1                   YANGTK
SYS_IOT_OVER_86415             TABLE PARTITION    P3                   YANGTK
SYS_IOT_TOP_86415              INDEX PARTITION    P1                   TEST
SYS_IOT_TOP_86415              INDEX PARTITION    P2                   YANGTK
SYS_IOT_TOP_86415              INDEX PARTITION    P3                   YANGTK

已选择6行。

如果需要对OVERFLOW段进行单独的设置：

SQL> CREATE TABLE T_IND_PART
  2  (ID NUMBER PRIMARY KEY,
  3  NAME VARCHAR2(30),
  4  CREATE_DATE DATE)
  5  ORGANIZATION INDEX
  6  INCLUDING NAME
  7  OVERFLOW
  8  PARTITION BY RANGE (ID)
  9  (PARTITION P1 VALUES LESS THAN (100) TABLESPACE TEST
 10  OVERFLOW TABLESPACE USERS,
 11  PARTITION P2 VALUES LESS THAN (200)
 12  OVERFLOW TABLESPACE TEST,
 13  PARTITION P3 VALUES LESS THAN (300));

表已创建。

SQL> SELECT SEGMENT_NAME, SEGMENT_TYPE, PARTITION_NAME, TABLESPACE_NAME
  2  FROM USER_SEGMENTS;

SEGMENT_NAME                   SEGMENT_TYPE       PARTITION_NAME       TABLESPACE_NAME
------------------------------ ------------------ -------------------- --------------------
SYS_IOT_OVER_86505             TABLE PARTITION    P1                   USERS
SYS_IOT_OVER_86505             TABLE PARTITION    P2                   TEST
SYS_IOT_OVER_86505             TABLE PARTITION    P3                   YANGTK
SYS_IOT_TOP_86505              INDEX PARTITION    P1                   TEST
SYS_IOT_TOP_86505              INDEX PARTITION    P2                   YANGTK
SYS_IOT_TOP_86505              INDEX PARTITION    P3                   YANGTK

已选择6行。

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/4227/viewspace-627884/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/4227/viewspace-627884/