分区表新增分区测试

标签：INDEX 00 MAX 分区 PARTITION PART 分区表测试

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.新增分区和新增索引分区表空间

3.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'));

3.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';

标签：INDEX,00,MAX,分区,PARTITION,PART,分区表,测试
From： https://www.cnblogs.com/monkey6/p/17056136.html