Concatenated Index Column Order - Does it really matters?

From: Antony Raj <ca_raj_at_yahoo.com>
Date: Thu, 2 Feb 2012 12:48:29 -0800 (PST)
Message-ID: <1328215709.95796.YahooMailNeo_at_web36803.mail.mud.yahoo.com>

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

Received on Thu Feb 02 2012 - 14:48:29 CST