Oracle DB每日分区 [英] Oracle DB daily partitioning

问题描述

我有下表

  CREATE TABLE "METRIC_VALUE_RAW" 
   (    
    "SUBELEMENT_ID" INTEGER NOT NULL , 
    "METRIC_METADATA_ID" INTEGER NOT NULL , 
    "METRIC_VALUE_INT" INTEGER, 
    "METRIC_VALUE_FLOAT" FLOAT(126), 
    "TIME_STAMP" TIMESTAMP  NOT NULL 
   ) ;

1. 每小时都会使用sql loader将数据加载到表中.
2. 我想创建分区，以便每天将数据放入一个分区.
3. 在表中，我要存储30天的数据.因此，当超过30天时，最早的分区应该被删除.

您能否分享有关如何设计分区的想法.

Can you share your ideas on how I can design the partitions.

推荐答案

下面是一个在Oracle 11g上执行此操作的示例，它工作得很好.我还没有在Oracle 10g上尝试过，您可以尝试一下.

here is an example how to do it on Oracle 11g and it works very well. I haven't tried it on Oracle 10g, you can try it.

这是创建每日分区表的方法:

This is the way, how to create a table with daily partitions:

CREATE TABLE XXX (
    partition_date   DATE,
  ...,
  ...,
)
PARTITION BY RANGE (partition_date)
INTERVAL (NUMTODSINTERVAL(1, 'day'))
(
   PARTITION part_01 values LESS THAN (TO_DATE('2000-01-01','YYYY-MM-DD'))
)
TABLESPACE  MY_TABLESPACE
NOLOGGING;

如上所示，Oracle将在2000年1月1日之后为每个不同的partition_day自动创建单独的分区.其partition_date早于该日期的记录将存储在名为"part_01"的分区中.

As you see above, Oracle will automaticaly create separate partitions for each distinct partition_day after 1st January 2000. The records, whose partition_date is older than this date, will be stored in partition called 'part_01'.

您可以使用以下语句监视表分区:

You can monitore your table partitions using this statement:

SELECT * FROM user_tab_partitions WHERE table_name = 'XXX';

然后，当您要删除某些分区时，请使用以下命令:

Afterwards, when you would like to delete some partitions, use following command:

ALTER TABLE XXX DROP PARTITION AAAAAA UPDATE GLOBAL INDEXES

其中"AAAAAA"是分区名称.

where 'AAAAAA' is parition name.

我希望它将对您有帮助！

I hope it will help you!

这篇关于Oracle DB每日分区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！