RE: When should a table be partitioned?

From: DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM>
Date: Fri, 18 Jan 2002 10:21:37 -0800
Message-ID: <F001.003F3D9F.20020118091035@fatcity.com>

Cherie - I am relatively new to partitioned tables, but I have two criteria.

Partition the table for performance. If you have some significant queries that will be doing full table scans (not unusual for data warehouses), then may partition so the full table scans just scan a selected partition.
Partition for manageability. If the table is really large you could partition by the year or some such so that you can add a new year and remove an old year.

Another possibility is if you have many disks, even if the full table scan is going to hit the entire table, you can partition the table across several disks so that multiple disks are scanning simultaneously.

Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
Sent: Friday, January 18, 2002 7:21 AM
To: Multiple recipients of list ORACLE-L

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: DENNIS WILLIAMS
INET: DWILLIAMS_at_LIFETOUCH.COM

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------