ORACLE - 分割分区时，我还能同时读取其中的数据吗？

Question

语句

• 假设我有一个按日期范围分区的模式（但没有子分区）
• 包含很多行
• 假设我在这个模式中有分区 P1在某个时刻
• ，假设我想将其拆分，因为分区充满了行，分割时间需要 1 到 3 秒

在分割期间，我可以：

• 仍然读取参考分区（例如 P1）中包含的数据吗？
• 仍然读取正在移动或已经移动到新分区中的数据分区已创建？

根据文档：

我没有在官方文档中找到任何说明： https://docs.oracle.com/cd/B13789_01/server.101/b10759/statements_3001.htm#sthref3944
https://docs.oracle.com/cd/E11882_01/server.112/e25523/part_admin002.htm#i1008028
https://docs.oracle.com/database/121/VDBG/GUID-65E169AC-3224-405E-AD1D-9FBF4D5231BA.htm
https://docs.oracle.com/database/121/VDBG/GUID-6BB84952-7021-4CBA-91ED-180E0656E02B.htm#VDBG00303

但是，他们有时会提到锁。但是这个“锁”是避免只写还是也避免读/写？

在您要求自己测试之前：

通常的分割时间仍然不到一秒，但有时我们会遇到分割参考分区的问题。而且，我们的分裂时间是不可预测的。最后，我们并不总是读取该分区中的数据，因此我们需要插入新数据，从而填充尽可能多的分区，然后尝试拆分和读取，这是相当复杂的。

所以如果有人知道答案那就更好了

Answer 1

如果您正在处理范围和日期，为什么要担心拆分分区呢？您可以使用间隔，每次添加新日期（日、周、月、季度、年）时，都会自动添加 PARTITION，从而减少拆分分区的需要。

下面是每日分区的设置以及一些工具，用于重新管理分区并删除旧分区（如果您希望在一段时间后删除旧分区）。

CREATE OR REPLACE PROCEDURE ddl(p_cmd varchar2) 
 authid current_user
is
t1 pls_integer;
BEGIN 
t1 := dbms_utility.get_time; 

dbms_output.put_line(p_cmd);

 execute immediate p_cmd;

dbms_output.put_line((dbms_utility.get_time - t1)/100 || ' seconds');

END;
/

CREATE TABLE PARTITION_RETENTION (
   seq_num NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
    TABLE_NAME VARCHAR2(30),
    RETENTION INTERVAL DAY(3) TO SECOND(0),
 CONSTRAINT
partition_retention_pk primary key (table_name),
CONSTRAINT CHK_NON_ZERO_DAYS CHECK (
        RETENTION > INTERVAL '0' DAY
    ),
    CONSTRAINT CHK_WHOLE_DAYS CHECK (
        EXTRACT(HOUR FROM RETENTION) = 0
        AND EXTRACT(MINUTE FROM RETENTION) = 0
        AND EXTRACT(SECOND FROM RETENTION) = 0
    )
);

insert into PARTITION_RETENTION (TABLE_NAME, RETENTION) 
select 'T1', interval '10' day from dual union all
select 'T3', interval '15' day from dual union all
select 'T4', 15 * interval '1' day from dual union all
select 'T5', 5 * interval '1 00:00:00' day to second from dual;

CREATE TABLE t1 (     
 seq_num NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
   dt   DATE
)
PARTITION BY RANGE (dt)
INTERVAL (NUMTODSINTERVAL(7,'DAY'))
(
   PARTITION OLD_DATA values LESS THAN (TO_DATE('2022-01-01','YYYY-MM-DD'))
);
/

INSERT into t1 (dt)
with dt (dt, interv) as (
select date '2022-01-01', numtodsinterval(1,'DAY') from dual
union all
select dt.dt + interv, interv from dt
where dt.dt + interv < date '2022-02-01')
select dt from dt;
/

create index t1_global_ix on t1 (dt);
/


