RE: Concatenated Index Column Order - Does it really matters?
Date: Thu, 2 Feb 2012 21:57:41 +0000
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.
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?
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:
I am thinking of adding another index with the following column order to satisfy the top 2 SQLs. JOB_ID,
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 Received on Thu Feb 02 2012 - 15:57:41 CST