Oracle FAQ Your Portal to the Oracle Knowledge Grid

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

RE: Partitioning Questions

From: Elliott, Patrick <>
Date: Wed, 7 Jun 2000 09:57:02 -0500
Message-Id: <>

The point is not to help the optimizer. The point is to reduce I/O. If you have ten partitions, then queries using the partition key only have to search a tenth of the data. This is true whether you partition the index or the table.

You are incorrect in stating that queries using indexes don't need partitioning. A partition is not a leaf block in a b-tree index. The better analogy would be to say that it is a branch or subset of the branches in the b-tree. I agree that you will not benefit from partitioning if the index you are using is a global index, but if you partition the index and table the same way, then you will benefit from the increased performance.

Multiple disk drives working on retrieving the data is not a performance benefit of partitioning. This is a parallel query feature which can be utilized with or without partitioning.

> -----Original Message-----
> From: ramon de los reyes []
> Sent: Wednesday, June 07, 2000 6:35 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Partitioning Questions
> when u do partioning at least when one partition fails then u can still
> select from the remaining partition. my .02 c worth.
> -----Original Message-----
> To: Multiple recipients of list ORACLE-L <>
> Date: Tuesday, June 06, 2000 3:32 AM
> >We are just now beginning to work with partitioned tables and views. I
> have
> >a few nagging questions. One of them is -- What is the true performance
> >benefits of partitioning? I am not talking about the maintenance
> benefits,
> >just the query performance benefits.
> >
> >For example, since our queries will be using the indexes, no reason to
> >partition the table right? I guess there would be some benefit on
> >partitioning the table so that multiple disk drives could be working to
> >retrieve the data. Any other performance benefits (not including the DDL
> >benefits)?
> >
> >And what about the index partitioning that we are going to do. It kind
> of
> >seems like we are doing Oracle's work for them. I mean, we are telling
> the
> >optimizer that this partition has these values, this other partition has
> >these other values, etc. Is this not just a leaf block in a b-tree index
> or
> >am I missing something? Why would this help the optimizer?
> >
> >Ok, you can blast away. I am sure that I am missing something obvious
> and
> >maybe someone can explain it to me. What are the true performance
> benefits
> >of table and index partitioning? (again, not the DDL benefits).
> >
> >Tom
> >
> >Tom Terrian
> >Oracle DBA
> >(937) 221-1647
> >
> >
> --
> Author: ramon de los reyes
> 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: (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
Received on Wed Jun 07 2000 - 09:57:02 CDT

Original text of this message