| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: When should a table be partitioned?
Cheri,
Just wondered even when you have a table with data still you can partition
it or you have to create a new table with partitioning and load the data
from old to new one. Is it right or not?
-----Original Message-----
Sent: Tuesday, January 22, 2002 8:10 AM
To: Multiple recipients of list ORACLE-L
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,
>>> 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). The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system.
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). Received on Tue Jan 22 2002 - 13:00:51 CST
 |
 |