[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1022
  • Last Modified:

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
0
nkwzxbz
Asked:
nkwzxbz
1 Solution
 
GGuzdziolCommented:
Maybe this can help

DECODE(T23.ACCNT_TYPE_CD, 'Customer', 1, 0) = 0

and index on DECODE(T23.ACCNT_TYPE_CD, 'Customer', 1, 0)
0
 
GGuzdziolCommented:
But I suppose that if T23.ACCNT_TYPE_CD is usually != 'Customer' then this is not good idea to use index on it...
0
 
schwertnerCommented:
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_stats(cascade=>true);
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.

0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
actonwangCommented:
>>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.
0
 
awking00Commented:
The not equals condition is what requires the full table scan. You can create a function to return one value if the condition is true and another if not, then create a function based index on it.

create or replace function get_type_cd(type_cd_in in varchar2) return number is
v_type_cd varchar2(30);
begin
v_type_cd := type_cd_in;

if upper(v_type_cd) = 'CUSTOMER' then
return 1;
else return 0;
end if;
end;
/

create index t23_fbidx on s_org_ext(get_type_cd(acct_type_cd));

Then in your query -

WHERE
...
get_type_cd(T23.ACCT_TYPE_CD) = 0 AND ...

It may also help using an index hint

SELECT /*+ INDEX(t23 t23_fbidx) */ ...

Good luck!

0
 
Computer101Commented:
Forced accept.

Computer101
EE Admin
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now