oracle--表分区、分区索引

最新推荐文章于 2025-08-04 23:14:52 发布

weixin_30319153

最新推荐文章于 2025-08-04 23:14:52 发布

阅读量116

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://www.cnblogs.com/eadela/p/11570857.html

本文详细介绍了Oracle数据库中表分区和分区索引的概念及使用方法，包括范围分区、本地索引、前缀索引以及区间分区的具体实现，通过实际SQL语句展示了如何创建、查询、更新和维护分区表及其索引。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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