Solved

creating an index question

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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
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 shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

615 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