All,
We have a 1.4B row table that is hash partitioned by a key (ID). The key is
increasing monotonically and seems to be coming from a sequence. The loads
happen nightly and those are the only time the table is inserted to; during
the day time only SELECTs run against the table.
The table has 4 indexes on it, including one on the ID column. None of the
indexes are partitioned (!)
table t:
--------
ID NUMBER(16)
...
EFFDT DATE
..
Unique Index on ID column. Two other single column indexes on 2 number
columns and one single column index on a date column.
Queries that run in the day time typically access past several months of
data from the table by the ID key mostly. It is accessed via other columns
as well, but that is less than 20% of the time. There are about 10M rows
per month and we have 32 hash partitions. No INSERTS in the day time.
Since the load is happening nightly and there are only queries in the day
time, I am considering testing out range partitioning the table rather than
hash partitioning. It seems hash partitioning is recommended for keys based
on sequences to avoid index contention during inserts. However, with the
hash based approach the queries are scanning all partitions having to go
through all 1.4B rows for a few months of records. If I go with range
partitioning (one per month) on the ID key and if the queries access past
few months of data only, I feel we can see a substantial performance
improvement with queries only having to visit a fraction of the partitions
and rows.
I am also planning on creating a locally partitioned index on the ID column
and also partitioned indexes on the other 3 columns as well. Not sure if it
will increase the load time. One option for the indexes is that the locally
partitioned index will have the new partition created as needed and the
remaining 3 indexes dropped and rebuilt nightly. Will this be faster than
the index getting updated during the daily loads?
Can the listers share their thoughts on moving to range partition and
indexes during load.
Ram.
--
