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.


On Fri, Aug 7, 2009 at 6:09 PM, <Jay.Miller@xxxxxxxxxxxxxxxx> 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
> 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;
> --

Toon Koppelaars
RuleGen BV

(co)Author: "Applied Mathematics for Database Professionals"

Other related posts: