RE: Concatenated Index Column Order - Does it really matters?

No, you should make the "most selected" column the leading column of the 
multi-column index.  That is if 6 queries reference one or both of the two 
columns and the 3 queries that reference only one of the columns reference the 
same column then you normally want that column to be the leading column 
(providing the data is not badly skewed).

Ever case depends on the data distribution and the actual queries being ran but 
in general I suggest looking at what columns are referenced and which other 
indexes you would need to create if you choose a specific order.

The least selective column being first could be beneficial if index compression 
is used.  Again I find use of this feature has to be judged on a case by case 
basis.


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Antony Raj
Sent: Thursday, February 02, 2012 3:48 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: 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


--
http://www.freelists.org/webpage/oracle-l


Other related posts: