查詢優化奧克力____○r一種C一世和Oracle

查詢：

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

問題：

如何優化此查詢的性能？

使用連接好還是做笛卡爾積更好？

這是一個修改後的查詢，它應該更快並產生相同的結果：

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;

推理：

• Doc_recon_header_w 僅被加入以獲取 message_date_time，因此它被移動到選擇列表，因此它只會出現在將要顯示的結果上。請注意，如果表中每個 fileid 有超過一行，或者記錄不存在標題記錄，這將不起作用，但我不希望出現任何一種情況。
• doc_recon_record 與 doc_duplicate_b 的連接僅用於檢查前者中是否存在記錄，這意味著如果重新排列表順序，則連接不需要是 aFULL JOIN並且可以是 a 。LEFT JOIN

引用自：https://dba.stackexchange.com/questions/18817