Partial local index on partitioned Oracle table

indexoraclepartitioning

We have a big transactional table, partitioned on transaction date (day). Each day, several million records are added.
We need to do selects on this table, over a period of the last five days, and including the accountid.

In order to keep things a bit performant, I suggested to partition the table on day level (done), and create a local index (prefixed or non-prefixed) on accountid.
Now, one of the teammembers says it will not be possible, since they need to create an index on only the last five partitions of the table, and that would require too much effort.

AFAIK, the creation of the index needs to be done only once, and then daily, the index on the new partition only needs to be created. Besides that, indexes on partitions older than 5 days will never be used anymore, so there's nothing to do there.

What are your ideas on this? How to best solve this issue, with performance in mind?

Best Answer

You can create indexes only for certain partitions - of course this is possible only for LOCAL indexes.

The problem is you would have to set DEFAULT ATTRIBUTES INDEXING ON in order to get index created on new partitions. Index partitions older than 5 days you would have to delete (i.e. ALTER TABLE ... MODIFY PARTITION ... UNUSABLE LOCAL INDEXES; or ALTER INDEX ... MODIFY PARTITION ... UNUSABLE;) manually.

Of course you can set DEFAULT ATTRIBUTES INDEXING OFF but then you have to create (i.e. rebuild) the new index partition every day - which should be the same amount of effort.

It is no big deal to run such task (either of mentioned above) in PL/SQL procedure which is executed daily by a scheduler job in the database.

Inital task

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

Daily task

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

However if you don't query data older than 5 days then this approach does not have any effect on performance, you will just save some disc space.

Related Question