CREATE OR REPLACE PROCEDURE MaintainPartitions IS  EXPRESSION_IS_OF_WRONG_TYPE EXCEPTION;
    PRAGMA EXCEPTION_INIT(EXPRESSION_IS_OF_WRONG_TYPE, -6550);

    CURSOR PartTables IS
    SELECT TABLE_NAME, INTERVAL
    FROM USER_PART_TABLES 
    WHERE PARTITIONING_TYPE = 'RANGE' 
    ORDER BY TABLE_NAME;

    CURSOR TabParts(aTableName VARCHAR2) IS 
    SELECT PARTITION_NAME, HIGH_VALUE
    FROM USER_TAB_PARTITIONS
 WHERE regexp_like(partition_name,'^SYS_P[[:digit:]]{1,10}')  AND
 TABLE_NAME = aTableName AND
table_name not like 'BIN$%'
      and    interval is not null
    ORDER BY PARTITION_POSITION;

    ym INTERVAL YEAR TO MONTH;
    ds INTERVAL DAY TO SECOND;
    newPartName VARCHAR2(30);
    PERIOD TIMESTAMP;

BEGIN

    FOR aTab IN PartTables LOOP 
        BEGIN       
            EXECUTE IMMEDIATE 'BEGIN :ret := '||aTab.INTERVAL||'; END;' USING OUT ds;
            ym := NULL; 
        EXCEPTION 
            WHEN EXPRESSION_IS_OF_WRONG_TYPE THEN
                EXECUTE IMMEDIATE 'BEGIN :ret := '||aTab.INTERVAL||'; END;' USING OUT ym;
                ds := NULL;         
        END;            

        FOR aPart IN TabParts(aTab.TABLE_NAME) LOOP         
            EXECUTE IMMEDIATE 'BEGIN :ret := '||aPart.HIGH_VALUE||'; END;' USING OUT PERIOD;
            IF ds IS NOT NULL THEN
                IF ds >= INTERVAL '7' DAY THEN
                    -- Weekly partition
                    EXECUTE IMMEDIATE 'BEGIN :ret := TO_CHAR('||aPart.HIGH_VALUE||' - :int, :fmt); END;' USING OUT newPartName, INTERVAL '1' DAY, '"P_"IYYY"W"IW';
                ELSE
                    -- Daily partition
                    EXECUTE IMMEDIATE 'BEGIN :ret := TO_CHAR('||aPart.HIGH_VALUE||' - :int, :fmt); END;' USING OUT newPartName, INTERVAL '1' DAY, '"P_"YYYYMMDD';
                END IF;
            ELSE
                IF ym = INTERVAL '3' MONTH THEN
                    -- Quarterly partition 
                    EXECUTE IMMEDIATE 'BEGIN :ret := TO_CHAR('||aPart.HIGH_VALUE||' - :int, :fmt); END;' USING OUT newPartName, INTERVAL '1' DAY, '"P_"YYYY"Q"Q';
                ELSE
                    -- Monthly partition
                    EXECUTE IMMEDIATE 'BEGIN :ret := TO_CHAR('||aPart.HIGH_VALUE||' - :int, :fmt); END;' USING OUT newPartName, INTERVAL '1' DAY, '"P_"YYYYMM';
                END IF;
            END IF;

            IF newPartName <> aPart.PARTITION_NAME THEN
                EXECUTE IMMEDIATE 'ALTER TABLE '||aTab.TABLE_NAME||' RENAME PARTITION '||aPart.PARTITION_NAME||' TO '||newPartName;
            END IF;             
        END LOOP;
    END LOOP;

END MaintainPartitions;
/

CREATE OR REPLACE PROCEDURE rebuild_index
authid current_user
is

