Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

creating an index question

Posted on 2006-06-30
7
Medium Priority
?
1,020 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
6 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Suggested Courses

772 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