Oracle 中的动态表分区
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2191780/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Dynamic table partitioning in Oracle
提问by Eduardo Z.
I'm in the process of building a database storage for my app consisting on a single table with a huge data volume (hundreds of millions of records). I'm planning on having an index on the date field, since I'll be doing a batch recovery of all the records in a given period of time every now and then (for example, retrieving all records for the following day, at midnight).
我正在为我的应用程序构建一个数据库存储，该数据库存储包含在具有巨大数据量（数亿条记录）的单个表上。我计划在日期字段上建立索引，因为我将不时地对给定时间段内的所有记录进行批量恢复（例如，检索第二天午夜的所有记录）。
Since the number of records is huge and performance is an important concern in this system, I would like to know if there is a way I can dynamically partition my table so that I can retrieve the records faster, creating and truncating partitions as they are no longer needed. For example, how would I go about creating a partition for the following day and populating it with the rest of the data after I'm done processing today's records?
由于记录数量巨大并且性能是该系统中的一个重要问题，我想知道是否有一种方法可以对我的表进行动态分区，以便我可以更快地检索记录，创建和截断分区，因为它们不是不再需要。例如，在我处理完今天的记录后，我将如何为第二天创建一个分区并用其余数据填充它？
回答by APC
In 11g we can define INTERVAL partitions, and Oracle will automatically create new partitions when it gets new records whose keys don't fit in any of the existing ranges. This is a very cool feature. Find out more.
在 11g 中，我们可以定义 INTERVAL 分区，当 Oracle 获取其键不适合任何现有范围的新记录时，它将自动创建新分区。这是一个非常酷的功能。 了解更多。
One thing to bear in mind is that Partitioning is a chargeable extra on top of the Enterprise Edition license. So it is not cheap to use.
要记住的一件事是，分区是企业版许可证之上的一项额外收费。所以使用起来并不便宜。
回答by Vincent Malgrat
you can automate the process of creating or truncating partitions through the use of dynamic SQL. You would write procedures with either EXECUTE IMMEDIATEor DBMS_SQLand you would schedule them with DBMS_JOBor DBMS_SCHEDULER(DBMS_SCHEDULERis a 10g feature and is more versatile than DBMS_JOB).
您可以通过使用动态 SQL自动执行创建或截断分区的过程。您可以使用EXECUTE IMMEDIATE或EXECUTE IMMEDIATE编写过程，DBMS_SQL并使用DBMS_JOBor安排它们DBMS_SCHEDULER（DBMS_SCHEDULER是 10g 的一个功能，比 更通用DBMS_JOB）。
You probably want to build the partition statements manually first and automate the process later when you are confident with the DDL. You will find all the synthax in the documentation for the ALTER TABLE statement.
您可能希望先手动构建分区语句，然后在您对 DDL 有信心时自动执行该过程。您将在 ALTER TABLE 语句的文档中找到所有 synthax 。
回答by John Fuller
Here is a sample of something I came up with for creating partitions using SYSDATEand an offset. I had to create replacement parameters with a string concatenated to the SYSDATE:
这是我想出的使用SYSDATE和偏移量创建分区的示例。我必须使用连接到 的字符串创建替换参数SYSDATE：
COLUMN temp_var new_value partition_name_01;
SELECT 'TABLE_NAME_' || TO_CHAR(SYSDATE - 3, 'YYYYMMDD') AS temp_var FROM dual;
COLUMN temp_var new_value partition_date_01;
SELECT TO_CHAR(SYSDATE - 3, 'SYYYY-MM-DD') || ' 00:00:00' AS temp_var FROM dual;
COLUMN temp_var new_value partition_name_02;
SELECT 'TABLE_NAME_' || TO_CHAR(SYSDATE - 2, 'YYYYMMDD') AS temp_var FROM dual;
COLUMN temp_var new_value partition_date_02;
SELECT TO_CHAR(SYSDATE - 2, 'SYYYY-MM-DD') || ' 00:00:00' AS temp_var FROM dual;
COLUMN temp_var new_value partition_name_03;
SELECT 'TABLE_NAME_' || TO_CHAR(SYSDATE - 1, 'YYYYMMDD') AS temp_var FROM dual;
COLUMN temp_var new_value partition_date_03;
SELECT TO_CHAR(SYSDATE - 1, 'SYYYY-MM-DD') || ' 00:00:00' AS temp_var FROM dual;
COLUMN temp_var new_value partition_name_04;
SELECT 'TABLE_NAME_' || TO_CHAR(SYSDATE, 'YYYYMMDD') AS temp_var FROM dual;
COLUMN temp_var new_value partition_date_04;
SELECT TO_CHAR(SYSDATE, 'SYYYY-MM-DD') || ' 00:00:00' AS temp_var FROM dual;
CREATE TABLE TABLE_NAME
(
SEQ_NO NUMBER NOT NULL,
INSERT_DATE DATE NOT NULL,
FIRST_NAME VARCHAR2 (256 BYTE),
LAST_NAME VARCHAR2 (256 BYTE),
ID_NUM NUMBER,
ID_STATUS NUMBER
)
PARTITION BY RANGE
(INSERT_DATE)
SUBPARTITION BY LIST
(ID_STATUS)
SUBPARTITION TEMPLATE (
SUBPARTITION SP1 VALUES (0) TABLESPACE &tblspce,
SUBPARTITION SP2 VALUES (1) TABLESPACE &tblspce,
SUBPARTITION SP3 VALUES (2) TABLESPACE &tblspce)
(
PARTITION &partition_name_01
VALUES LESS THAN
(TO_DATE ('&partition_date_01',
'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN')),
PARTITION &partition_name_02
VALUES LESS THAN
(TO_DATE ('&partition_date_02',
'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN')),
PARTITION &partition_name_03
VALUES LESS THAN
(TO_DATE ('&partition_date_03',
'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN')),
sysdate
PARTITION &partition_name_04
VALUES LESS THAN
(TO_DATE ('&partition_date_04',
'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN')))
ENABLE ROW MOVEMENT;
回答by user2758538
There's a product that takes care of it automatically. PartitionManager for Oracle provides automatic partition management, based on the organization retention, including purging and archiving old data, statistics copy etc. You can try it out at http://www.xyrosoft.com
有一种产品可以自动处理它。PartitionManager for Oracle 提供基于组织保留的自动分区管理，包括清除和归档旧数据、统计复制等。您可以在http://www.xyrosoft.com 上试用
