RE: Philosophical question on primary keys

From: <Jay.Miller_at_tdameritrade.com>
Date: Tue, 11 Aug 2009 19:00:30 -0400
Message-ID: <CA405610095C8F42B6FEBFAAA09A7A2A0268EF57_at_prdkcwsemlmb05.prod-am.ameritrade.com>



Excellent observations and we are in fact working on default values for all of those (it has even greater benefit for some idiotic batch jobs). That is a longer term project though as it requires both code changes and convincing a department to accept a newer version of an application.

An index change has a much shorter testing cycle which is why I was looking at this as a temporary stopgap.

This application was written by some java consultants who had no idea about programming for an Oracle database and I was only called in the week before it was supposed to go live. I could tell you some horror stories about this app...

Jay Miller
Sr. Oracle DBA
201.369.8355

-----Original Message-----
From: Mark W. Farnham [mailto:mwf_at_rsiz.com] Sent: Sunday, August 09, 2009 12:11 PM
To: Miller, Jay; oracle-l_at_freelists.org Subject: RE: Philosophical question on primary keys

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 Tue Aug 11 2009 - 18:00:30 CDT

Original text of this message