Re: partitioning question - anybody know?

A copy of this was sent to kal121_at_yahoo.com (if that email address didn't require changing) On Fri, 17 Dec 1999 21:28:43 GMT, you wrote:

>In order for a partition to be accessed by a SQL query, does the query
>have to reference the partition key directly, or does it not matter?
>
>For example, if I define my_table as:
>
>id number
>name varchar2(40)
>
>and I partition on id, would the following querys *both* access an
>individual partition, or only the first one?
>
>SELECT * from my_table
>WHERE id = 1;
>
>SELECT * from my_table
>WHERE name = 'Joe';
>or would this second query access *all* partitions, because I am not
>referencing the partition key directly?
>
>I wouldn't think it would matter which columns are referenced in the
>WHERE clause, but I remember reading somewhere that it does...
>

the first query would only access 1 partition. It can determine from the query that only one parition can possibly contain the answer.

The second query may access >1 partition. No partition elimination can be done since there is no correlation between NAME and partitions. If name is not indexed -- and we full scan, we must full scan all partitions sinces name = 'Joe' can be in any and all partitions.

OTOH, if name has an index on it -- we'll only access data in data (as opposed to index) partitions that contain Joe. This is not partition elimination but rather an index that tells us what data we need to read.

>Thanks
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Dec 17 1999 - 20:00:38 CST