where 條件下的列序列是否對複合鍵的序列很重要？在 Oracle 數據庫中

在這裡，在exists clause包含所有主鍵列的程式碼中where，我想知道其中這些列的順序對查詢性能很重要。

 update temp_fd_dtl_svj a
 set a.LAST_INT_PAID_DT = ( select max(b.TRAN_DT) from temp_fd_int_svj b
                             where a.COMP_CD = b.comp_cd and a.BRANCH_CD = b.BRANCH_CD and a.ACCT_CD = b.ACCT_CD
                               and a.ACCT_TYPE = b.ACCT_TYPE and a.fd_no = b.FD_NO and b.Tran_type in ('I')
                           )
 , a.LAST_PROV_DT = ( select max(b.TRAN_DT) from temp_fd_int_svj b
                             where a.COMP_CD = b.comp_cd and a.BRANCH_CD = b.BRANCH_CD and a.ACCT_CD = b.ACCT_CD
                               and a.ACCT_TYPE = b.ACCT_TYPE and a.fd_no = b.FD_NO and b.Tran_type in ('P')
                           )
  , a.LAST_TDS_DT = ( select max(b.TRAN_DT) from temp_fd_int_svj b
                             where a.COMP_CD = b.comp_cd and a.BRANCH_CD = b.BRANCH_CD and a.ACCT_CD = b.ACCT_CD
                               and a.ACCT_TYPE = b.ACCT_TYPE and a.fd_no = b.FD_NO and b.Tran_type in ('T')
                           )
 where exists (select *
               from temp_fd_int_svj c
              where a.acct_cd = c.acct_CD
                and a.comp_cd = c.comp_cd
                and a.BRANCH_CD = c.BRANCH_CD
                AND a.ACCT_TYPE = c.ACCT_TYPE
                and a.fd_no = c.FD_NO
              )
 ;

不，沒關係。確保：

1. 兩個表中的數據類型相同
2. 所有列都被索引索引
3. 索引統計資訊不會過時。

SELECT stale_stats FROM user_ind_statistics WHERE index_name = upper(’&index_name’);

/* 格式化於 20/3/2019 03:00:33 pm (QP5 v5.326) */ UPDATE temp_fd_dtl_svj a SET a.last_int_paid_dt = (SELECT MAX (b.tran_dt) FROM temp_fd_int_svj b WHERE a.comp_cd = b.comp_cd AND a.branch_cd = b.branch_cd AND a.acct_cd = b.acct_cd AND a.acct_type = b.acct_type AND a.fd_no = b.fd_no AND b.tran_type IN (‘I’)), a.last_prov_dt = (SELECT MAX (b.tran_dt) FROM temp_fd_int_svj b 其中 a.comp_cd = b.comp_cd AND a.branch_cd = b.branch_cd AND a.acct_cd = b。acct_cd AND a.acct_type = b.acct_type AND a.fd_no = b.fd_no AND b.tran_type IN (‘P’)), a.last_tds_dt = (SELECT MAX (b.tran_dt) FROM temp_fd_int_svj b WHERE a.comp_cd = b .comp_cd AND a.branch_cd = b.branch_cd AND a.acct_cd = b.acct_cd AND a.acct_type = b.acct_type AND a.fd_no = b.fd_no AND b.tran_type IN (‘T’)) 存在的地方（選擇 1 /tran_type IN (‘P’)), a.last_tds_dt = (SELECT MAX (b.tran_dt) FROM temp_fd_int_svj b WHERE a.comp_cd = b.comp_cd AND a.branch_cd = b.branch_cd AND a.acct_cd = b.acct_cd AND a .acct_type = b.acct_type AND a.fd_no = b.fd_no AND b.tran_type IN (‘T’)) WHERE EXISTS (SELECT 1 /tran_type IN (‘P’)), a.last_tds_dt = (SELECT MAX (b.tran_dt) FROM temp_fd_int_svj b WHERE a.comp_cd = b.comp_cd AND a.branch_cd = b.branch_cd AND a.acct_cd = b.acct_cd AND a .acct_type = b.acct_type AND a.fd_no = b.fd_no AND b.tran_type IN (‘T’)) WHERE EXISTS (SELECT 1 /acct_cd AND a.acct_type = b.acct_type AND a.fd_no = b.fd_no AND b.tran_type IN (‘T’)) WHERE EXISTS (SELECT 1 /acct_cd AND a.acct_type = b.acct_type AND a.fd_no = b.fd_no AND b.tran_type IN (‘T’)) WHERE EXISTS (SELECT 1 /不要選擇所有列，1 是正確的/來自 temp_fd_int_svj c 其中 a.acct_cd = c.acct_cd AND a.comp_cd = c.comp_cd AND a.branch_cd = c.branch_cd AND a.acct_type = c.acct_type AND a.fd_no = c.fd_no);

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