Partial local index on partitioned Oracle table

Question

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?

Answer 1

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.

Answer 2

I really don't understand the statement

would require too much effort

You should be using Interval Partitioning (11g+) to automagically create your partitions as you need them.

With 12c, you can have Partial Indexes for Partitioned Tables.

You would still create a local INDEX once per table, not once per partition.

As always, you will need to run benchmarks to see which indexing method works best.

Answer 3

We have same type of table with billions records in our Project. So we do followings actions in our tables for have performance and fastest access to data :

1. Create Partition Base Day => (Same as you)
2. Create Subpartition by Hash on Account_id

you can see example :

    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')))

Answer 4

Apart from Wernfried Domscheit's answer (don't know his version), which is basically the correct direction, I would go as follows:

1. Change default attribute of table to disable automatic creation of indexes on partitions:

alter table mytab modify default attributes indexing off;

It sets the table's default indexing to off. No existing index will be affected. It relates to partial indexes only and every new local index with "PARTIAL INDEXING" will not be created on the local partitions.

2. Create the index with INDEXING PARTIAL:

create index mylocal_partidx on mytab(accountid) local indexing partial;

Since index is partial and default attribute on table is NO, no local partitions are created.

3. Create required index partitions by simply swith indexing on for selected partitions. Each command creates the partial marked indexes on the partition:

   alter table mytab modify partition P100 indexing ON;
   alter table mytab modify partition P101 indexing ON;
   alter table ...
   

Now, you have only the relevant n partitions indexed. All indexes not marked partial are fully created.

To drop unwanted partitions, do similar like Wernfrieds version, but with different command:

    alter table mytab modify partition P001 indexing OFF;

It will drop only indexes marked "partial" from this partition. All other indexes to be kept, must not be set to "INDEXING PARTIAL".