BEGIN 
        for i in (
            select index_owner, index_name, partition_name, 'partition' ddl_type
           from all_ind_partitions
           where status = 'UNUSABLE'
           union all
           select owner, index_name, null, null
           from all_indexes
           where status = 'UNUSABLE'
       )
       loop
         if i.ddl_type is null then
          ddl('alter index '||i.index_owner||'.'||i.index_name||' rebuild parallel 4 online');
         else
          ddl('alter index '||i.index_owner||'.'||i.index_name||' modify '||i.ddl_type||' '||i.partition_name||' rebuild parallel 4 online');
         end if;
       end loop;
 END;
/


EXEC MaintainPartitions;


DECLARE
    CANNOT_DROP_LAST_PARTITION EXCEPTION;
    PRAGMA EXCEPTION_INIT(CANNOT_DROP_LAST_PARTITION, -14758);

    CANNOT_DROP_ONLY_ONE_PARTITION EXCEPTION;
    PRAGMA EXCEPTION_INIT(CANNOT_DROP_ONLY_ONE_PARTITION, -14083);

   ts TIMESTAMP;
 
   CURSOR TablePartitions IS
    SELECT TABLE_NAME, PARTITION_NAME, p.HIGH_VALUE, t.INTERVAL, RETENTION, DATA_TYPE
    FROM USER_PART_TABLES t
        JOIN USER_TAB_PARTITIONS p USING (TABLE_NAME)
        JOIN USER_PART_KEY_COLUMNS pk ON pk.NAME = TABLE_NAME
        JOIN USER_TAB_COLS tc USING (TABLE_NAME, COLUMN_NAME)
        JOIN PARTITION_RETENTION r USING (TABLE_NAME)
    WHERE        pk.object_type     = 'TABLE' AND
   t.partitioning_type = 'RANGE' AND 
    REGEXP_LIKE (tc.data_type, '^DATE$|^TIMESTAMP.*'); 

BEGIN

    FOR aPart IN TablePartitions LOOP
        EXECUTE IMMEDIATE 'BEGIN :ret := '||aPart.HIGH_VALUE||'; END;' USING OUT ts;
        IF ts < SYSTIMESTAMP - aPart.RETENTION THEN
            BEGIN
             ddl('alter table '||aPart.TABLE_NAME||' drop partition '||aPart.partition_name);
 
            EXCEPTION
                WHEN CANNOT_DROP_ONLY_ONE_PARTITION THEN
                    DBMS_OUTPUT.PUT_LINE('Cant drop the only partition '||aPart.PARTITION_NAME ||' from table '||aPart.TABLE_NAME);
  
   ddl('ALTER TABLE '||aPart.TABLE_NAME||' TRUNCATE PARTITION '||aPart.PARTITION_NAME);
                                                   WHEN CANNOT_DROP_LAST_PARTITION THEN
                    BEGIN
                        DBMS_OUTPUT.PUT_LINE('Drop last partition '||aPart.PARTITION_NAME ||' from table '||aPart.TABLE_NAME);
                        EXECUTE IMMEDIATE 'ALTER TABLE '||aPart.TABLE_NAME||' SET INTERVAL ()';
 
 ddl('alter table '||aPart.TABLE_NAME||' drop partition '||aPart.partition_name);

                        EXECUTE IMMEDIATE 'ALTER TABLE '||aPart.TABLE_NAME||' SET INTERVAL( '||aPart.INTERVAL||' )';            
                    EXCEPTION
                        WHEN CANNOT_DROP_ONLY_ONE_PARTITION THEN 
                            -- Depending on the order the "last" partition can be also the "only" partition at the same time
                                                 
                    EXECUTE IMMEDIATE 'ALTER TABLE '||aPart.TABLE_NAME||' SET INTERVAL( '||aPart.INTERVAL||' )';    

DBMS_OUTPUT.PUT_LINE('Cant drop the only partition '||aPart.PARTITION_NAME ||' from table '||aPart.TABLE_NAME);
  
         ddl('ALTER TABLE '||aPart.TABLE_NAME||' TRUNCATE PARTITION '||aPart.PARTITION_NAME);                
               END;
            END;
        END IF;
    END LOOP;
   rebuild_index();
END;