分区表（1）

drop table tb_part_test;
drop table tb_part_test_singe1;

create table tb_part_test(id number,name char(20),hire_date date)
partition by range (hire_date)
(
  partition P1 values less than (TO_DATE('2010-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  partition P2 values less than (TO_DATE('2010-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  partition P3 values less than (TO_DATE('2010-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  partition P4 values less than (maxvalue)
);
create index idx_part_n_h on tb_part_test (name,hire_date) local;
create index idx_part_h on tb_part_test (hire_date) local;

select t.index_name,t.partition_name,t.num_rows,t.status from user_ind_partitions t where t.index_name like 'IDX_PART%' order by 1 ;

select index_name,table_name,status from user_indexes where table_name='TB_PART_TEST';

insert into tb_part_test values(1,'tom1',sysdate-70);
insert into tb_part_test values(2,'tom2',sysdate-60);
insert into tb_part_test values(3,'tom3',sysdate-50);
insert into tb_part_test values(4,'tom4',sysdate-40);
commit;

select * from tb_part_test

select * from tb_part_test partition(p1);
select * from tb_part_test partition(p2);
select * from tb_part_test partition(p3);
select * from tb_part_test partition(p4);

create table tb_part_test_singe1(id number,name char(20),hire_date date);
insert into tb_part_test_singe1 values(6,'david6',sysdate-10);
commit;
create index idx_part_test_n_h on tb_part_test_singe1 (name,hire_date);
create index idx_part_test_h on tb_part_test_singe1 (hire_date);
select t1.index_name,t1.status from user_indexes t1 where t1.index_name like 'IDX_PART%' order by 1 ;

select * from tb_part_test_singe1

alter table tb_part_test exchange partition p3 with table tb_part_test_singe1 INCLUDING INDEXES;

select * from tb_part_test;

alter index IDX_PART_H rebuild partition p3;
alter index IDX_PART_N_H rebuild partition p3;

analyze table tb_part_test compute statistics;