RE: Philosophical question on primary keys
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <Jay.Miller@xxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 9 Aug 2009 12:10:53 -0400
Others have given you a good balanced view of possibilities and cautions for
seeking optimization as you requested. I am going to depart completely from
that thread to wonder aloud if your system is being handed a box of needles,
tossing them a few per haystack, and then sorting through the haystacks to
find what you already had in hand just a bit before. Often, but clearly not
always, that is the case when attributes with names like "delete_ts",
"message_read_ts", and "expiration_ts" are born NULL and constitute what
could have been an identifiable queue of things to be handled. When new
records needing further "work" are mixed in with a history of "handled"
work, it can often be much less work to keep track the needles in the first
place. It is also often not much of a change to the schema and program suite
(though it may involve the similar tweak to many where clauses done
comprehensively.)
Typically it involves either building a table representing just the
"unhandled" rows or changing the sense of the flags and dates to be born
with a non-null default value that makes them candidates for work with an
ending status or date that becomes NULL when the work is done making them
disappear from the index that chooses them in the first place. And of course
you need to make sure your queries with appropriately set or collected
statistics will drive off the queue (table, or disappearing index(es)) in a
manner that will be an aid and comfort in the long haul.
Clearly your mileage may vary, but I thought since it is such a high
percentage of your i/o I ought to mention it. Even if this is not the issue,
you may want to examine whether providing a significant default value as an
alternative to NULL will lead to less row migration. That is something
dependent on the complex relationship of storage parameters and what
percentage of the length of the row change you experience overtime when
perhaps several date or timestamp types go from NULL to a definitive value.
It is the case more than zero percent of the time that defaulting to a
non-null value is worthwhile for that purpose alone.
Regards,
mwf
<snip>
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
Other related posts:
