Re: Philosophical question on primary keys

From: Stefan Knecht <knecht.stefan_at_gmail.com>
Date: Fri, 7 Aug 2009 21:16:35 +0200
Message-ID: <486b2b610908071216j6715558clfb32012a4c092a63_at_mail.gmail.com>

On Fri, Aug 7, 2009 at 8:04 PM, Bobak, Mark <Mark.Bobak_at_proquest.com> 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_at_10046.ch

10046 Consulting GmbH
Schwarzackerstrasse 29
CH-8304 Wallisellen
Switzerland

Phone +41-(0)8400-10046
Cell +41 (0) 79 571 36 27
info_at_10046.ch
http://www.10046.ch

---

On Fri, Aug 7, 2009 at 8:04 PM, Bobak, Mark <Mark.Bobak_at_proquest.com> wrote:

> Jay,
>
> Have you considered an IOT?
>
> -Mark
>
> -----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
>
>
>

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

Received on Fri Aug 07 2009 - 14:16:35 CDT