RE: Philosophical question on primary keys
Date: Tue, 11 Aug 2009 18:55:55 -0400
Message-ID: <CA405610095C8F42B6FEBFAAA09A7A2A0268EF56_at_prdkcwsemlmb05.prod-am.ameritrade.com>
I don't think a single table hash cluster would work as we have no idea how large this table will grow (original max size from the requirements was 5 million rows and it's already over 26 million and growing rapidly).
Changing secure_inbox_message an iot while intriguing might actually yield lower performance than just creating the index on the 2 columns whose data is being retrieved. This is because there is some row chaining (not just migration) in that table due to some huge columns so using an iot has the potential of increasing the lios.
But thanks for the suggestions. I had to think about it :).
Jay Miller
Sr. Oracle DBA
201.369.8355
From: Bobak, Mark [mailto:Mark.Bobak_at_proquest.com]
Sent: Friday, August 07, 2009 3:39 PM
To: Stefan Knecht; Miller, Jay
Cc: oracle-l_at_freelists.org
Subject: RE: Philosophical question on primary keys
Absolutely....I think the only thing used less than an IOT is a single-table hash cluster.... J
From: Stefan Knecht [mailto:knecht.stefan_at_gmail.com]
Sent: Friday, August 07, 2009 3:17 PM
To: jay.miller_at_tdameritrade.com
Cc: oracle-l_at_freelists.org; Bobak, Mark
Subject: Re: Philosophical question on primary keys
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 Tue Aug 11 2009 - 17:55:55 CDT