Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
2  
As for your 1st qustion: What do you expect to get in the other columns after grouping batch_id ? As for Question 2: are you aware that your doing a Cartesian multiple with STATUS_RESULT subquery and doc_recon_record_w combined with doc_recon_header_w ? –  A.B.Cade Jun 5 '12 at 8:51

5 Answers 5

up vote 3 down vote accepted

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.
share|improve this answer
    
Thanks, I have updated my query & question now. –  Rahul Thakur Jun 5 '12 at 13:29
    
Btw, there are multiple document_id records for the same batch_id. –  Rahul Thakur Jun 5 '12 at 13:38
    
Updated answer based on updated question. –  Leigh Riffel Jun 5 '12 at 17:41
    
Thanks a ton :) –  Rahul Thakur Jun 5 '12 at 18:10
    
I'm curious to know what the performance difference was. –  Leigh Riffel Jun 5 '12 at 18:12

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.

share|improve this answer
    
Thanks, I have updated my query & question now. –  Rahul Thakur Jun 5 '12 at 13:35

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.

share|improve this answer
1  
How do you know OP wants the max value of each column? –  A.B.Cade Jun 5 '12 at 9:14
1  
Do not encourage the use of imlicit joins wheic are a SQL antipattern. –  HLGEM Jun 5 '12 at 14:27

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.

share|improve this answer
    
It is showing duplicates from doc_duplicate_b & doc_recon_record_w table. The union you are doing there, it gets in from there. Are unions a better approach than joins? –  Rahul Thakur Jun 5 '12 at 14:24
    
Your query does look cleaner, but then again doing a union over 2 resultants? What if I have thousands of records? Also, I've updated the query to current state. –  Rahul Thakur Jun 5 '12 at 14:29
    
@DD_ - I see that you rephrased your query - at a quick glance I suspect that the nested decode can be split as well using a WHERE clause like recordstatus = '5' –  dezso Jun 5 '12 at 14:41
    
Yes I did, in the nested decode, I want a null when the record status is '5' ie 'SUCCESS' or else the comment from record table and if exists in the duplicate table then from there. –  Rahul Thakur Jun 6 '12 at 17:17

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.

share|improve this answer
1  
+1 for the excellent advice. You are describing Wolfgang Breitling's Tuning by Cardinality Feedback method. A little nitpicking though: this method is not an "explain plan", which is what you get after an explain plan statement and a subsequent "select * from table(dbms_xplan.display)". With dbms_xplan.display_cursor, you are looking at the actual plan and actual row cardinalities. –  Rob van Wijk Jun 5 '12 at 15:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.