Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

creating an index question

Posted on 2006-06-30
7
Medium Priority
?
1,015 Views
Last Modified: 2012-05-05
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
Comment
Question by:nkwzxbz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 14

Expert Comment

by:GGuzdziol
ID: 17016113
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
 
LVL 14

Expert Comment

by:GGuzdziol
ID: 17016119
But I suppose that if T23.ACCNT_TYPE_CD is usually != 'Customer' then this is not good idea to use index on it...
0
 
LVL 48

Expert Comment

by:schwertner
ID: 17016125
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
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 19

Expert Comment

by:actonwang
ID: 17017572
>>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
 
LVL 32

Accepted Solution

by:
awking00 earned 2000 total points
ID: 17018047
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
 
LVL 1

Expert Comment

by:Computer101
ID: 20295072
Forced accept.

Computer101
EE Admin
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question