Jay, 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@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 > ='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; > > -- > //www.freelists.org/webpage/oracle-l > > > -- Toon Koppelaars RuleGen BV +31-615907269 Toon.Koppelaars@xxxxxxxxxxx www.RuleGen.com TheHelsinkiDeclaration.blogspot.com (co)Author: "Applied Mathematics for Database Professionals" www.RuleGen.com/pls/apex/f?p=14265:13