RE: Philosophical question on primary keys
- From: <Jay.Miller@xxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 11 Aug 2009 19:00:30 -0400
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@xxxxxxxx]
Sent: Sunday, August 09, 2009 12:11 PM
To: Miller, Jay; oracle-l@xxxxxxxxxxxxx
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;
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
