Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Partitioning opinion

RE: Partitioning opinion

From: Leslie Tierstein <leslie.tierstein_at_visionchain.com>
Date: Tue, 31 Aug 2004 12:19:40 -0700
Message-ID: <4AE2782C2093B34784E67971035A8160966856@ehost011-2.exch011.intermedia.net>


In our experience, non-prefix local indexes work fine. They essentially give another access path to the data, in addition to via partition pruning. I think it shows up as a fast full scan, and performance is adequate for our reports. (Oracle 8i and 9i with 300 million+ rows per partition)

All of our indexes are local, but we have to do deletes/truncates on data in the partitions, and global indexes didn't give the performance we needed. (To be more blunt: Your delete when archiving will perform horribly.)

We have also placed the partitions in different tablespaces, so our backup strategy can take into account making the tablespaces read-only as their date range is passed.

Sorry this is anecdotal and I don't have the performance stats. They're on a client's system which I don't have access to today. But I have vivid memories.

Leslie
Leslie Tierstein
Senior Consultant
Vision Chain, Inc.
The first software to power the demand data network phone: 202-261-3549

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Harvinder Singh Sent: Tuesday, August 31, 2004 1:02 PM
To: oracle-l_at_freelists.org
Subject: Partitioning opinion

Hi,

We have a table having 14 columns and contain historical data and we are planning to implement partitioning so that we will keep only 12 months data online and purge the old partition every month. We will be using Range partitioning on Date column name Interv and have primary key on (id,Interv). Data is never updated and only deleted for archiving that we are planning to do as drop partition so for most of the time only inserts will go against this table and few Selects. For performance of select we have to add 2 more non-unique global indexes. So the schema will look like:
Primary key index on (id,Interv)
Does non-prefix local indexes be Ok or we should change the order of PK to (interv,id) to have prefix local index? Non-unique index on 3 columns
Non-unique index on 3 columns
Should we partition these global non-unique indexes or it does not matter since partitions will not be used by optimizer for pruning?

Thanks
--Harvinder



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

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
Received on Tue Aug 31 2004 - 14:18:26 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US