We are using an ETL tool to archive and prune a number of tables in our
OLTP database (moving data to our OLAP database).



The scenario is that we have a "process control table" that contains a
list of these pruning processes and the "last processed timestamp". This
methodology will not change as it is deeply instantiated in our
operational environment. Indexing is in place further indexes will have
no impact.



Basically a query runs and joins to this "process control table" to
compare a timestamp on the table due to be pruned to the "lasted
processed timestamp". This means that the two tables are going to create
a lengthy and costly join.



Just as an experiment I replaced the predicate in where clause in one of
the queries with a "hard timestamp" like '2007-10-01-12.00.00.000000'
and got an immediate result set from the query. The query plan topped
out at 90 with this test. Using the current method the plan topped out
at 11,000+.



I don't think it can be done, but I am looking for a way to get the
query to work more like it is comparing to a hard timestamp rather than
doing a table join to compare a timestamp in a column.



The issue is this and other queries run every 5, 10, or 15 minutes and I
want to reduce the impact on the system. Here is an example a query that
result in a costlier plan:



with pulldate (last_processed) as
(select TS_LAST_PROCESSED from AI.PROCESS_CONTROL

where AI.PROCESS_CONTROL.PROCESS = 'NBOHist1')
SELECT
AI.NBO_HISTORY.SUMMARY_ID,
AI.NBO_HISTORY.UI,
AI.NBO_HISTORY.LOCATION_ID,
AI.NBO_HISTORY.SERVICE_AREA_ID,
AI.NBO_HISTORY.CONTACT_CHANNEL_ID,
AI.NBO_HISTORY.NBO_CODE,
AI.NBO_HISTORY.MODIFIED_TS
FROM
AI.NBO_HISTORY ,pulldate
WHERE
AI.NBO_HISTORY.MODIFIED_TS>pulldate.last_processed

WITH UR;



Douglas Kostelnik

Senior Database Administrator/Architect




------------------------------------------------------------------------
The information transmitted is intended only for the person(s) or entity to which it is addressed
and may contain confidential and or privileged material and should be treated as a confidential
AAA Auto Club South communication. If the reader of this message is not the intended recipient, you
are hereby notified that your access is unauthorized, and any review, dissemination,distribution, or
copying of this message including any attachments is strictly prohibited.
========================================================================

DB2 & UDB email list listserv db2-l LazyDBA home page