Query optimization [Oracle]

Question

The Query:

select JOIN_RESULT.batch_id as BATCH_ID,
       JOIN_RESULT.unique_doc_id as DOCUMENT_ID,
       JOIN_RESULT.brand as BRAND,
       JOIN_RESULT.message_date_time as UPLOAD_DATE,
       decode(JOIN_RESULT.recordstatus, '5', 'SUCCESS', 'FAILED') as UPLOAD_STATUS,
       decode(JOIN_RESULT.recordstatus,
              '5',
              null,
              (decode(STATUS_RESULT.vendor_unique_doc_id,
                      JOIN_RESULT.unique_doc_id,
                      STATUS_RESULT.comments,
                      JOIN_RESULT.comments))) as FAILURE_REASON
  from (select d.vendor_unique_doc_id, d.comments
          from doc_duplicate_b d
         inner join doc_recon_record_w r on d.vendor_unique_doc_id =
                                                      r.unique_doc_id) STATUS_RESULT
  full join (select r.batch_id,
                    r.unique_doc_id,
                    r.brand,
                    h.message_date_time,
                    r.recordstatus,
                    r.comments
               from doc_recon_record_w r
              inner join doc_recon_header_w h on r.fileid =
                                                           h.fileid) JOIN_RESULT on STATUS_RESULT.vendor_unique_doc_id =
                                                                                    JOIN_RESULT.unique_doc_id
 order by JOIN_RESULT.batch_id

The Question:

How can I optimize the performance of this query?

Using joins good or is it better to do a Cartesian Product?

Answer 1

Here is a modified query that should be faster and produce the same results:

SELECT r.batch_id, r.unique_doc_id AS DOCUMENT_ID, r.brand,
   (SELECT h.message_date_time FROM doc_recon_header_w h WHERE r.fileid = h.fileid) 
      AS UPLOAD_DATE,
   decode(r.recordstatus, '5', 'SUCCESS', 'FAILED') AS UPLOAD_STATUS,
   decode(r.recordstatus, '5',  null,
      (decode(d.vendor_unique_doc_id, r.unique_doc_id, d.comments, r.comments))) 
      AS FAILURE_REASON
FROM doc_recon_record_w r
LEFT JOIN doc_duplicate_b d ON d.vendor_unique_doc_id = r.unique_doc_id
ORDER BY r.batch_id;

Reasoning:

• Doc_recon_header_w is only joined to get the message_date_time, so that is moved to the select list so it will occur only on the results that will be displayed. Note that this won't work if there is more than one row per fileid in the table or a header record does not exist for a record, but I wouldn't expect either of these to be the case.
• The join on doc_recon_record with doc_duplicate_b is done only to check for the existence of a record in the former, which means the join doesn't need to be a FULL JOIN and can be a LEFT JOIN if the table order is re-arranged.

Answer 2

1) You want ORDER BY rather than group by as you'd like the duplicates and not aggregation. Put ORDER BY BATCH_ID at the end of your query.

2) Your subselect doesn't seem to join with the rest of the query as you re-alias r. Consider dropping the subselect for a join and see if you can get it to make sense.

select r.batch_id as BATCH_ID,
       r.unique_doc_id as DOCUMENT_ID,
       r.brand as BRAND,
       h.message_date_time as UPLOAD_DATE,


       case
         when d.vendor_unique_doc_id = r.unique_doc_id then
          d.comments
         else
          decode(r.recordstatus, '5', 'SUCCESS', r.comments)
       end as UPLOAD_STATUS
  from (select d.vendor_unique_doc_id, d.comments
          from doc_duplicate_b d,
               doc_recon_record_w r
         where d.vendor_unique_doc_id = r.unique_doc_id) STATUS_RESULT,
       doc_recon_record_w r left join doc_duplicate_b d on d.vendor_unique_doc_id = r.unique_doc_id
       doc_recon_header_w h
 where r.fileid = h.fileid
 order by r.batch_id

or something like that, will pick up when there is a match, puts null in the join and thus should fire your case statement's default when no match.

Answer 3

Here is the updated SQL query.

select r.batch_id as BATCH_ID,
       max(r.unique_doc_id) as DOCUMENT_ID,
       max(r.brand) as BRAND,
       max(h.message_date_time) as UPLOAD_DATE,
       max(case
         when STATUS_RESULT.vendor_unique_doc_id = r.unique_doc_id then
          STATUS_RESULT.comments
         else
          decode(r.recordstatus, '5', 'SUCCESS', r.comments)
       end) as UPLOAD_STATUS
  from (select d.vendor_unique_doc_id, d.comments
          from doc_duplicate_b d,
               doc_recon_record_w r
         where d.vendor_unique_doc_id = r.unique_doc_id) STATUS_RESULT,
       doc_recon_record_w r,
       doc_recon_header_w h
 where r.fileid = h.fileid
      group by r.batch_id

for your second question its all depend on the indexing of the source table. You may use query explain to view performance.

Answer 4

Finally, I've come up with this:

select 
    r.batch_id as BATCH_ID,
    r.unique_doc_id as DOCUMENT_ID,
    r.brand as BRAND,
    h.message_date_time as UPLOAD_DATE,
    d.comments as UPLOAD_STATUS
from 
    doc_duplicate_b d
    inner join doc_recon_record_w r on r.unique_doc_id = d.vendor_unique_doc_id
    inner join doc_recon_header_w h on r.fileid = h.fileid
UNION
select 
    r.batch_id as BATCH_ID,
    r.unique_doc_id as DOCUMENT_ID,
    r.brand as BRAND,
    h.message_date_time as UPLOAD_DATE,
    decode(r.recordstatus,
        '5',
        'SUCCESS',
        r.comments
    ) UPLOAD_STATUS
from doc_recon_record_w r
    inner join doc_recon_header_w h on r.fileid = h.fileid

Please check it and compare its result set to your original one. Without your sample data I could not test it.

The idea was to split the CASE (which no longer exists up there) since you used exactly the same condition as in joining the two subqueries, and the content of STATUS_RESULT only appeared when the two sides of the OUTER JOIN were both non-null (which practically means an inner join). As this will probably yield much less rows than an outer join, and the other part of the CASE used only the other subquery (again with possibly much less rows), it can be faster then the original OUTER JOIN.

The rest is eliminating the subqueries.

Answer 5

When dealing with performance, the first place I'd check is the explain plan since it will detail how the Oracle engine is dealing with the query.

alter session set statistics_level=all;
set linesize 256;
set pagesize 0;
set serveroutput off;

--SQL STATEMENT

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

While a daunting read, a good rule of thumb is to look at the E-Rows (Number of Expected Rows) and A-Rows (Number of Actual Rows processed). When these are equal you are doing good - you will want to dig into the times when they are out of sync. This may be a case of stats our of sync or an idea of where a join needs to be improved.