与索引有关的表: 分区索引分2类:有前缀(prefix)的分区索引和无前缀(nonprefix)的分区索引: (1)有前缀的分区索引指包含了分区键,并且将其作为引导列的索引。 create index i_id_global on PDBA(id) global --引导列 2 partition by range(id) --分区键 3 (partition p1 values less than (200), 4 partition p2 values less than (maxvalue) 5 ); 这里的ID 就是分区键,并且分区键id 也是索引的引导列。 (2)无前缀的分区索引的列不是以分区键开头,或者不包含分区键列。 create index ix_custaddr_local_id_p on custaddr(id) local ( partition t_list556 tablespace icd_service, partition p_other tablespace icd_service ) 全局分区索引不支持非前缀的分区索引,如果创建,报错如下: SQL> create index i_time_global on PDBA(id) global --索引引导列 2 partition by range(time) --分区建 3 (partition p1 values less than (TO_DATE(‘2010-12-1’, ‘YYYY-MM-DD’)), 4 partition p2 values less than (maxvalue) 5 ); partition by range(time) * 第 2 行出现错误: ORA-14038: GLOBAL 分区索引必须加上前缀 Local 本地索引 分区表索引注意事项: 索引已创建。 和下面SQL 效果相同,因为local索引就是分区索引: create index ix_custaddr_local_id_p on custaddr(id) local ( partition t_list556 tablespace icd_service, partition p_other tablespace icd_service )SQL> create index ix_custaddr_local_areacode on custaddr(areacode) local; 验证2个索引的类型: SQL> select index_name,table_name,partitioning_type,locality,ALIGNMENT from user_part_indexes where table_name=‘CUSTADDR’;index_name table_name partition locali alignment
ix_custaddr_local_areacode custaddr list local prefixed ix_custaddr_local_id custaddr list local non_prefixed Global索引 注意:Oracle只支持2中类型的全局分区索引: 官网的说明如下: 示例1:全局索引,全局索引对所有分区类型都支持: sql> create index ix_custaddr_ global_id on custaddr(id) global; 示例2:全局分区索引,只支持Range 分区和Hash 分区: (1)创建2个测试分区表: create table pdba (id number, time date) partition by range (time) 2 ( 3 partition p1 values less than (to_date(‘2010-10-1’, ‘yyyy-mm-dd’)), 4 partition p2 values less than (to_date(‘2010-11-1’, ‘yyyy-mm-dd’)), 5 partition p3 values less than (to_date(‘2010-12-1’, ‘yyyy-mm-dd’)), 6 partition p4 values less than (maxvalue) 7 ); 表已创建。 create table Thash 2 ( 3 id number primary key, 4 item_id number(8) not null 5 ) 6 partition by hash(id) 7 ( 8 partition part_01, 9 partition part_02, 10 partition part_03 11 ); 表已创建。 SQL> create index i_id_global on PDBA(id) global 2 partition by range(id) 3 (partition p1 values less than (200), 4 partition p2 values less than (maxvalue) 5 ); –这个是有前缀的分区索引。 SQL> create index i_time_global on PDBA(id) global 2 partition y range(time) 3 (partition p1 values less than (TO_DATE(‘2010-12-1’, ‘YYYY-MM-DD’)), 4 partition p2 values less than (maxvalue) 5 );partition by range(time) * 第 2 行出现错误: ORA-14038: GLOBAL 分区索引必须加上前缀 SQL> create index i_time_global on PDBA(time) global 2 partition by range(time) 3 (partition p1 values less than (TO_DATE(‘2010-12-1’, ‘YYYY-MM-DD’)), 4 partition p2 values less than (maxvalue) 5 ); –有前缀的分区索引 SQL> select index_name,table_name,partitioning_type,locality,ALIGNMENT from user_part_indexes where table_name=‘PDBA’;index_name table_name partition locali alignmenti_id_global pdba range global prefixed i_time_global pdba range global prefixedSQL> CREATE INDEX ix_hash ON PDBA (id,time) GLOBAL 2 PARTITION BY HASH (id) 3 (PARTITION p1, 4 PARTITION p2, 5 PARTITION p3, 6 PARTITION p4); 只要索引的引导列包含分区键,就是有前缀的分区索引。 对于分区索引,不能整体进行重建,只能对单个分区进行重建。语法如下: Alter index idx_name rebuild partition index_partition_name [online nologging] 示例: SQL> select index_name,partition_name from user_ind_partitions where index_name=‘I_TIME_GLOBAL’;INDEX_NAME PARTITION_NAMEI_TIME_GLOBAL P1I_TIME_GLOBAL P2SQL> alter index I_TIME_GLOBAL rebuild partition p1 online nologging; alter index I_TIME_GLOBAL rebuild partition p2 online nologging; 索引已更改。 (2)全局索引 SQL> select owner,index_name,table_name,status from dba_indexes where INDEX_NAME=‘IX_PDBA_GLOBAL’;owner index_name table_name statussys ix_pdba_global pdba valid alter table pdba drop partition p2; SQL> select owner,index_name,table_name,status from dba_indexes where INDEX_NAME=‘IX_PDBA_GLOBAL’;owner index_name table_name statussys ix_pdba_global pdba validsplit 分区:SQL> alter table pdba split partition P4 at(TO_DATE(‘2010-12-21 00:00:00’,‘YYYY-MM-DD HH24:MI:SS’)) into (partition P4, partition P5); SQL> select owner,index_name,table_name,status from dba_indexes where INDEX_NAME=‘IX_PDBA_GLOBAL’;owner index_name table_name statussys ix_pdba_global pdba validdrop 分区时使用update indexesSQL> alter table pdba drop partition P4 UPDATE INDEXES; SQL> select owner,index_name,table_name,status from dba_indexes where INDEX_NAME=‘IX_PDBA_GLOBAL’;owner index_name table_name statussys ix_pdba_global pdba valid Alter index idx_name rebuild [online nologging] 示例: Alter index ix_pdba_global rebuild online nologging; 补充一点,分区表存储空间的问题: SQL> select table_name,partition_name,tablespace_name from user_tab_partitions where table_name=‘DBA’;TABLE_NAME PARTITION_NAME TABLESPACE_NAMEDBA P1 SYSTEM DBA P2 SYSTEM DBA P3 SYSTEM DBA P4 SYSTEM 分区表: SQL> select owner,table_name,tablespace_name,cluster_name from all_tables where table_name=‘DBA’;OWNER TABLE_NAME TABLESPACE_NAME CLUSTER_NAMESYS DBA SQL> select owner,table_name,tablespace_name,cluster_name from all_tables where table_name=‘DAVE’;OWNER TABLE_NAME TABLESPACE_NAME CLUSTER_NAME |
|