Query optimization [Oracle]

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?

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.

StackExchange Database Administrators Q#18817, answer score: 3