RE: Hash to range partition

  • From: "Mark W. Farnham" <mwf@xxxxxxxx>
  • To: <gogala.mladen@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 30 Jul 2020 20:12:05 -0400

Well, let's see: 

Is there already a date column on the table?

Is it the "born-on" date?

So picking the 90+ percent edge case of business data that has "yes" to both of 
those, is it also true that there is an offset date, and "age" if you will 
after which such rows can never again be modified?

Once again I'll go with the edge case (perhaps only 80% of the earlier 90+%) 
that is true.

Then:

Create a very dense table structure (0 percent free) and find the shortest key 
that is tied for query parameter.

Partition by that date column, whether by hour, day, week, month depending on 
what makes sense for queries.

Insert into those partitions using the filter for just the rows for each 
partition and order by that column (or columns) for all the periods that have 
not yet become quiescent.

Then set up a the rest of the partitions at a percent free that will 
accommodate likely updates for the "young" partitions and copy them in.

Now, your monthly maintenance (or whatever period you are partitioned on) 
becomes copy out the "became quiescent" partitions to percent free 0 storage 
with the same order by, and swap them back in.

Betcha everything runs faster for just a wee bit of trouble that is NOT an 
extra work forever treadmill.

Good luck,

mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Mladen Gogala
Sent: Thursday, July 30, 2020 6:59 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: 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

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




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


Other related posts: