Solved

creating an index question

Posted on 2006-06-30
7
1,000 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
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 47

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 31

Accepted Solution

by:
awking00 earned 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
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.

758 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now