准备表空间
create tablespace YEAR2018 datafile
'/u02/oradata/MPAPEX/pdb1/YEAR2018_1.dbf' size 16M AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED,
'/u02/oradata/MPAPEX/pdb1/YEAR2018_2.dbf' size 16M AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED
default storage (initial 128K next 2M pctincrease 0);
create tablespace YEAR2019 datafile
'/u02/oradata/MPAPEX/pdb1/YEAR2019_1.dbf' size 16M AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED,
'/u02/oradata/MPAPEX/pdb1/YEAR2019_2.dbf' size 16M AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED
default storage (initial 128K next 2M pctincrease 0);
create tablespace YEAR2020 datafile
'/u02/oradata/MPAPEX/pdb1/YEAR2020_1.dbf' size 16M AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED,
'/u02/oradata/MPAPEX/pdb1/YEAR2020_2.dbf' size 16M AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED
default storage (initial 128K next 2M pctincrease 0);
create tablespace I_YEAR2018 datafile
'/u02/oradata/MPAPEX/pdb1/I_YEAR2018_1.dbf' size 16M AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED,
'/u02/oradata/MPAPEX/pdb1/I_YEAR2018_2.dbf' size 16M AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED
default storage (initial 128K next 2M pctincrease 0);
create tablespace I_YEAR2019 datafile
'/u02/oradata/MPAPEX/pdb1/I_YEAR2019_1.dbf' size 16M AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED,
'/u02/oradata/MPAPEX/pdb1/I_YEAR2019_2.dbf' size 16M AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED
default storage (initial 128K next 2M pctincrease 0);
create tablespace I_YEAR2020 datafile
'/u02/oradata/MPAPEX/pdb1/I_YEAR2020_1.dbf' size 16M AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED,
'/u02/oradata/MPAPEX/pdb1/I_YEAR2020_2.dbf' size 16M AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED
default storage (initial 128K next 2M pctincrease 0);
创建范围分区表
--drop TABLE test_range_tab;
CREATE TABLE test_range_tab
(
range_tab_seq number not null,
range_tab_no varchar2(20),
range_tab_name varchar2(20),
range_id number,
range_tab_date date --範圍分區列
)partition by range (range_tab_date)
(
partition P2018 values less than (TO_DATE('2019-01-01', 'YYYY-MM-DD')) tablespace YEAR2018,
partition P2019 values less than (TO_DATE('2020-01-01', 'YYYY-MM-DD')) tablespace YEAR2019,
partition P2020 values less than (TO_DATE('2021-01-01', 'YYYY-MM-DD')) tablespace YEAR2020
);
创建主键(local 分区索引)
alter table test_range_tab
add constraint pk_test_range_tab_local primary key (range_tab_date,range_tab_seq)
using index local
(
PARTITION P2018 TABLESPACE I_YEAR2018,
PARTITION P2019 TABLESPACE I_YEAR2019,
PARTITION P2020 TABLESPACE I_YEAR2020
);
#select dbms_metadata.get_ddl('INDEX',upper('pk_test_range_tab_local'),'SYSTEM') index_name FROM DUAL;
CREATE UNIQUE INDEX "SYSTEM"."PK_TEST_RANGE_TAB_LOCAL" ON "SYSTEM"."TEST_RANGE_TAB" ("RANGE_TAB_DATE", "RANGE_TAB_SEQ")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) LOCAL
(PARTITION "P2018"
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "I_YEAR2018" ,
PARTITION "P2019"
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "I_YEAR2019" ,
PARTITION "P2020"
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "I_YEAR2020" )
创建索引(local 分区索引)
create index idx_test_range_tab_local_1 on test_range_tab(range_tab_date,range_tab_no)
local
(
PARTITION P2018 TABLESPACE I_YEAR2018,
PARTITION P2019 TABLESPACE I_YEAR2019,
PARTITION P2020 TABLESPACE I_YEAR2020
);
#select dbms_metadata.get_ddl('INDEX',upper('idx_test_range_tab_local_1'),'SYSTEM') index_name FROM DUAL;
CREATE INDEX "SYSTEM"."IDX_TEST_RANGE_TAB_LOCAL_1" ON "SYSTEM"."TEST_RANGE_TAB" ("RANGE_TAB_DATE", "RANGE_TAB_NO")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) LOCAL
(PARTITION "P2018"
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "I_YEAR2018" ,
PARTITION "P2019"
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "I_YEAR2019" ,
PARTITION "P2020"
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "I_YEAR2020" )
插入测试数据
insert into test_range_tab(range_tab_seq,range_tab_name,range_tab_date,range_id) values(1,'a1',sysdate-365,1);
insert into test_range_tab(range_tab_seq,range_tab_name,range_tab_date,range_id) values(2,'b1',sysdate-365,2);
insert into test_range_tab(range_tab_seq,range_tab_name,range_tab_date,range_id) values(3,'c1',sysdate-365,2);
insert into test_range_tab(range_tab_seq,range_tab_name,range_tab_date,range_id) values(11,'a2',sysdate-365,101);
insert into test_range_tab(range_tab_seq,range_tab_name,range_tab_date,range_id) values(12,'b2',sysdate-365,102);
insert into test_range_tab(range_tab_seq,range_tab_name,range_tab_date,range_id) values(13,'c2',sysdate-365,103);
insert into test_range_tab(range_tab_seq,range_tab_name,range_tab_date,range_id) values(21,'a3',sysdate-365,1001);
insert into test_range_tab(range_tab_seq,range_tab_name,range_tab_date,range_id) values(22,'b3',sysdate-365,1002);
insert into test_range_tab(range_tab_seq,range_tab_name,range_tab_date,range_id) values(23,'c3',sysdate-365,1002);
commit;
创建local 分区索引(不指定表空间 【索引和分区将存放在同一个表空间】),单分区列(有前缀的本地索引)
create index idx_range_tab_date_local_2 on test_range_tab(range_tab_date) local;
#select dbms_metadata.get_ddl('INDEX',upper('idx_range_tab_date_local_2'),'SYSTEM') index_name FROM DUAL;
CREATE INDEX "SYSTEM"."IDX_RANGE_TAB_DATE_LOCAL_2" ON "SYSTEM"."TEST_RANGE_TAB" ("RANGE_TAB_DATE")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) LOCAL
(PARTITION "P2018"
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "YEAR2018" ,
PARTITION "P2019"
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "YEAR2019" ,
PARTITION "P2020"
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "YEAR2020" )
SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions a1
where a1.index_name=upper('idx_range_tab_date_local_2');
INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS
IDX_RANGE_TAB_DATE_LOCAL_2 P2018 TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') USABLE
IDX_RANGE_TAB_DATE_LOCAL_2 P2019 TO_DATE(' 2020-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') USABLE
IDX_RANGE_TAB_DATE_LOCAL_2 P2020 TO_DATE(' 2021-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') USABLE
创建local 分区索引(不指定表空间 ),分区列 + 其他字段 (有前缀的本地索引)
create index idx_test_range_tab_local_3 on test_range_tab(range_tab_date,range_tab_name) local;
#select dbms_metadata.get_ddl('INDEX',upper('idx_test_range_tab_local_3'),'SYSTEM') index_name FROM DUAL;
CREATE INDEX "SYSTEM"."IDX_TEST_RANGE_TAB_LOCAL_3" ON "SYSTEM"."TEST_RANGE_TAB" ("RANGE_TAB_DATE", "RANGE_TAB_NAME")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) LOCAL
(PARTITION "P2018"
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "YEAR2018" ,
PARTITION "P2019"
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "YEAR2019" ,
PARTITION "P2020"
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "YEAR2020" )
无分区列(无前缀的本地索引)
create index idx_test_range_tab_local_4 on test_range_tab(range_tab_name) local;
#select dbms_metadata.get_ddl('INDEX',upper('idx_test_range_tab_local_4'),'SYSTEM') index_name FROM DUAL;
CREATE INDEX "SYSTEM"."IDX_TEST_RANGE_TAB_LOCAL_4" ON "SYSTEM"."TEST_RANGE_TAB" ("RANGE_TAB_NAME")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) LOCAL
(PARTITION "P2018"
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "YEAR2018" ,
PARTITION "P2019"
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "YEAR2019" ,
PARTITION "P2020"
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "YEAR2020" )
#从user_part_indexes视图也可以证明刚才创建的索引,一个是有前缀的,一个是无前缀的
[oracle@xag182 ~]$ sql system/123456@127.0.0.1:1521/PDB1
SQL> SET SQLFORMAT ansiconsole
SQL> select index_name,table_name,partitioning_type,locality,ALIGNMENT from user_part_indexes where table_name=upper('test_range_tab');
INDEX_NAME TABLE_NAME PARTITIONING_TYPE LOCALITY ALIGNMENT
PK_TEST_RANGE_TAB_LOCAL TEST_RANGE_TAB RANGE LOCAL PREFIXED
IDX_TEST_RANGE_TAB_LOCAL_1 TEST_RANGE_TAB RANGE LOCAL PREFIXED
IDX_RANGE_TAB_DATE_LOCAL_2 TEST_RANGE_TAB RANGE LOCAL PREFIXED
IDX_TEST_RANGE_TAB_LOCAL_3 TEST_RANGE_TAB RANGE LOCAL PREFIXED
IDX_TEST_RANGE_TAB_LOCAL_4 TEST_RANGE_TAB RANGE LOCAL NON_PREFIXED
#唯一索引必须包含分区列
SQL> create unique index idx_u_local_5 on test_range_tab(range_tab_name) local;
Error starting at line : 1 in command -
create unique index idx_u_local_2 on test_range_tab(range_tab_name) local
Error report -
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index
SQL> create unique index idx_u_local_5 on test_range_tab(range_tab_date,range_tab_no,range_tab_name) local;
Index IDX_U_LOCAL_2 created.
创建普通索引(没有local 参数)分区操作后如删除分区后需要重建索引,alter index 索引名称 rebuild online;
drop index IDX_TEST_RANGE_TAB_LOCAL_1;
drop index IDX_RANGE_TAB_DATE_LOCAL_2;
drop index IDX_TEST_RANGE_TAB_LOCAL_3;
drop index IDX_TEST_RANGE_TAB_LOCAL_4;
drop index IDX_U_LOCAL_5;
#单分区列
create index idx_range_tab_date_1 on test_range_tab(range_tab_no);
#select dbms_metadata.get_ddl('INDEX',upper('idx_range_tab_date_1'),'SYSTEM') index_name FROM DUAL;
CREATE INDEX "SYSTEM"."IDX_RANGE_TAB_DATE_1" ON "SYSTEM"."TEST_RANGE_TAB" ("RANGE_TAB_NO")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM"
drop index idx_range_tab_date_1 ;
create index idx_range_tab_date_1 on test_range_tab(range_tab_no) global;
#select dbms_metadata.get_ddl('INDEX',upper('idx_range_tab_date_1'),'SYSTEM') index_name FROM DUAL;
CREATE INDEX "SYSTEM"."IDX_RANGE_TAB_DATE_1" ON "SYSTEM"."TEST_RANGE_TAB" ("RANGE_TAB_NO")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM"
#普通唯一索引
create unique index idx_uique_1 on test_range_tab(range_tab_name);
#插入失败(唯一索引冲突)
insert into test_range_tab(range_tab_seq,range_tab_name,range_tab_date,range_id) values(4,'a1',sysdate,4);
全局分区索引
create index idx_test_range_tab_global_1 on test_range_tab(range_id)
global partition by range(range_id)
(
partition p_less100 values less than (100),
partition p_less1000 values less than (1000),
partition p_lessmax values less than (MAXVALUE)
);
#select dbms_metadata.get_ddl('INDEX',upper('idx_test_range_tab_global_1'),'SYSTEM') index_name FROM DUAL;
CREATE INDEX "SYSTEM"."IDX_TEST_RANGE_TAB_GLOBAL_1" ON "SYSTEM"."TEST_RANGE_TAB" ("RANGE_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" GLOBAL PARTITION BY RANGE ("RANGE_ID")
(PARTITION "P_LESS100" VALUES LESS THAN (100)
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ,
PARTITION "P_LESS1000" VALUES LESS THAN (1000)
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ,
PARTITION "P_LESSMAX" VALUES LESS THAN (MAXVALUE)
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" )
#分区索引列+其他列
create index idx_test_range_tab_global_2 on test_range_tab(range_id,range_tab_name)
global partition by range(range_id)
(
partition p_less100 values less than (100) TABLESPACE I_YEAR2018,
partition p_less1000 values less than (1000) TABLESPACE I_YEAR2019,
partition p_lessmax values less than (MAXVALUE) TABLESPACE I_YEAR2020
);
#select dbms_metadata.get_ddl('INDEX',upper('idx_test_range_tab_global_2'),'SYSTEM') index_name FROM DUAL;
CREATE INDEX "SYSTEM"."IDX_TEST_RANGE_TAB_GLOBAL_2" ON "SYSTEM"."TEST_RANGE_TAB" ("RANGE_ID", "RANGE_TAB_NO")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" GLOBAL PARTITION BY RANGE ("RANGE_ID")
(PARTITION "P_LESS100" VALUES LESS THAN (100)
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "I_YEAR2018" ,
PARTITION "P_LESS1000" VALUES LESS THAN (1000)
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "I_YEAR2019" ,
PARTITION "P_LESSMAX" VALUES LESS THAN (MAXVALUE)
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "I_YEAR2020" )
SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions
where index_name in(upper('idx_test_range_tab_global_1'),upper('idx_test_range_tab_global_2'));
INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS
IDX_TEST_RANGE_TAB_GLOBAL_1 P_LESS100 100 USABLE
IDX_TEST_RANGE_TAB_GLOBAL_1 P_LESS1000 1000 USABLE
IDX_TEST_RANGE_TAB_GLOBAL_1 P_LESSMAX MAXVALUE USABLE
IDX_TEST_RANGE_TAB_GLOBAL_2 P_LESS100 100 USABLE
IDX_TEST_RANGE_TAB_GLOBAL_2 P_LESS1000 1000 USABLE
IDX_TEST_RANGE_TAB_GLOBAL_2 P_LESSMAX MAXVALUE USABLE
#删除表中的一个分区
alter table test_range_tab drop partition P2018;
#删除分区时 加上 update global indexes 则无需重建索引(但本次操作会慢)
alter table test_range_tab drop partition P2018 update global indexes;
#删除后全局分区索引失效
SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions
where index_name in(upper('idx_test_range_tab_global_1'),upper('idx_test_range_tab_global_2'));
INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS
IDX_TEST_RANGE_TAB_GLOBAL_1 P_LESS100 100 UNUSABLE
IDX_TEST_RANGE_TAB_GLOBAL_1 P_LESS1000 1000 UNUSABLE
IDX_TEST_RANGE_TAB_GLOBAL_1 P_LESSMAX MAXVALUE UNUSABLE
IDX_TEST_RANGE_TAB_GLOBAL_2 P_LESS100 100 UNUSABLE
IDX_TEST_RANGE_TAB_GLOBAL_2 P_LESS1000 1000 UNUSABLE
IDX_TEST_RANGE_TAB_GLOBAL_2 P_LESSMAX MAXVALUE UNUSABLE
#重建全局索引
alter index idx_test_range_tab_global_1 rebuild partition p_less100 online nologging;
alter index idx_test_range_tab_global_1 rebuild partition p_less1000 online nologging;
alter index idx_test_range_tab_global_1 rebuild partition p_lessmax online nologging;
alter index idx_test_range_tab_global_2 rebuild partition p_less100 TABLESPACE I_YEAR2018;
alter index idx_test_range_tab_global_2 rebuild partition p_less1000 TABLESPACE I_YEAR2019;
alter index idx_test_range_tab_global_2 rebuild partition p_lessmax TABLESPACE I_YEAR2020;
SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions
where index_name in(upper('idx_test_range_tab_global_1'),upper('idx_test_range_tab_global_2'));
INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS
IDX_TEST_RANGE_TAB_GLOBAL_1 P_LESS100 100 USABLE
IDX_TEST_RANGE_TAB_GLOBAL_1 P_LESS1000 1000 USABLE
IDX_TEST_RANGE_TAB_GLOBAL_1 P_LESSMAX MAXVALUE USABLE
IDX_TEST_RANGE_TAB_GLOBAL_2 P_LESS100 100 USABLE
IDX_TEST_RANGE_TAB_GLOBAL_2 P_LESS1000 1000 USABLE
IDX_TEST_RANGE_TAB_GLOBAL_2 P_LESSMAX MAXVALUE USABLE