RE: Philosophical question on primary keys

From: Kenneth Naim <kennaim_at_gmail.com>
Date: Fri, 7 Aug 2009 12:25:57 -0400
Message-ID: <01e201ca177b$bf70afd0$3e520f70$_at_com>

I'd go with adding the second index for all the reasons listed. If that query is taking up 92% of the io, I'd rather invest the effort on insert and still maintain the unique constraint of the message id column, in case a bug comes up where the same message_id's exists with different folder_id's. Does the message_id or folder_id column get updated, if it doesn't then only additional overhead is insert which should be minimal relative to the benefit.

The amount of LIO seems high for a query that just accesses a single row within the index. Are you sure there isn't something else going on?

Ken

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jay.Miller_at_tdameritrade.com Sent: Friday, August 07, 2009 12:09 PM
To: oracle-l_at_freelists.org
Subject: Philosophical question on primary keys

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

--

http://www.freelists.org/webpage/oracle-l Received on Fri Aug 07 2009 - 11:25:57 CDT