Re: Philosophical question on primary keys

Are you aware of the feature that a non-unique index can be used to enforce a primary key, whose columns are a prefix part of the indexed columns?

Say table T, with columns A, B and C.
Design-wise let A be the primary key.
You can create an index (non-unique*!) on columns A,B and then add a primary key of A only.
It will use the non-unique index to enforce the key.

(*!) Now here's the *strange* thing, afaik it is required that this index be non-unique. Don't ask me why. Beats the heck out of me, since a proper subset of the indexed columns constitute a key of the table. If you ask me someone at Oracle wasn't paying attention when introducing this feature.

Toon

On Fri, Aug 7, 2009 at 6:09 PM, <Jay.Miller_at_tdameritrade.com> wrote:

> We have a table with a primary key of message_id. This table is
> occasionally queried on that column alone but most often on both the id
> and a folder_id column (also numeric) with no other data being returned.
>
> There are no foreign keys pointing to this primary key.
>
> I'm trying to squeeze every last bit of performance out of one piece of
> SQL that accounts 92% of all database i/o.
>
> Using an index on message_id,folder_id reduces LIOs from 36 to 32 for a
> typical query as opposed to using the primary key index on message_id.
>
> The question is whether to create a new index or change the primary key
> to include both columns.
>
> Arguments against modifying primary key:
> The primary key is just message_id, adding folder_id doesn't make it any
> more unique. Also folder_id currently only exists in this table so if
> for some unforeseen reason we someday need to point a foreign key to
> this table this might cause a problem. No one thinks this will ever be
> necessary but who knows what might happen in the future.
>
> Arguments for modifying primary key:
> One less index on the table means less overhead for inserts/updates.
> One less index is less storage used. Also, I'm having trouble getting
> the optimizer to use the second index in our test environment without
> resorting to an index hint which I prefer to avoid.
>
>
>
> Comments welcome. Thanks!
>
>
> Here's the SQL in case anyone wants to take a look at it:
> SELECT i.message_priority_cd, COUNT(*) AS count_label
> FROM CLIENTMSG_ADMIN.message_instance i,
> CLIENTMSG_ADMIN.message_transmission m,
> CLIENTMSG_ADMIN.secure_inbox_message secure_inbox
> WHERE i.message_id = secure_inbox.message_id
> AND m.message_id = i.message_id
> AND m.channel_cd = 'SECURE_INBOX'
> AND i.account_nbr = :1
> AND (m.delivery_status_cd = 'PENDING' OR m.delivery_status_cd
> ='DELIVERED')
> AND m.message_read_ts IS NULL
> AND m.delete_ts IS NULL
> AND (i.expiration_ts > current_timestamp OR i.expiration_ts IS NULL)
> AND secure_inbox.folder_id <> 3
> AND secure_inbox.folder_id <> 2
> GROUP BY i.message_priority_cd;
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Toon Koppelaars
RuleGen BV
+31-615907269
Toon.Koppelaars_at_RuleGen.com
www.RuleGen.com
TheHelsinkiDeclaration.blogspot.com

(co)Author: "Applied Mathematics for Database Professionals"
www.RuleGen.com/pls/apex/f?p=14265:13

--
http://www.freelists.org/webpage/oracle-l