Concatenated Index Column Order - Does it really matters?

Hi,
 
I have a table T1 has 35 million rows.The following SQLs (Top 2) and many more 
runs every day.
 
UPDATE T1 SET AUDIT_FLAG='H' WHERE AUDIT_FLAG='C' AND 
JOB_ID=(SELECT MAX(JOB_ID) FROM T1 WHERE JOB_ID <> :1);
 
SELECT Z.EMPLID, Z.EMPL_RCD, Z.FIRST_NAME, Z.LAST_NAME, Z.MIDDLE_NAME,
Z.COVERAGE_BEGIN_DT, DECODE(Z.EMPL_STATUS, 'U', 'A', Z.EMPL_STATUS),
Z.UNION_CD, Z.PAYGROUP, Z.EXPECTED_RETURN_DT, Z.SEX, Z.DEPENDENT_BENEF,
  Z.RELATIONSHIP, Z.NATIONAL_ID, Z.BIRTHDATE, Z.STUDENT, Z.DISABLED,
Z.MAR_STATUS, Z.MAR_STATUS_DT, Z.ADDRESS1, Z.ADDRESS2, Z.CITY, Z.STATE,
  Z.POSTAL, Z.COUNTRY, Z.DEDUCTION_BEGIN_DT, Z.COVRG_CD,
Z.COVERAGE_ELECT,   Z.TERMINATION_DT, Z.BENEFIT_PLAN, Z.VENDOR_ID,
Z.END_DT, Z.ELIG_CONFIG1,   Z.BEGIN_DT, Z.PHONE 
FROM T1 Z 
WHERE Z.JOB_ID = ( SELECT MAX(Z1.JOB_ID) FROM T1 Z1 WHERE Z1.EMPLID = Z.EMPLID 
AND Z1.AUDIT_FLAG ='H') And Z.EMPLID = :1 
AND NOT EXISTS ( SELECT 'X' FROM T1 Z2 WHERE Z2.EMPLID = Z.EMPLID AND 
Z2.DEPENDENT_BENEF = Z.DEPENDENT_BENEF AND   Z2.AUDIT_FLAG = 'C' and   
AUDIT_ACTN in ('A','C') ) ORDER BY Z.EMPLID;
 
Unique Index Columns:
 
JOB_ID
EMPLID
PLAN_TYPE
DEPENDENT_BENEF
 
I am thinking of adding another index with the following column order to 
satisfy the top 2 SQLs.
JOB_ID,
AUDIT_FLAG,
EMPLID,
DEPENDENT_BENEF,
AUDIT_ACTN
 
 Column Name                    Null?      Type             NUM_DISTINCT
 ------------------------------ ---------- --------------- ------------
 JOB_ID                         NOT NULL   NUMBER(10,0)             394
 EMPLID                         NOT NULL   VARCHAR2(44)           31366
 PLAN_TYPE                      NOT NULL   VARCHAR2(8)                2
 DEPENDENT_BENEF                NOT NULL   VARCHAR2(8)               14
 AUDIT_ACTN                     NOT NULL   VARCHAR2(4)                3
 AUDIT_FLAG                     NOT NULL   VARCHAR2(4)                3
 
My question is that should I consider the "most selective column" as a leading 
column?
 
Thanks
--
http://www.freelists.org/webpage/oracle-l


Other related posts: