RE: Hash Join and sequential reads

From: Mark W. Farnham <>
Date: Thu, 17 Jul 2008 14:08:52 -0400
Message-ID: <027601c8e838$2ca40b20$>

JL already made a reasonable conjectures about non-index driven db file sequential read waits from your query.  

I have a question about your query and data:  

Do you currently have any actual w_person_d.changed_on_dt values earlier than 01-JAN-1900?  

I believe your when case logic only makes use of a null w_response_d.resp_date by setting a date older than the aforementioned 01-JAN-1900 to null.

It's possible I read that wrong as formatted, but if I'm right I guess I'd update any very old dates to null (if that is your intention and any exist) and then dispense with the outer bit of the outer join. Without caring whether I annoy the anti-guessing alliance, I'd guess you're trying to pick up the highest date changed for a given integration_id. It hardly seems like a good idea to update every single row in w_person_d (especially for the rows for which a null was generated for the outer part of the id match and the value is updated to either itself [or NULL in the case of an old current date]).  

Of course I may have misread that case clause.  



From: [] On Behalf Of Debaditya Chatterjee
Sent: Wednesday, July 16, 2008 8:22 PM
<snip>Can anybody help me understand why there is a db file sequential read
wait event ?




SQL_ID 9crcdnsy4yq0v, child number 0

update /*+ bypass_ujvc */ (select x_recency_date, case when nvl(pd.CHANGED_ON_DT,to_date('01-JAN-1900','DD-MON-YYYY')) >= nvl(rd.RESP_DATE,to_date('01-JAN-1900','DD-MON-YYYY')) then pd.CHANGED_ON_DT

    when nvl(pd.CHANGED_ON_DT,to_date('01-JAN-1900','DD-MON-YYYY')) < nvl(rd.RESP_DATE,to_date('01-JAN-1900','DD-MON-YYYY')) then rd.RESP_DATE end
derived_recency_date from w_person_d pd,(select max(d.resp_date) resp_date,

          nvl(X_PR_CON_ID, X_PRSP_CON_ID) con_prsp_id from w_response_d d
        group by nvl(X_PR_CON_ID, X_PRSP_CON_ID)) rd where pd.integration_id
rd.con_prsp_id(+)) v1 set v1.x_recency_date = v1.derived_recency_date

<snip>SQL> SELECT opname, sofar, totalwork, start_time,

          time_remaining, elapsed_seconds, MESSAGE
     FROM v$session_longops
     WHERE SID = 1956

 ORDER BY start_time 2 3 4 5
  6 /


--------------- ---------- ---------- --------------------------
-------------- ---------------

Hash Join            22635      66525 15-JUL-2008 14:11:09
185278           95552

Hash Join: : 22635 out of 66525 Blocks done
Received on Thu Jul 17 2008 - 13:08:52 CDT

Original text of this message