Oracle-L: RE: primary key, local partition index

From: Stephane Faroult <sfaroult_at_oriolecorp.com>
Date: Fri, 10 Jan 2003 01:09:56 -0800
Message-ID: <F001.0052C19F.20030110010956@fatcity.com>

('binary' encoding is not supported, stored as-is)
>Hi all...
>
>I'm messing around with partitioning, and trying to
>create a primary key
>index which is locally partition to match the
>underlying table. Table
>looks something like this:
>
>create table mytable (
>id number,
>last date)
>tablespace data
>storage (initial 64k next 64k pctincrease 0)
>partition by range (last)
>(partition lastq1_03 values less
>than(to_date('01-APR-2003','DD-MON-YYYY')),
> partition lastq2_03 values less
>than(to_date('01-JUL-2003','DD-MON-YYYY')),
> partition lastq3_03 values less
>than(to_date('01-OCT-2003','DD-MON-YYYY')),
> partition lastq4_03 values less
>than(to_date('01-JAN-2004','DD-MON-YYYY')))
>enable row movement;
>
>SQL> alter table mytable add primary key (id) using
>index
>tablespace "INDX" local enable;
>alter table mytable add primary key (id) using
>index
>tablespace "INDX" local enable
>*
>ERROR at line 1:
>ORA-14039: partitioning columns must form a subset
>of key columns of a
>UNIQUE index
>
>Basically what Oracle is saying is hey, your table
>is partitioned on last,
>and you want to create a local index on id, no can
>do. So how *DO* I do
>that, and for that matter, how do I manage with a
>foreign key constraint?
>
>I've looked at the docs, but I really need a good
>example. If anyone can
>point me in the right direction, I'd appreciate it.
>
>
>Thanks,
>Sean
>

Sean,

Don't forget that partitioning means scattering rows at different physical locations depending on some criterion. Fancy a moment you are Oracle and you get a query with the sole criterion

where id = some_value
To which index partition do you want to go to find your rowid? Assuming that your index is partitioned, you would have to search all partitions in turn (or in parallel) until you hit the good value (all of them before returning 'no data found'). Hardly ideal. Can you imagine the work involved when inserting a row into a table referencing this one? And with unique indexes it makes checking duplicates a nightmare (you would have to search as many trees as partitions) which is why Oracle forbids it - unless the partitioning key is part of the index keys and therefore we are certain we cannot possibly find a duplicate in another partition. In your case your PK should therefore be global. On pure performance grounds (as opposed to maintenance ones) the benefits of partitioning are : a) Partition pruning, which means that the partition key must be involved somewhere in your criteria b) Parallelism
If you are not on a massively parallel box, my feeling is, once again forgetting about maintenance considerations, you are better off with global indexes rather than artificially local ones if your partition key is no 'natural' part of your index.

Regards,

Stephane Faroult
Oriole

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroul
  INET: sfaroult_at_oriolecorp.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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 Fri Jan 10 2003 - 03:09:56 CST

RE: primary key, local partition index - prefixed/non-prefixed