RE: Philosophical question on primary keys

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Sun, 9 Aug 2009 12:10:53 -0400
Message-ID: <35BB6F9581F44AA3AADCB023AB016A54_at_rsiz.com>

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;

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




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

Received on Sun Aug 09 2009 - 11:10:53 CDT