0

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?

0

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.

  • That's the thing, I don't understand the "would require too much effort" either... Interval Partitioning is created. Partial Indexes could be used, but not sure if there is an advantage of using a partial index over a local index on the whole partitioned table. – Borniet Sep 12 '18 at 9:18
0

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

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.