oracle 表分区、分区索引

 

 

 

 

 

 

 

 

--|/ range分区
create table sale(
       product_id varchar2(5),
       sale_count number(10,2)
)
partition by range (sale_count)
(
          partition P1 values less than (1000),
          partition P2 values less than (2000),
          partition P3 values less than (3000)
);
 
SELECT * FROM SALE;
 
select * from user_tab_partitions;
 
select * from sale partition(p1);
select * from sale partition(p2);
select * from sale partition(p3);
select * from sale partition(p4);
 
insert into sale values('1',500);
insert into sale values('2',1300);
insert into sale values('1',2441);
commit;
 
insert into sale values('1',3500);
commit;
 
alter table sale add partition p4 values less than(maxvalue);
 
alter table sale drop partition p4;
 
SELECT * FROM SALE for update;
 
alter table sale enable row movement;
 
update sale set sale_count = 1200 where product_id = '1';
commit;
update sale set sale_count = 500 where product_id = '1';
 
---------------------------------
--本地索引
create index inx_sale on sale(sale_count) local;
 
select * from user_ind_partitions;
 
--前缀索引
create index idx_sale_global global
       partition by range(sale_count)
       (
         partition p1 values less than(),
         partition p1 values less than(maxvalue),
          
       )
----------------------
create table interval_sale
(sid int, sdate timestamp)
partition by range(sdate)
interval (numtoyminterval(1,'MONTH'))
(
  partition p1 values less than (timestamp '2019-01-01 00:00:00')
);
 
select numtoyminterval(1,'MONTH') from dual;
 
--flashback table emp1 to before drop;
 
--purge recyclebin;
 
select * from user_tab_partitions;
 
insert into interval_sale values(1, to_timestamp('2018-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS.FF'));
insert into interval_sale values(2, to_timestamp('2019-02-01 00:00:00','YYYY-MM-DD HH24:MI:SS.FF'));
insert into interval_sale values(3, to_timestamp('2019-03-02 00:00:00','YYYY-MM-DD HH24:MI:SS.FF'));
insert into interval_sale values(1, to_timestamp('2018-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS.FF'));
insert into interval_sale values(1, to_timestamp('2020-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS.FF'));
commit;
 
select * from interval_sale partition(SYS_P331);