学习ORACLE-分区表的使用（2）——分区操作对索引的影响

最新推荐文章于 2024-01-26 16:46:59 发布

VIP文章储加富

最新推荐文章于 2024-01-26 16:46:59 发布

阅读量553

点赞数

分类专栏：学习文章标签： oracle partition 分区索引

本文链接：https://blog.csdn.net/qq_33316544/article/details/85304912

版权

操作分区表时，对其上的索引有何影响

1.准备

1.1 分区表:

CREATE TABLE PART_TABLENAME (
    COLUMN_1 NUMBER(20),
    COLUMN_2 VARCHAR2(10),
    COLUMN_3 DATE)
    PARTITION BY RANGE(COLUMN_3)
    (
    PARTITION P_201801 VALUES LESS THAN (TO_DATE('2018-02-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) TABLESPACE USERS,
    PARTITION P_201802 VALUES LESS THAN (TO_DATE('2018-03-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) TABLESPACE USERS,
    PARTITION P_201803 VALUES LESS THAN (TO_DATE('2018-04-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) TABLESPACE USERS,
    PARTITION P_201804 VALUES LESS THAN (TO_DATE('2018-05-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) TABLESPACE USERS,
    PARTITION P_201805 VALUES LESS THAN (TO_DATE('2018-06-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) TABLESPACE USERS,
    PARTITION P_201806 VALUES LESS THAN (TO_DATE('2018-07-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) TABLESPACE USERS
    );

1.2 数据

循环生成数据以及数据分布：

DECLARE
  v_date DATE := to_date('2018-01-01', 'yyyy-mm-dd');
BEGIN
  FOR i IN 1..6 LOOP
    FOR j IN 1..29 LOOP
      INSERT INTO part_tablename (column_1,column_2,column_3)
         VALUES (i,j,v_date);
      v_date:=v_date+1;
    END LOOP;
  END LOOP;
  COMMIT;
END;

SELECT to_char(column_3,'yyyymm') "月度",COUNT(1) "条数"
FROM part_tablename 
GROUP BY to_char