Re: When should a table be partitioned?

Cherie,
 I use the row count ,table size, and comonality method to determine which tables to partition. The majority of our data is DATE driven both in the record and the query so I partition the tables by date range using the date range most commonly queried. The limits of the OS and datafile size also drives the partitioning of some tables. If the data for a year will not fit into one datafile then it is a candidate for partitioning. I manually configure the partitioning on the 7.3.4 database and use views to access the data. I know that there are a lot of full table scans with views but it gets around the OS limits until we upgrade to 8i. On the 8i server I use the partitioning a lot and it makes the management of the data easier and has faster access time. The same query on the 8.1.7 with partitioning is 3 times faster than the 7.3.4 database. With theReceived: from CONNECT-MTA by galotter change to the new year it was easy to add
another partition on 8i but the 7.3.4 required a new table, datafile and view change.
ROR mª¿ªm
>>> 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 

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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ron Rogers
  INET: RROGERS_at_galottery.org

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).