oracle 根据日期列向其他分区添加一个新分区，每天添加一个分区

pjngdqdw  于 2023-02-07  发布在  Oracle
关注(0)|答案(1)|浏览(218)

我想设计一个表，如表F和分区它的方式，通过添加一个新的数据，假设它不在现有的分区创建一个新的分区。（分区基于日期列和1分区为每一天）。我是新的甲骨文，请帮助我，什么是最好的主意？我该如何写这个代码。表F是：

DATE           Amount           ID
2015-05-18     1000             1
2015-05-19     2000             2
2015-05-20     3000             3
2015-05-21     4000             4
2015-05-21     5000             5
2015-05-21     3000             6
2015-05-22     2002             7
oracle

1条答案

按热度按时间
qxgroojn

qxgroojn1#

您可以在日期列上创建几个不同的分区，即（每日、每周、每月、每季度或每年），并在您需要确定的列上包含本地或全局索引
下面是一个具有全局索引的每周PARTITION的示例。当向表中插入新日期时，PARTITION将自动添加系统生成的名称。

/* weekly  PARTITION */

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 (DATE '2022-04-01')
  );
/

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

create index t1_global_ix on T1 (dt);
/

每次创建新分区时，Oracle都会生成一个系统名称，该名称非常神秘
下面是PARTITION名称的列表，它是Oracle在我加载上面的数据时生成的

SELECT PARTITION_NAME
    FROM USER_TAB_PARTITIONS
WHERE TABLE_NAME = 'T1'

PARTITION_NAME
OLD_DATA
SYS_P458773
SYS_P458774
SYS_P458775
SYS_P458776
SYS_P458777
SYS_P458778
SYS_P458779
SYS_P458780
SYS_P458781
SYS_P458782
SYS_P458783
SYS_P458784
SYS_P458785
SYS_P458786
SYS_P458787
SYS_P458788
SYS_P458789
SYS_P458790
SYS_P458791
SYS_P458792
SYS_P458793
SYS_P458794

尽管PARTITION管理将使用系统生成的PARTITION名称，但我使用下面的过程将它们重命名为更有意义的名称。
让我们创建并运行该过程，然后再次查看名称。正如您所看到的，由于我们使用的是周分区，因此名称P_表示分区YYYY（4位数）所在的年份，W表示一年中的周，##表示一年中的周数。
我建议使用调度器每天至少运行一次这个进程。你可以运行它很多次，因为它不会造成任何伤害。

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

EXEC MaintainPartitions

SELECT PARTITION_NAME
    FROM USER_TAB_PARTITIONS
WHERE TABLE_NAME = 'T1'

PARTITION_NAME
OLD_DATA
P_2022W14
P_2022W15
P_2022W16
P_2022W17
P_2022W18
P_2022W19
P_2022W20
P_2022W21
P_2022W22
P_2022W23
P_2022W24
P_2022W25
P_2022W26
P_2022W27
P_2022W28
P_2022W29
P_2022W30
P_2022W31
P_2022W32
P_2022W33
P_2022W34

SELECT COUNT(*) FROM USER_TAB_PARTITIONS 
COUNT(*)
31

下一步是设置RETENTION表。每个间隔范围PARTITION都应该有一个条目。
RETENTION值由您决定。在我的示例中，我为表T1选择了30天。这意味着，当PARTITION的最大值大于30天时，它就有资格被删除。因此，在设置这些值时要明智地选择。
注意：我列出了其他表的名称，以显示每个表如何拥有自己的值。

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 'T0', interval '10' day from dual union all
select 'T1', interval '30' day from dual union all
select 'T2', interval '15' day from dual union all
select 'T3', interval '30' 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;

以下是需要创建的3个程序。
ddl过程是一个 Package 器，它向您显示正在处理的内容以及所需的时间。
rebuild_index过程显然会重建任何无效的索引。正如我上面提到的，如果您使用的是全局索引，并且删除了PARTITION，则需要重建索引。在本例中，我硬编码了并行4，但如果您有足够的CPU能力，则可能需要增加数量以满足您的需要。
此外，还有其他方法可以将索引标记为不可用，因此您可能需要考虑安排该任务。
最后是匿名块。它实际上删除了已经过了保留期的PARTITIONS。这需要每天安排一次！！
如果你仔细观察匿名块，最后一步是调用重建索引过程，所以如果一个索引不可用，它将被重建。
现在，让我们运行该流程，看看会发生什么。

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

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;

 
alter table T1 drop partition OLD_DATA
.02 seconds
alter table T1 drop partition P_2022W14
.01 seconds
alter table T1 drop partition P_2022W15
.02 seconds
alter table T1 drop partition P_2022W16
.01 seconds
alter table T1 drop partition P_2022W17
.02 seconds
alter table T1 drop partition P_2022W18
.01 seconds
alter table T1 drop partition P_2022W19
.02 seconds
alter table T1 drop partition P_2022W20
.01 seconds
alter table T1 drop partition P_2022W21
.01 seconds
alter table T1 drop partition P_2022W22
.02 seconds
alter table T1 drop partition P_2022W23
.01 seconds
alter table T1 drop partition P_2022W24
.01 seconds
alter table T1 drop partition P_2022W25
.01 seconds
alter table T1 drop partition P_2022W26
.01 seconds
alter table T1 drop partition P_2022W27
.02 seconds
alter index SQL_WUKYPRGVPTOUVLCAEKUDCRCQI.T1_GLOBAL_IX rebuild parallel 4 online
.1 seconds
…
…
…
alter index SQL_WUKYPRGVPTOUVLCAEKUDCRCQI.T1_GLOBAL_IX rebuild parallel 4 online
.1 seconds

SELECT count(*) from USER_TAB_PARTITIONS 
Where
table_name not like 'BIN$%'

8

SELECT PARTITION_NAME
    FROM USER_TAB_PARTITIONS
WHERE TABLE_NAME = 'T1'
AND
table_name not like 'BIN$%'

P_2022W28
P_2022W29
P_2022W30
P_2022W31
P_2022W32
P_2022W33
P_2022W34
P_2022W35
赞(0）分享  回复(0）举报  2023-02-07
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com