分区表新增分区测试

1.无MAX分区且非自动分区且全部分区索引

1.1.环境准备

--建表
CREATE TABLE MONKEY.TEST_PART_NULL_NORMAL
(
   ID         NUMBER,
   STU_NAME VARCHAR2(100),
   STU_AGE NUMBER,
   ADD_DATE   DATE
)
PARTITION BY RANGE (ADD_DATE)
   (
   PARTITION DCS_P202211 VALUES LESS THAN (TO_DATE (' 2022-12-01 00:00:00','SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
   PARTITION DCS_P202212 VALUES LESS THAN (TO_DATE (' 2023-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
   PARTITION DCS_P202301 VALUES LESS THAN (TO_DATE (' 2023-02-01 00:00:00','SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
   ) ENABLE ROW MOVEMENT;
   
--建索引
create unique index monkey.TEST_PART_NULL_NORMAL_id on monkey.TEST_PART_NULL_NORMAL(id,add_date) local;

create index monkey.TEST_PART_NULL_NORMAL_name on monkey.TEST_PART_NULL_NORMAL(stu_name) local;

create index monkey.TEST_PART_NULL_NORMAL_union on monkey.TEST_PART_NULL_NORMAL(stu_age,stu_name) local;

--插几条测试数据
INSERT INTO MONKEY.TEST_PART_NULL_NORMAL VALUES (1,'AA',20,TO_DATE('20221102080000','YYYYMMDDHH24MISS'));

INSERT INTO MONKEY.TEST_PART_NULL_NORMAL VALUES (2,'BB',20,TO_DATE('20221202080000','YYYYMMDDHH24MISS'));

INSERT INTO MONKEY.TEST_PART_NULL_NORMAL VALUES (3,'CC',20,TO_DATE('20230102080000','YYYYMMDDHH24MISS'));

1.2.结论

无MAX分区情况下，插入大于最大分区值时报错

INSERT INTO MONKEY.TEST_PART_NULL_NORMAL VALUES (4,'DD',20,TO_DATE('20230202080000','YYYYMMDDHH24MISS'));

增加分区，分区和原来表空间相同，分区索引不会失效

ALTER TABLE  MONKEY.TEST_PART_NULL_NORMAL ADD PARTITION DCS_P202302 VALUES LESS THAN (TO_DATE (' 2023-03-01 00:00:00','SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'));

增加分区，分区和原来表空间不同，分区索引不会失效

ALTER TABLE  MONKEY.TEST_PART_NULL_NORMAL ADD PARTITION DCS_P202302  VALUES LESS THAN (TO_DATE (' 2023-03-01 00:00:00','SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE MYTBS;

MOVE非空分区，分区索引失效

ALTER TABLE MONKEY.TEST_PART_NULL_NORMAL MOVE PARTITION DCS_P202301 TABLESPACE MYTBS;

MOVE空分区，分区索引不失效

ALTER TABLE MONKEY.TEST_PART_NULL_NORMAL MOVE PARTITION DCS_P202302 TABLESPACE MYTBS;

删除非空分区或者空分区，分区索引都不失效

ALTER TABLE MONKEY.TEST_PART_NULL_NORMAL DROP PARTITION DCS_P202302;

2.有MAX分区且非自动分区且全部分区索引

2.1.环境准备

ALTER TABLE MONKEY.TEST_PART_NULL_NORMAL ADD  PARTITION DCS_MAX VALUES LESS THAN (MAXVALUE);

--向MAX分区中添加数据
DECLARE
   v_sql   VARCHAR2 (2000);
BEGIN
   FOR i IN 10 .. 28
   LOOP
      FOR j IN 10 .. 23
      LOOP
         FOR k IN 10 .. 59
         LOOP
            v_sql :='INSERT INTO MONKEY.TEST_PART_NULL_NORMAL VALUES (3,''D'',20,TO_DATE(''202303'|| I|| J|| K|| '00'',''YYYYMMDDHH24MISS''))';
            EXECUTE IMMEDIATE V_SQL;
         END LOOP;
      END LOOP;
   END LOOP;
   COMMIT;
END;

2.2.结论

插入的数值如果大于最后一个正常分区，会插入到MAX分区中

MAX分区只含有下一个新增分区的数据且新增分区和MAX分区同一表空间，分区索引不失效

ALTER TABLE MONKEY.TEST_PART_NULL_NORMAL SPLIT PARTITION DCS_MAX AT ( (TO_DATE(' 2023-06-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN'))) INTO ( PARTITION DCS_P202305 TABLESPACE USERS , PARTITION DCS_MAX TABLESPACE USERS);

MAX分区只含有下一个新增分区的数据且新增分区和MAX分区不同表空间，分区索引失效

ALTER TABLE MONKEY.TEST_PART_NULL_NORMAL SPLIT PARTITION DCS_MAX AT ( (TO_DATE(' 2023-06-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN'))) INTO ( PARTITION DCS_P202305 TABLESPACE MYTBS , PARTITION DCS_MAX TABLESPACE USERS);

MAX分区只含有下一个新增分区的数据且新增分区和MAX分区同一表空间，MAX重新指定分区，分区索引不失效

ALTER TABLE MONKEY.TEST_PART_NULL_NORMAL SPLIT PARTITION DCS_MAX AT ( (TO_DATE(' 2023-05-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN'))) INTO ( PARTITION DCS_P202304 TABLESPACE USERS , PARTITION DCS_MAX TABLESPACE MYTBS);

MAX分区含有下一个新增分区的数据和下个分区之后的数据，MAX分区和新增分区索引失效

ALTER TABLE MONKEY.TEST_PART_NULL_NORMAL SPLIT PARTITION DCS_MAX AT ( (TO_DATE(' 2023-06-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN'))) INTO ( PARTITION DCS_P202305 TABLESPACE MYTBS , PARTITION DCS_MAX TABLESPACE MYTBS);

MAX分区空新增分区，新分区表空间无论是否和MAX分区相同，分区索引不失效

ALTER TABLE MONKEY.TEST_PART_NULL_NORMAL SPLIT PARTITION DCS_MAX AT ( (TO_DATE(' 2023-05-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN'))) INTO ( PARTITION DCS_P202304 TABLESPACE USERS , PARTITION DCS_MAX TABLESPACE USERS);

删除非空分区或者空分区，分区索引都不失效

alter table MONKEY.TEST_PART_NULL_NORMAL drop partition DCS_P202302;

MOVE分区和1.2结论相同

3.有MAX分区且非自动分区且有全局索引

3.1.环境准备

CREATE TABLE MONKEY .TEST_PART_NULL_NORMAL
(
   ID         NUMBER,
   name varchar2(20),
   age number,
   ADD_DATE   DATE
)TABLESPACE HERTBS
PARTITION BY RANGE (ADD_DATE)
   (
   PARTITION DCS_P202301 VALUES LESS THAN (TO_DATE (' 2023-02-01 00:00:00','SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
   PARTITION DCS_P202302 VALUES LESS THAN (TO_DATE (' 2023-03-01 00:00:00','SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
   PARTITION DCS_P202303 VALUES LESS THAN (TO_DATE (' 2023-04-01 00:00:00','SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
   PARTITION DCS_MAX VALUES LESS THAN (MAXVALUE)
   ) ENABLE ROW MOVEMENT;

CREATE INDEX MONKEY.TEST_PART_NULL_NORMAL_ID ON MONKEY.TEST_PART_NULL_NORMAL(ID) TABLESPACE HERTBS;

CREATE INDEX MONKEY.TEST_PART_NULL_NORMAL_AD ON MONKEY.TEST_PART_NULL_NORMAL(ADD_DATE) LOCAL TABLESPACE HERTBS;

3.2.结论

MAX分区空，新增分区，全局和分区索引都不失效

ALTER TABLE MONKEY.TEST_PART_NULL_NORMAL SPLIT PARTITION DCS_MAX AT ( (TO_DATE(' 2023-05-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN'))) INTO ( PARTITION DCS_P202304 TABLESPACE MYTBS , PARTITION DCS_MAX TABLESPACE MYTBS);

MAX非空，且只含下一个月分区的数据，且新分区和MAX在同一个表空间，全局和分区索引都不失效

ALTER TABLE MONKEY.TEST_PART_NULL_NORMAL SPLIT PARTITION DCS_MAX AT ( (TO_DATE(' 2023-06-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN'))) INTO ( PARTITION DCS_P202305 TABLESPACE MYTBS , PARTITION DCS_MAX TABLESPACE MYTBS);

MAX非空，且只含下一个月分区的数据，且新分区和MAX不同一个表空间，全局和分区索引都失效

ALTER TABLE MONKEY.TEST_PART_NULL_NORMAL SPLIT PARTITION DCS_MAX AT ( (TO_DATE(' 2023-09-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN'))) INTO ( PARTITION DCS_P202308 TABLESPACE HERTBS , PARTITION DCS_MAX TABLESPACE HERTBS);

MAX非空，且含有下一个新增分区的数据和下个分区之后的数据，全局和分区索引都失效

ALTER TABLE MONKEY.TEST_PART_NULL_NORMAL SPLIT PARTITION DCS_MAX AT ( (TO_DATE(' 2023-07-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN'))) INTO ( PARTITION DCS_P202306 TABLESPACE MYTBS , PARTITION DCS_MAX TABLESPACE MYTBS);

TRUNCATE非空分区，分区索引未失效，全局索引失效

ALTER TABLE MONKEY.TEST_PART_NULL_NORMAL TRUNCATE PARTITION DCS_P202301;

TRUNCATE空分区，分区索引和全局索引未失效

ALTER TABLE MONKEY.TEST_PART_NULL_NORMAL TRUNCATE PARTITION DCS_P202301;

删除空分区，分区索引和全局索引未失效

ALTER TABLE MONKEY.TEST_PART_NULL_NORMAL DROP PARTITION DCS_P202301;

删除非空分区，分区索引未失效，全局索引失效

ALTER TABLE MONKEY.TEST_PART_NULL_NORMAL DROP PARTITION DCS_P202302;

4.新增分区和新增索引分区表空间

4.1.新增分区表空间

有MAX分区，新增分区不指定表空间的话，和MAX表空间相同

ALTER TABLE MONKEY.TEST_PART_NULL_NORMAL SPLIT PARTITION DCS_MAX AT ( (TO_DATE(' 2023-08-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN'))) INTO ( PARTITION DCS_P202307, PARTITION DCS_MAX);

无MAX分区，新增分区不指定表空间的话，和建表时指定的表空间相同

ALTER TABLE  MONKEY.TEST_PART_NULL_NORMAL ADD PARTITION DCS_P202309 VALUES LESS THAN (TO_DATE (' 2023-10-01 00:00:00','SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'));

4.2.新增索引分区表空间

新增分区索引表空间和建索引时指定的表空间相同（不管有无MAX分区）
建索引时如果没有指定表空间，新增分区索引表空间和新增分区表空间相同
```
CREATE INDEX MONKEY.TEST_PART_NULL_NORMAL_AD
   ON MONKEY.TEST_PART_NULL_NORMAL (NAME)
   LOCAL;
```

查看建索引时是否指定表空间

SELECT OWNER,
       INDEX_NAME,
       CASE
          WHEN INSTR (DBMS_METADATA.GET_DDL ('INDEX', INDEX_NAME, OWNER),
                      'TABLESPACE')
               - INSTR (DBMS_METADATA.GET_DDL ('INDEX', INDEX_NAME, OWNER),
                        'PARTITION') < 0
          THEN
             'YES'
          WHEN INSTR (DBMS_METADATA.GET_DDL ('INDEX', INDEX_NAME, OWNER),
                      'TABLESPACE')
               - INSTR (DBMS_METADATA.GET_DDL ('INDEX', INDEX_NAME, OWNER),
                        'PARTITION') =
                  INSTR (DBMS_METADATA.GET_DDL ('INDEX', INDEX_NAME, OWNER),
                         'TABLESPACE')
          THEN
             '普通索引'
          ELSE
             'NO'
       END
          AS CREATETABLESPACE
  FROM DBA_INDEXES
 WHERE TABLE_OWNER = 'MONKEY' AND TABLE_NAME = 'TEST_PART_NULL_NORMAL';

查看現有索引分區和建索引指定表空間是否相同

SELECT INDEX_OWNER,
         INDEX_NAME,
         CASE
            WHEN COUNT (1) > 1 THEN '建表表空間和分區表空間不同'
            ELSE '相同'
         END
            IFDIFFERENT
    FROM (  SELECT B.INDEX_OWNER, B.INDEX_NAME, B.TABLESPACE_NAME
              FROM DBA_INDEXES A, DBA_IND_PARTITIONS B
             WHERE     A.OWNER = B.INDEX_OWNER
                   AND A.INDEX_NAME = B.INDEX_NAME
                   AND A.TABLE_OWNER = 'GLMLB4_MLBII'
                   AND A.TABLE_NAME = 'R_SMT_MATRIX_UNION'
          GROUP BY INDEX_OWNER, B.INDEX_NAME, B.TABLESPACE_NAME)
GROUP BY INDEX_OWNER, INDEX_NAME

修改分区索引表空间

SELECT B.INDEX_OWNER,
       B.INDEX_NAME,
       B.PARTITION_NAME,
       B.TABLESPACE_NAME
  FROM DBA_INDEXES A, DBA_IND_PARTITIONS B
 WHERE A.OWNER = B.INDEX_OWNER AND A.INDEX_NAME = B.INDEX_NAME AND A.TABLE_OWNER='MONKEY' AND A.TABLE_NAME='TEST_PART_NULL_NORMAL';


SELECT 'ALTER INDEX '||B.INDEX_OWNER||'.'||
       B.INDEX_NAME||' rebuild partition '||
       B.PARTITION_NAME||' TABLESPACE HETBS;'
  FROM DBA_INDEXES A, DBA_IND_PARTITIONS B
 WHERE A.OWNER = B.INDEX_OWNER AND A.INDEX_NAME = B.INDEX_NAME AND A.TABLE_OWNER='MONKEY' AND A.TABLE_NAME='TEST_PART_NULL_NORMAL' AND B.TABLESPACE_NAME='USERS';

5.新增分区实战

5.1.查看是否有新数据进入

select max(add_date) from monkey.monkey_par_test partition(PTT_P202406)；

5.2.保存MAX分区数据

CREATE TABLE monkey.monkey_par_test_bak tablespace users as select * from  monkey.monkey_par_test partition(PARTS_MAX);

5.3.truncate max分区

ALTER TABLE monkey.monkey_par_test TRUNCATE PARTITION PARTS_MAX drop storage;

5.4.新增分区

ALTER TABLE monkey.monkey_par_test SPLIT PARTITION PARTS_MAX AT ( (TO_DATE(' 2024-08-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN'))) INTO ( PARTITION PTT_P202407 TABLESPACE USERS , PARTITION PARTS_MAX TABLESPACE USERS);
ALTER TABLE monkey.monkey_par_test SPLIT PARTITION PARTS_MAX AT ( (TO_DATE(' 2024-09-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN'))) INTO ( PARTITION PTT_P202408 TABLESPACE USERS , PARTITION PARTS_MAX TABLESPACE USERS);

5.5.检查索引状态

SELECT  'alter index '||INDEX_OWNER||'.'||INDEX_NAME||'  rebuild partition '||PARTITION_NAME|| ' online nologging parallel 10;'  FROM DBA_IND_PARTITIONS WHERE STATUS='UNUSABLE';
	
SELECT 'alter index '||owner||'.'||index_name||' rebuild nologging parallel 10;' FROM DBA_INDEXES WHERE STATUS ='UNUSABLE'
UNION ALL
SELECT 'alter index '||owner||'.'||index_name||' noparallel;' FROM DBA_INDEXES WHERE STATUS ='UNUSABLE';

5.6.禁用trigger

# 查看trigger
select * from dba_triggers where table_owner= 'monkey' and table_name='monkey_par_test';
# 禁用trigger
ALTER TRIGGER monkey.TRI_monkey_par_test DISABLE;

5.7.插回数据

# 使用ogg排除用户回插
insert into monkey.monkey_par_test select * from monkey.monkey_par_test_bak;
commit;

5.8.解禁trigger

ALTER TRIGGER monkey.TRI_monkey_par_test ENABLE;

5.9.检查

并行是否关闭，trigger是否为enable，收集统计信息，是否有索引失效

posted @ 2023-01-16 18:51 monkey6 阅读(70) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

monkey6