“动态" oracle 11g中的分区 [英] "Dynamic" partitions in oracle 11g

问题描述

我有一个包含很多信息的日志表. 我想将其分为两个部分:第一部分是过去一个月的日志，因为它们通常被查看.第二部分是该年度剩余时间的日志(压缩).

I have a log table with a lot of information. I would like to partition it into two: first part is the logs from the past month, since they are commonly viewed. Second part is the logs from the rest of the year (Compressed).

我的问题是所有分区示例，其中直到2013年1月1日"，比2013年1月1日更新"-即具有固定的日期...

My problem is that all the examples of partitions where "up until 1/1/2013", "more recent than 1/1/2013" - That is with fixed dates...

我正在寻找/期望的是一种在上个月定义分区的方法，这样，当日期改变时，来自30天前的日志将自动"传输到压缩分区.

What I am looking for/expecting is a way to define a partition on the last month, so that when the day changes, the logs from 30 days ago, are "automatically" transferred to the compressed partition.

我想我可以创建另一个完全压缩的表并使用JOBS移动信息，但是我希望有一个内置的解决方案.

I guess I can create another table which is completley compressed and move info using JOBS, but I was hoping for a built-in solution.

谢谢.

推荐答案

我认为您想要基于日期的间隔分区.这将自动为您生成分区.例如，每月的分区为:

I think you want interval partitions based on a date. This will automatically generate the partitions for you. For example, monthly partitions would be:

create table test_data (
   created_date      DATE default sysdate not null,
   store_id          NUMBER,
   inventory_id      NUMBER,
   qty_sold          NUMBER
)
PARTITION BY RANGE (created_date)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
(
   PARTITION part_01 values LESS THAN (TO_DATE('20130101','YYYYMMDD'))
)

在插入数据时，Oracle将放入适当的分区，或者在需要时创建一个分区.分区名称将有点神秘(SYS_xxxx)，但是您可以使用"partition for"子句来仅获取所需的月份.例如:

As data is inserted, Oracle will put into the proper partition or create one if needed. The partition names will be a bit cryptic (SYS_xxxx), but you can use the "partition for" clause to grab only the month you want. For example:

select * from test_data partition for (to_date('20130101', 'YYYYMMDD'))

这篇关于“动态" oracle 11g中的分区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！