--|/ 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);
