On Fri, Aug 7, 2009 at 8:04 PM, Bobak, Mark <Mark.Bobak@xxxxxxxxxxxx> wrote: Jay, Have you considered an IOT? -Mark Exactly. Or perhaps a single table hash cluster could work just as well -- can potentially reduce LIOs even more than an IOT ( depending on your queries and DML of course) --but definetely worth to look at and test with it... Stefan ========================= Stefan P Knecht CEO & Founder s@xxxxxxxx 10046 Consulting GmbH Schwarzackerstrasse 29 CH-8304 Wallisellen Switzerland Phone +41-(0)8400-10046 Cell +41 (0) 79 571 36 27 info@xxxxxxxx http://www.10046.ch ========================= On Fri, Aug 7, 2009 at 8:04 PM, Bobak, Mark <Mark.Bobak@xxxxxxxxxxxx> wrote: > Jay, > > Have you considered an IOT? > > -Mark > > -----Original Message----- > From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] > On Behalf Of Jay.Miller@xxxxxxxxxxxxxxxx > Sent: Friday, August 07, 2009 12:09 PM > To: oracle-l@xxxxxxxxxxxxx > 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; > > -- > //www.freelists.org/webpage/oracle-l > > > > > -- > //www.freelists.org/webpage/oracle-l > > >