Re: Hash to range partition

Hi Ram!

There are only 2 things I can think of:

1. Create new table, do insert /* +APPEND */  into new_tab select /*+ parallel(16) */ from old_table;
2. Use DBMS_REDEFINITION

Personally, I would use the first method, if possible.

Regards

On 7/30/20 6:16 PM, Ram Raman wrote:
> 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.
> --
>

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217

--
http://www.freelists.org/webpage/oracle-l