分区表及分区索引创建示例

转载自：http://space.itpub.net/35489/viewspace-617689

CREATE TABLE DFMS.AAAAA  
(
  SERIAL_NUMBER      VARCHAR2(25 BYTE)          NOT NULL,
  REPAIR_COUNT       INTEGER                    DEFAULT 0                     NOT NULL,
  REWORK_COUNT       INTEGER                    DEFAULT 0                     NOT NULL,
  IN_STATION_COUNT   NUMBER(3),
  IN_STATION_TIME       DATE,
  SHIP_DATE          DATE,
  PACK_SEQ_NO        INTEGER,
  ORIGINAL_WO_NO     VARCHAR2(25 BYTE)  
)
TABLESPACE BASE_DATA   
PCTUSED    40
PCTFREE    10
INITRANS   1
MAXTRANS   255
LOGGING  
PARTITION BY RANGE (in_station_time)
(  
  PARTITION P200907 VALUES LESS THAN (TO_DATE('2009-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING
    NOCOMPRESS
    TABLESPACE BASE_DATA,
  PARTITION P201001 VALUES LESS THAN (TO_DATE('2010-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING
    NOCOMPRESS
    TABLESPACE BASE_DATA,  
  PARTITION P201007 VALUES LESS THAN (TO_DATE('2010-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING
    NOCOMPRESS
    TABLESPACE BASE_DATA,  
  PARTITION PMAX VALUES LESS THAN (MAXVALUE)
    LOGGING
    NOCOMPRESS
    TABLESPACE BASE_DATA  
) 
NOCOMPRESS 
NOCACHE
MONITORING
NOPARALLEL
ENABLE ROW MOVEMENT;

 

CREATE INDEX  DFMS.AAAAA_TIME_IDX ON  DFMS.AAAAA(in_station_time)  
LOCAL 
TABLESPACE  BASE_IDX ;  

 

创建完成之后在Toad中查看脚本如下,   可以看出， local 索引的分区完全继承表的分区的属性，包括分区类型，分区的范围值即不需指定也 不能更改， 这就是说： local 索引的分区维护完全依赖于其索引所在表。 不过，分区名称，以及分区所在表空间等信息是可以自定义的 。 我们也可以这样写，

CREATE INDEX  DFMS.AAAAA_TIME_IDX ON  DFMS.AAAAA(in_station_time)   local (
partition   i_range_p1   tablespace  tbspart01,
partition   i_range_p2   tablespace  tbspart01,
partition   i_range_p3   tablespace  tbspart02,
partition   i_range_pmax   tablespace  tbspart02
)    tablespace   tbspart00 ; 

 

 

Toad中脚本如下： 

CREATE TABLE DFMS.AAAAA
(
  SERIAL_NUMBER     VARCHAR2(25 BYTE)           NOT NULL,
  REPAIR_COUNT      INTEGER                     DEFAULT 0                     NOT NULL,
  REWORK_COUNT      INTEGER                     DEFAULT 0                     NOT NULL,
  IN_STATION_COUNT  NUMBER(3),
  IN_STATION_TIME   DATE,
  SHIP_DATE         DATE,
  PACK_SEQ_NO       INTEGER,
  ORIGINAL_WO_NO    VARCHAR2(25 BYTE)
)
TABLESPACE BASE_DATA
PCTUSED    40
PCTFREE    10
INITRANS   1
MAXTRANS   255
LOGGING
PARTITION BY RANGE (IN_STATION_TIME)
(  
  PARTITION P200907 VALUES LESS THAN (TO_DATE(' 2009-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING
    NOCOMPRESS
    TABLESPACE BASE_DATA
    PCTUSED    40
    PCTFREE    10
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
                INITIAL          10M
                NEXT             10M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                FREELISTS        1
                FREELIST GROUPS  1
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION P201001 VALUES LESS THAN (TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING
    NOCOMPRESS
    TABLESPACE BASE_DATA
    PCTUSED    40
    PCTFREE    10
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
                INITIAL          10M
                NEXT             10M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                FREELISTS        1
                FREELIST GROUPS  1
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION P201007 VALUES LESS THAN (TO_DATE(' 2010-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING
    NOCOMPRESS
    TABLESPACE BASE_DATA
    PCTUSED    40
    PCTFREE    10
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
                INITIAL          10M
                NEXT             10M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                FREELISTS        1
                FREELIST GROUPS  1
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION PMAX VALUES LESS THAN (MAXVALUE)
    LOGGING
    NOCOMPRESS
    TABLESPACE BASE_DATA
    PCTUSED    40
    PCTFREE    10
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
                INITIAL          10M
                NEXT             10M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                FREELISTS        1
                FREELIST GROUPS  1
                BUFFER_POOL      DEFAULT
               )
)
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING
ENABLE ROW MOVEMENT;

CREATE INDEX DFMS.AAAAA_TIME_IDX ON DFMS.AAAAA
(IN_STATION_TIME)
  TABLESPACE BASE_IDX
  INITRANS   2
  MAXTRANS   255
LOCAL (  
  PARTITION P200907
    LOGGING
    NOCOMPRESS
    TABLESPACE BASE_IDX
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          5M
                NEXT             5M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                FREELISTS        1
                FREELIST GROUPS  1
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION P201001
    LOGGING
    NOCOMPRESS
    TABLESPACE BASE_IDX
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          5M
                NEXT             5M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                FREELISTS        1
                FREELIST GROUPS  1
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION P201007
    LOGGING
    NOCOMPRESS
    TABLESPACE BASE_IDX
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          5M
                NEXT             5M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                FREELISTS        1
                FREELIST GROUPS  1
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION PMAX
    LOGGING
    NOCOMPRESS
    TABLESPACE BASE_IDX
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          5M
                NEXT             5M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                FREELISTS        1
                FREELIST GROUPS  1
                BUFFER_POOL      DEFAULT
               )
)
NOPARALLEL;

分区表上的索引


表可以按range，hash，list分区，表分区后，其上的索引和普通表上的索引有所不同，oracle对于分区表上的索引分为2类，即局部索引和全局索引，下面分别对这2种索引的特点和局限性做个总结。



局部索引local index

1.        局部索引一定是分区索引，分区键等同于表的分区键，分区数等同于表的分区说，一句话，局部索引的分区机制和表的分区机制一样。

2.        如果局部索引的索引列以分区键开头，则称为前缀局部索引。

3.        如果局部索引的列不是以分区键开头，或者不包含分区键列，则称为非前缀索引。

4.        前缀和非前缀索引都可以支持索引分区消除，前提是查询的条件中包含索引分区键。

5.        局部索引只支持分区内的唯一性，无法支持表上的唯一性，因此如果要用局部索引去给表做唯一性约束，则约束中必须要包括分区键列。

6.        局部分区索引是对单个分区的，每个分区索引只指向一个表分区，全局索引则不然，一个分区索引能指向n个表分区，同时，一个表分区，也可能指向n个索引分区，对分区表中的某个分区做truncate或者move，shrink等，可能会影响到n个全局索引分区，正因为这点，局部分区索引具有更高的可用性。

7.        位图索引只能为局部分区索引。

8.        局部索引多应用于数据仓库环境中。



全局索引global index

1.        全局索引的分区键和分区数和表的分区键和分区数可能都不相同，表和全局索引的分区机制不一样。

2.        全局索引可以分区，也可以是不分区索引，全局索引必须是前缀索引，即全局索引的索引列必须是以索引分区键作为其前几列。

3.        全局分区索引的索引条目可能指向若干个分区，因此，对于全局分区索引，即使只

动，截断一个分区中的数据，都需要rebulid若干个分区甚

至是整个索引。

4.        全局索引多应用于oltp系统中。

5.        全局分区索引只按范围或者散列hash分区，hash分区是10g以后才支持。

6.        oracle9i以后对分区表做move或者truncate的时可以用update global indexes语句来同步更新全局分区索引，用消耗一定资源来换取高度的可用性。

7.        表用a列作分区，索引用b做局部分区索引，若where条件中用b来查询，那么oracle会扫描所有的表和索引的分区，成本会比分区更高，此时可以考虑用b做全局分区索引。



分区索引字典



DBA_PART_INDEXES 分区索引的概要统计信息，可以得知每个表上有哪些分区索引，分区索引的类新(local/global,)

Dba_ind_partitions每个分区索引的分区级统计信息

Dba_indexesminusdba_part_indexes，可以得到每个表上有哪些非分区索引



索引重建



Alter index idx_name rebuild partition index_partition_name [online nologging]

需要对每个分区索引做rebuild，重建的时候可以选择online(不会锁定表),或者nologging建立索引的时候不生成日志，加快速度。

Alter index rebuild idx_name [online nologging]

对非分区索引，只能整个index重建

引用 删除 tolywang   /   2009-10-29 15:40:13

关于USING INDEX  

两种情况：
1.对于创建约束时隐式创建的索引，在做删除操作的时候:   9i~10g都会连带删除该索引
2.对于先创建索引，再创建约束（使用到此索引）这种情况:
9i版本：需要区分索引是否唯一：
如果索引是唯一的，则删除约束的时候，会连带删除索引；如果非唯一的，则不会删除索引。
10g版本：无论索引是否唯一，都只是删除约束，索引不会删除。
可以参考metalink文档：309821.1

引用 删除 tolywang   /   2009-10-29 15:26:03

假设运行 

CREATE UNIQUE INDEX  DFMS.AAAAA_SN_IDX  
ON  DFMS.AAAAA(SERIAL_NUMBER)  
LOCAL
TABLESPACE  BASE_IDX ;   

或者  

创建针对 SERIAL_NUMBER 的 unique index 后使用下面语句建立PK  (当然也可以直接创建PK) .  
alter table  DFMS.AAAAA    
add constraint   PK_AAAAA_SN    
primary key (SERIAL_NUMBER)  
USING INDEX  
LOCAL ; 


都会报错， ORA-14039: 分区列必须构成 UNIQUE 索引的关键字列子集 . 



这时候由于 oracle 不支持在分区表上创建PK主键时主键列不包含分区列(也就是上面的in_station_time )，创建其他约束（unique）也不可以。  


为解决这个问题，两种方法：
1、将分区列in_station_time 也加入PK 列中，采用

alter table  DFMS.AAAAA    
add constraint   PK_AAAAA_SN    
primary key (SERIAL_NUMBER, in_station_time)   
USING INDEX  
LOCAL ; 




2、创建index好了

create index    indx__AAAAA_SN    
on  DFMS.AAAAA (SERIAL_NUMBER)   local;