nkwzxbz
asked on
creating an index question
I have a SQL as following:
SELECT
T12.CONFLICT_ID,
T12.LAST_UPD,
T12.CREATED,
T12.LAST_UPD_BY,
T12.CREATED_BY,
T12.MODIFICATION_NUM,
T12.ROW_ID,
T12.ROW_ID,
T23.ADL_STATUS,
T19.ROW_ID,
T19.NAME,
T23.CUST_STAT_CD,
T23.ACCNT_TYPE_CD,
T23.CLIENT_FLG,
T23.CMPT_FLG,
T23.CG_CONS_END_OFFSET,
T23.CG_CONS_STRTOFFSET,
T23.BASE_CURCY_CD,
T23.HIST_SLS_CURCY_CD,
T23.DUNS_NUM,
T23.PROD_DIST_CD,
T23.DIVISION,
T5.ATTRIB_16,
T23.EXCH_DT,
T23.FACILITY_FLG,
T23.FRGHT_TERMS_CD,
T23.GOOD_STANDING_FLG,
T23.INCL_FLG,
T23.INTEGRATION_ID,
T23.INT_ORG_FLG,
T23.INVSTR_FLG,
T23.LEGAL_FORM_CD,
T23.LOC,
T23.LOCATION_LEVEL,
T23.MAIN_FAX_PH_NUM,
T23.MAIN_PH_NUM,
T23.MASTER_OU_ID,
T4.NAME,
T23.NAME,
T23.PO_PAY_CURCY_CD,
T23.PAR_OU_ID,
T10.NAME,
T10.ACCNT_TYPE_CD,
T23.PAR_DUNS_NUM,
T23.PRTNR_FLG,
T12.NAME,
T13.NAME,
T23.CURR_PRI_LST_ID,
T23.PR_ADDR_ID,
T23.PR_REP_DNRM_FLG,
T23.PR_REP_MANL_FLG,
T23.PR_REP_SYS_FLG,
T23.PR_REP_ASGN_TYPE,
T23.PR_BL_ADDR_ID,
T23.PR_BL_PER_ID,
T23.PR_MKT_SEG_ID,
T23.PR_CON_ID,
T23.PR_CRDT_AREA_ID,
T23.PR_INDUST_ID,
T23.BU_ID,
T23.PR_POSTN_ID,
T23.PR_MED_PROC_ID,
T23.PR_PRFL_ID,
T23.PR_SRV_AGREE_ID,
T23.PR_SHIP_ADDR_ID,
T23.PR_SHIP_PER_ID,
T23.PR_SPEC_ID,
T23.PR_PRTNR_OU_ID,
T23.PR_SYN_ID,
T23.PR_TERR_ID,
T23.PR_OU_TYPE_ID,
T23.CG_PRMO_END_OFFSET,
T23.REFERENCE_CUST_FLG,
T23.REGION,
T23.REL_ID,
T23.REVENUE_CLASS_CD,
T23.ROW_ID,
T23.RULE_ATTRIB1,
T23.RULE_ATTRIB2,
T18.OWN_INST_ID,
T18.INTEGRATION_ID,
T23.SRV_PROVDR_FLG,
T23.CG_SHIP_END_OFFSET,
T23.CG_SHIP_STRTOFFSET,
T23.SURVEY_TYPE_CD,
T15.NAME,
T23.TAX_LIST_ID,
T20.TOT_NUM_LIVES,
T23.PTNTL_SLS_CURCY_CD,
T23.OU_TYPE_CD,
T5.ATTRIB_09,
T5.ATTRIB_53,
T5.ATTRIB_41,
T5.ATTRIB_52,
T5.ATTRIB_34,
T14.ATTRIB_07,
T14.ATTRIB_34,
T5.ATTRIB_40,
T5.ATTRIB_47,
T14.ATTRIB_35,
T8.X_BILL_CYCL_FLAG,
T23.MISC_FLG,
T14.ATTRIB_03,
T23.X_SOURCE,
T5.ATTRIB_36,
T5.ATTRIB_51,
T5.ATTRIB_50,
T5.ATTRIB_49,
T5.ATTRIB_48,
T2.LOGIN,
T5.ATTRIB_44,
T5.ATTRIB_45,
T5.ATTRIB_42,
T5.ATTRIB_43,
T14.ATTRIB_36,
T14.ATTRIB_37,
T8.PAYMT_METH_CD,
T5.ATTRIB_46,
T23.X_SWITCH_BUREAU,
T23.X_SWITCH_BUREAU_CODE,
T23.SVC_CVRG_STAT_CD,
T14.ATTRIB_06,
T23.FRGHT_TERMS_INFO,
T23.OU_NUM_1,
T16.FST_NAME,
T16.LAST_NAME,
T7.ROW_STATUS,
T11.ROW_ID,
T22.BRICK_ID,
T11.COUNTRY,
T11.COUNTY,
T11.ZIPCODE,
T11.STATE,
T11.ADDR,
T11.CITY,
T3.CITY,
T3.COUNTRY,
T3.ZIPCODE,
T3.STATE,
T3.ADDR_LINE_2,
T3.ADDR,
T9.PAR_BU_ID,
T23.ROW_ID,
T23.MODIFICATION_NUM,
T23.CREATED_BY,
T23.LAST_UPD_BY,
T23.CREATED,
T23.LAST_UPD,
T23.CONFLICT_ID,
T23.PAR_ROW_ID,
T20.ROW_ID,
T20.MODIFICATION_NUM,
T20.CREATED_BY,
T20.LAST_UPD_BY,
T20.CREATED,
T20.LAST_UPD,
T20.CONFLICT_ID,
T20.PAR_ROW_ID,
T14.ROW_ID,
T14.MODIFICATION_NUM,
T14.CREATED_BY,
T14.LAST_UPD_BY,
T14.CREATED,
T14.LAST_UPD,
T14.CONFLICT_ID,
T14.PAR_ROW_ID,
T5.ROW_ID,
T5.MODIFICATION_NUM,
T5.CREATED_BY,
T5.LAST_UPD_BY,
T5.CREATED,
T5.LAST_UPD,
T5.CONFLICT_ID,
T5.PAR_ROW_ID,
T18.ROW_ID,
T18.MODIFICATION_NUM,
T18.CREATED_BY,
T18.LAST_UPD_BY,
T18.CREATED,
T18.LAST_UPD,
T18.CONFLICT_ID,
T18.PAR_ROW_ID,
T7.ROW_ID,
T6.ROW_ID,
T22.ROW_ID,
T11.ROW_ID,
T3.ROW_ID,
T1.ROW_ID,
T17.ROW_ID
FROM
SIEBEL.S_ORG_BU T1,
SIEBEL.S_USER T2,
SIEBEL.S_ADDR_PER T3,
SIEBEL.S_ORG_EXT T4,
SIEBEL.S_ORG_EXT_X T5,
SIEBEL.S_PARTY T6,
SIEBEL.S_ACCNT_POSTN T7,
SIEBEL.S_INV_PROF T8,
SIEBEL.S_ORG_EXT T9,
SIEBEL.S_ORG_EXT T10,
SIEBEL.S_ADDR_PER T11,
SIEBEL.S_PARTY T12,
SIEBEL.S_PRI_LST T13,
SIEBEL.S_ORG_INT_X T14,
SIEBEL.S_PRI_LST T15,
SIEBEL.S_CONTACT T16,
SIEBEL.S_PARTY T17,
SIEBEL.S_ORG_EXT_SS T18,
SIEBEL.S_ORG_PROMOPRFL T19,
SIEBEL.S_ORG_EXT_LSX T20,
SIEBEL.S_POSTN T21,
SIEBEL.S_CON_ADDR T22,
SIEBEL.S_ORG_EXT T23
WHERE
T12.CREATED_BY = T2.ROW_ID (+) AND
T23.MASTER_OU_ID = T4.PAR_ROW_ID AND
T23.PAR_OU_ID = T10.PAR_ROW_ID AND
T23.ROW_ID = T19.ACCNT_ID (+) AND T23.PR_PRFL_ID = T19.PROMO_PROFILE_ID (+) AND
T23.CURR_PRI_LST_ID = T13.ROW_ID (+) AND
T23.TAX_LIST_ID = T15.ROW_ID (+) AND
T12.ROW_ID = T8.ACCNT_ID (+) AND
T12.ROW_ID = T23.PAR_ROW_ID AND
T12.ROW_ID = T20.PAR_ROW_ID (+) AND
T12.ROW_ID = T14.PAR_ROW_ID (+) AND
T12.ROW_ID = T5.PAR_ROW_ID (+) AND
T12.ROW_ID = T18.PAR_ROW_ID (+) AND
T23.PR_POSTN_ID = T7.POSITION_ID AND T23.ROW_ID = T7.OU_EXT_ID AND
T7.POSITION_ID = T6.ROW_ID AND
T7.POSITION_ID = T21.PAR_ROW_ID (+) AND
T21.PR_EMP_ID = T16.PAR_ROW_ID (+) AND
T23.PR_ADDR_ID = T22.ADDR_PER_ID (+) AND T23.ROW_ID = T22.ACCNT_ID (+) AND
T23.PR_ADDR_ID = T11.ROW_ID (+) AND
T23.PR_ADDR_ID = T3.ROW_ID (+) AND
T1.BU_ID = :1 AND T23.ROW_ID = T1.ORG_ID AND
T1.BU_ID = T17.ROW_ID AND
T1.BU_ID = T9.PAR_ROW_ID (+) AND
((T23.ACCNT_TYPE_CD != 'Customer') AND
((T23.INT_ORG_FLG != 'Y' OR T1.ORG_PRTNR_FLG != 'N') AND T23.ACCNT_FLG != 'N')) AND
(T10.NAME LIKE 'A308%')
I got "table access full " for T23 (optimizer mode = CHOOSE), then I tried to create an index as following:
DROP INDEX SIEBEL.TEMP7;
CREATE INDEX SIEBEL.TEMP7 ON SIEBEL.S_ORG_EXT
(ACCNT_TYPE_CD, INT_ORG_FLG, ACCNT_FLG)
LOGGING
TABLESPACE INDX_SIEBEL
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
but still got "table access full", not use this index. If I changed to SQL to ( using = "Customer" instead of != "Customer")
...
T1.BU_ID = T9.PAR_ROW_ID (+) AND
((T23.ACCNT_TYPE_CD = 'Customer') AND
((T23.INT_ORG_FLG != 'Y' OR T1.ORG_PRTNR_FLG != 'N') AND T23.ACCNT_FLG != 'N')) AND
(T10.NAME LIKE 'A308%')
the Index will be used. I don't know why, how to create an index for != 'Customer'
Thanks,
Lily
SELECT
T12.CONFLICT_ID,
T12.LAST_UPD,
T12.CREATED,
T12.LAST_UPD_BY,
T12.CREATED_BY,
T12.MODIFICATION_NUM,
T12.ROW_ID,
T12.ROW_ID,
T23.ADL_STATUS,
T19.ROW_ID,
T19.NAME,
T23.CUST_STAT_CD,
T23.ACCNT_TYPE_CD,
T23.CLIENT_FLG,
T23.CMPT_FLG,
T23.CG_CONS_END_OFFSET,
T23.CG_CONS_STRTOFFSET,
T23.BASE_CURCY_CD,
T23.HIST_SLS_CURCY_CD,
T23.DUNS_NUM,
T23.PROD_DIST_CD,
T23.DIVISION,
T5.ATTRIB_16,
T23.EXCH_DT,
T23.FACILITY_FLG,
T23.FRGHT_TERMS_CD,
T23.GOOD_STANDING_FLG,
T23.INCL_FLG,
T23.INTEGRATION_ID,
T23.INT_ORG_FLG,
T23.INVSTR_FLG,
T23.LEGAL_FORM_CD,
T23.LOC,
T23.LOCATION_LEVEL,
T23.MAIN_FAX_PH_NUM,
T23.MAIN_PH_NUM,
T23.MASTER_OU_ID,
T4.NAME,
T23.NAME,
T23.PO_PAY_CURCY_CD,
T23.PAR_OU_ID,
T10.NAME,
T10.ACCNT_TYPE_CD,
T23.PAR_DUNS_NUM,
T23.PRTNR_FLG,
T12.NAME,
T13.NAME,
T23.CURR_PRI_LST_ID,
T23.PR_ADDR_ID,
T23.PR_REP_DNRM_FLG,
T23.PR_REP_MANL_FLG,
T23.PR_REP_SYS_FLG,
T23.PR_REP_ASGN_TYPE,
T23.PR_BL_ADDR_ID,
T23.PR_BL_PER_ID,
T23.PR_MKT_SEG_ID,
T23.PR_CON_ID,
T23.PR_CRDT_AREA_ID,
T23.PR_INDUST_ID,
T23.BU_ID,
T23.PR_POSTN_ID,
T23.PR_MED_PROC_ID,
T23.PR_PRFL_ID,
T23.PR_SRV_AGREE_ID,
T23.PR_SHIP_ADDR_ID,
T23.PR_SHIP_PER_ID,
T23.PR_SPEC_ID,
T23.PR_PRTNR_OU_ID,
T23.PR_SYN_ID,
T23.PR_TERR_ID,
T23.PR_OU_TYPE_ID,
T23.CG_PRMO_END_OFFSET,
T23.REFERENCE_CUST_FLG,
T23.REGION,
T23.REL_ID,
T23.REVENUE_CLASS_CD,
T23.ROW_ID,
T23.RULE_ATTRIB1,
T23.RULE_ATTRIB2,
T18.OWN_INST_ID,
T18.INTEGRATION_ID,
T23.SRV_PROVDR_FLG,
T23.CG_SHIP_END_OFFSET,
T23.CG_SHIP_STRTOFFSET,
T23.SURVEY_TYPE_CD,
T15.NAME,
T23.TAX_LIST_ID,
T20.TOT_NUM_LIVES,
T23.PTNTL_SLS_CURCY_CD,
T23.OU_TYPE_CD,
T5.ATTRIB_09,
T5.ATTRIB_53,
T5.ATTRIB_41,
T5.ATTRIB_52,
T5.ATTRIB_34,
T14.ATTRIB_07,
T14.ATTRIB_34,
T5.ATTRIB_40,
T5.ATTRIB_47,
T14.ATTRIB_35,
T8.X_BILL_CYCL_FLAG,
T23.MISC_FLG,
T14.ATTRIB_03,
T23.X_SOURCE,
T5.ATTRIB_36,
T5.ATTRIB_51,
T5.ATTRIB_50,
T5.ATTRIB_49,
T5.ATTRIB_48,
T2.LOGIN,
T5.ATTRIB_44,
T5.ATTRIB_45,
T5.ATTRIB_42,
T5.ATTRIB_43,
T14.ATTRIB_36,
T14.ATTRIB_37,
T8.PAYMT_METH_CD,
T5.ATTRIB_46,
T23.X_SWITCH_BUREAU,
T23.X_SWITCH_BUREAU_CODE,
T23.SVC_CVRG_STAT_CD,
T14.ATTRIB_06,
T23.FRGHT_TERMS_INFO,
T23.OU_NUM_1,
T16.FST_NAME,
T16.LAST_NAME,
T7.ROW_STATUS,
T11.ROW_ID,
T22.BRICK_ID,
T11.COUNTRY,
T11.COUNTY,
T11.ZIPCODE,
T11.STATE,
T11.ADDR,
T11.CITY,
T3.CITY,
T3.COUNTRY,
T3.ZIPCODE,
T3.STATE,
T3.ADDR_LINE_2,
T3.ADDR,
T9.PAR_BU_ID,
T23.ROW_ID,
T23.MODIFICATION_NUM,
T23.CREATED_BY,
T23.LAST_UPD_BY,
T23.CREATED,
T23.LAST_UPD,
T23.CONFLICT_ID,
T23.PAR_ROW_ID,
T20.ROW_ID,
T20.MODIFICATION_NUM,
T20.CREATED_BY,
T20.LAST_UPD_BY,
T20.CREATED,
T20.LAST_UPD,
T20.CONFLICT_ID,
T20.PAR_ROW_ID,
T14.ROW_ID,
T14.MODIFICATION_NUM,
T14.CREATED_BY,
T14.LAST_UPD_BY,
T14.CREATED,
T14.LAST_UPD,
T14.CONFLICT_ID,
T14.PAR_ROW_ID,
T5.ROW_ID,
T5.MODIFICATION_NUM,
T5.CREATED_BY,
T5.LAST_UPD_BY,
T5.CREATED,
T5.LAST_UPD,
T5.CONFLICT_ID,
T5.PAR_ROW_ID,
T18.ROW_ID,
T18.MODIFICATION_NUM,
T18.CREATED_BY,
T18.LAST_UPD_BY,
T18.CREATED,
T18.LAST_UPD,
T18.CONFLICT_ID,
T18.PAR_ROW_ID,
T7.ROW_ID,
T6.ROW_ID,
T22.ROW_ID,
T11.ROW_ID,
T3.ROW_ID,
T1.ROW_ID,
T17.ROW_ID
FROM
SIEBEL.S_ORG_BU T1,
SIEBEL.S_USER T2,
SIEBEL.S_ADDR_PER T3,
SIEBEL.S_ORG_EXT T4,
SIEBEL.S_ORG_EXT_X T5,
SIEBEL.S_PARTY T6,
SIEBEL.S_ACCNT_POSTN T7,
SIEBEL.S_INV_PROF T8,
SIEBEL.S_ORG_EXT T9,
SIEBEL.S_ORG_EXT T10,
SIEBEL.S_ADDR_PER T11,
SIEBEL.S_PARTY T12,
SIEBEL.S_PRI_LST T13,
SIEBEL.S_ORG_INT_X T14,
SIEBEL.S_PRI_LST T15,
SIEBEL.S_CONTACT T16,
SIEBEL.S_PARTY T17,
SIEBEL.S_ORG_EXT_SS T18,
SIEBEL.S_ORG_PROMOPRFL T19,
SIEBEL.S_ORG_EXT_LSX T20,
SIEBEL.S_POSTN T21,
SIEBEL.S_CON_ADDR T22,
SIEBEL.S_ORG_EXT T23
WHERE
T12.CREATED_BY = T2.ROW_ID (+) AND
T23.MASTER_OU_ID = T4.PAR_ROW_ID AND
T23.PAR_OU_ID = T10.PAR_ROW_ID AND
T23.ROW_ID = T19.ACCNT_ID (+) AND T23.PR_PRFL_ID = T19.PROMO_PROFILE_ID (+) AND
T23.CURR_PRI_LST_ID = T13.ROW_ID (+) AND
T23.TAX_LIST_ID = T15.ROW_ID (+) AND
T12.ROW_ID = T8.ACCNT_ID (+) AND
T12.ROW_ID = T23.PAR_ROW_ID AND
T12.ROW_ID = T20.PAR_ROW_ID (+) AND
T12.ROW_ID = T14.PAR_ROW_ID (+) AND
T12.ROW_ID = T5.PAR_ROW_ID (+) AND
T12.ROW_ID = T18.PAR_ROW_ID (+) AND
T23.PR_POSTN_ID = T7.POSITION_ID AND T23.ROW_ID = T7.OU_EXT_ID AND
T7.POSITION_ID = T6.ROW_ID AND
T7.POSITION_ID = T21.PAR_ROW_ID (+) AND
T21.PR_EMP_ID = T16.PAR_ROW_ID (+) AND
T23.PR_ADDR_ID = T22.ADDR_PER_ID (+) AND T23.ROW_ID = T22.ACCNT_ID (+) AND
T23.PR_ADDR_ID = T11.ROW_ID (+) AND
T23.PR_ADDR_ID = T3.ROW_ID (+) AND
T1.BU_ID = :1 AND T23.ROW_ID = T1.ORG_ID AND
T1.BU_ID = T17.ROW_ID AND
T1.BU_ID = T9.PAR_ROW_ID (+) AND
((T23.ACCNT_TYPE_CD != 'Customer') AND
((T23.INT_ORG_FLG != 'Y' OR T1.ORG_PRTNR_FLG != 'N') AND T23.ACCNT_FLG != 'N')) AND
(T10.NAME LIKE 'A308%')
I got "table access full " for T23 (optimizer mode = CHOOSE), then I tried to create an index as following:
DROP INDEX SIEBEL.TEMP7;
CREATE INDEX SIEBEL.TEMP7 ON SIEBEL.S_ORG_EXT
(ACCNT_TYPE_CD, INT_ORG_FLG, ACCNT_FLG)
LOGGING
TABLESPACE INDX_SIEBEL
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
but still got "table access full", not use this index. If I changed to SQL to ( using = "Customer" instead of != "Customer")
...
T1.BU_ID = T9.PAR_ROW_ID (+) AND
((T23.ACCNT_TYPE_CD = 'Customer') AND
((T23.INT_ORG_FLG != 'Y' OR T1.ORG_PRTNR_FLG != 'N') AND T23.ACCNT_FLG != 'N')) AND
(T10.NAME LIKE 'A308%')
the Index will be used. I don't know why, how to create an index for != 'Customer'
Thanks,
Lily
But I suppose that if T23.ACCNT_TYPE_CD is usually != 'Customer' then this is not good idea to use index on it...
Did you run the statistics of Oracle?
Oracle needs this in order to use indexes.
CREATE OR REPLACE PROCEDURE compute_statistics IS
BEGIN
dbms_stats.gather_database
END compute_statistics;
/
execute compute_statistics ;
as SYS user.
Index for index for != 'Customer' depends on the cardinality (nimber of the different values in the column).
If it is small the bitmap index is a good solution.
Oracle needs this in order to use indexes.
CREATE OR REPLACE PROCEDURE compute_statistics IS
BEGIN
dbms_stats.gather_database
END compute_statistics;
/
execute compute_statistics ;
as SYS user.
Index for index for != 'Customer' depends on the cardinality (nimber of the different values in the column).
If it is small the bitmap index is a good solution.
>>T23.ACCNT_TYPE_CD != 'Customer' ..
It is non-equal condition so your index might not be used by optimizer.
optimizer will use index when an equal condition is used.
It is non-equal condition so your index might not be used by optimizer.
optimizer will use index when an equal condition is used.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Forced accept.
Computer101
EE Admin
Computer101
EE Admin
DECODE(T23.ACCNT_TYPE_CD, 'Customer', 1, 0) = 0
and index on DECODE(T23.ACCNT_TYPE_CD, 'Customer', 1, 0)