RE: When should a table be partitioned?

Cherie,
We used the data volume and performance criterion while deciding to partition our tables. One of the major tables had grown over 100Mil rows and had 4 indexes. All tables had 4 digit YEAR field and most queries used YEAR in them, so it was a no-brainer. A few important queries were modified to use YEAR to facilitate partition pruning. So, after partitioning all these tables by YEAR, the performance was much improved. Archival criterion was also a driver, initially. But after the clients saw the results (good performance), they had other ideas. Now we are holding on to 12 years of data instead of 10 years. Looks like we are heading towards 15 years of historical data. And we have added 2 more indexes to that major table. Data loads are once a month.

HTH,

• Kirti

>>> Cherie_Machler_at_gelco.com 01/18/02 08:20AM >>>

We have a number of partitioned tables in a couple of existing data warehouses.
We are working on the design for a new warehouse and need to decide which
tables should be partitioned.

For you folks that have partitioned tables, how do you decide which tables
to
be partition? Some tables with very large row counts are obvious candidates.
If you go off of row counts solely, what is the cut-off point for where you
should start
partitioning? Is there a rule-of-thumb?

I'm having difficulty with the not-as-huge, not-as-obvious candidates.

What other criteria do you use besides row-count? Perhaps archival requirements?

I guess it would depend on what you are using the partitioning to achieve.
Partition
exclusion for read performance improvement or for culling off old data,
etc.

Any shared insights for where to draw the line on partitioning candidates
would
be greatly appreciated.

Thanks,

Cherie Machler

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com 
-- 
Author: 
  INET: Cherie_Machler_at_gelco.com 


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deshpande, Kirti
  INET: kirti.deshpande_at_verizon.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).