分区Oracle表上的部分局部索引

Question

我们有一个大的事务性表，在事务日期(日期)进行分区。每天增加几百万条记录。我们需要在这个表上进行选择，在过去的五天中，包括帐户。

为了保持有点表现力，我建议将表划分为日级(已完成)，并在accountid上创建一个本地索引(前缀或非前缀)。现在，一个团队成员说这是不可能的，因为他们只需要在表的最后五个分区上创建一个索引，这需要花费太多的精力。

AFAIK，索引的创建只需要完成一次，然后每天只需要创建新分区上的索引。此外，超过5天的分区上的索引将不再被使用，因此没有什么可做的。

你对此有什么看法？如何最好地解决这个问题，考虑到性能？

Answer 1

您只能为某些分区创建索引--当然，这仅适用于本地索引。

问题是，为了在新分区上创建索引，您必须设置DEFAULT ATTRIBUTES INDEXING ON。索引分区超过5天，您必须手动删除(即ALTER TABLE ... MODIFY PARTITION ... UNUSABLE LOCAL INDEXES;或ALTER INDEX ... MODIFY PARTITION ... UNUSABLE;)。

当然，您可以设置DEFAULT ATTRIBUTES INDEXING OFF，但随后您必须每天创建(即重新构建)新的索引分区--这应该是相同的工作量。

在PL/SQL过程中运行这样的任务(上面提到的任一项)并不是什么大不了的事情，它每天由数据库中的调度器作业执行。

Inital任务

-- Turn off Indexing for existing partitions
BEGIN
    FOR aPart IN (SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'TABLE1') LOOP
        EXECUTE IMMEDIATE 'ALTER TABLE TABLE1 MODIFY PARTITION '||aPart.PARTITION_NAME||' INDEXING OFF';
    END LOOP;   
END;
/

-- Create the index
CREATE INDEX TABLE1 ON idx (column1, column2) LOCAL INDEXING PARTIAL;

-- Enable indexing, otherwise new partitions will not get indexed.
ALTER TABLE TABLE1 MODIFY DEFAULT ATTRIBUTES INDEXING ON; 

-- Rebuild index on most recent partitions
DECLARE
   ts TIMESTAMP;
BEGIN
    FOR aPart IN (SELECT PARTITION_NAME, HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'TABLE1') LOOP
        EXECUTE IMMEDIATE 'BEGIN :ret := '||aPart.HIGH_VALUE||'; END;' USING OUT ts;
        IF ts > SYSTIMESTAMP - INTERVAL '5' DAY THEN
           EXECUTE IMMEDIATE 'ALTER INDEX idx REBUILD PARTITION '||aPart .PARTITION_NAME;
        END IF;
    END LOOP;   
END;
/

每日任务

DECLARE
   ts TIMESTAMP;
BEGIN
    FOR aPart IN (SELECT PARTITION_NAME, HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'TABLE1') LOOP
        EXECUTE IMMEDIATE 'BEGIN :ret := '||aPart.HIGH_VALUE||'; END;' USING OUT ts;
        IF ts < SYSTIMESTAMP - INTERVAL '5' DAY THEN
           EXECUTE IMMEDIATE 'ALTER TABLE TABLE1 MODIFY PARTITION '||aPart .PARTITION_NAME||' UNUSABLE LOCAL INDEXES';
        END IF;
    END LOOP;   
END;
/

但是，如果您不查询5天以上的数据，则此方法对性能没有任何影响，您只需节省一些磁盘空间。

Answer 2

我真的不明白这句话

需要付出太多的努力

您应该使用区间划分 (11g+)根据需要自动创建分区。

使用12c，您可以拥有分区表的部分索引。

您仍然会在每个表中创建一次本地INDEX，而不是每个分区一次。

与往常一样，您需要运行基准测试来查看哪种索引方法工作得最好。

Answer 3

我们有相同类型的表格，在我们的项目中有数十亿的记录。因此，我们在表中执行以下操作，以获得性能和最快的数据访问权限：

1. 创建分区基准日=> (与您相同)
2. 在Account_id上通过哈希创建子分区

你可以看到例子：

    PARTITION BY RANGE (DAY_KEY) INTERVAL (Numtodsinterval(1,'day')) SUBPARTITION BY HASH(ACCOUNTID)
     SUBPARTITIONS 64 (PARTITION P_FIRST_PART VALUES LESS THAN (TO_DATE('16-DEC-2015','DD-MON-YYYY')))