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

From: Powell, Mark <mark.powell2_at_hp.com>
Date: Thu, 2 Feb 2012 21:57:41 +0000
Message-ID: <7C4BF3B32B80CC44AE37D31B172415937DD6EA8F31_at_GVW1337EXC.americas.hpqcorp.net>

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Antony Raj Sent: Thursday, February 02, 2012 3:48 PM To: oracle-l_at_freelists.org
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

Received on Thu Feb 02 2012 - 15:57:41 CST