This discussion is archived
9 Replies Latest reply: Nov 18, 2012 6:12 AM by Peter Gjelstrup RSS

Index in table

873006 Newbie
Currently Being Moderated
Hi Team,

I have below query
SELECT   trans_num_v, serial_num_n, trans_date_d, account_link_code_n,
         main_account_link_code_n, subsidiary_code_v, db_cr_v, overall_amt_n,
         cleared_amt_n, temp_clr_amt_n, old_yr_adjust_amt_n,
         bill_cleared_flg_v, ROWID
    FROM cb_sub_ar_ap
   WHERE trans_num_v = :b4 || TRIM (:b3)
     AND serial_num_n = :b2
     AND main_account_link_code_n = :b1
     AND bill_cleared_flg_v = 'N'
in that query CB_SUB_AR_AP i have 4 index but one index also not pointing and if we see the
explain plan FULL TABLE SCAN is showing... Please can u guide me what is wrong in this...index.

1) CREATE INDEX SEP_MIG_FINAL_SCHEMA.CB_SUB_AR_AP$2 ON SEP_MIG_FINAL_SCHEMA.CB_SUB_AR_AP
(ACCOUNT_LINK_CODE_N, TRANS_DATE_D)

2) CREATE INDEX SEP_MIG_FINAL_SCHEMA.CB_SUB_AR_AP$3 ON SEP_MIG_FINAL_SCHEMA.CB_SUB_AR_AP
(MAIN_ACCOUNT_LINK_CODE_N, TRANS_DATE_D)

3) CREATE INDEX SEP_MIG_FINAL_SCHEMA.CB_SUB_AR_AP$4 ON SEP_MIG_FINAL_SCHEMA.CB_SUB_AR_AP
(ACCOUNT_LINK_CODE_N, DB_CR_V, BILL_CLEARED_FLG_V

3) CREATE INDEX SEP_MIG_STG.IDX_CB_TRANS_LINK_CD ON SEP_MIG_FINAL_SCHEMA.CB_SUB_AR_AP
(TRANS_NUM_V, ACCOUNT_LINK_CODE_N)

Edited by: 870003 on Nov 16, 2012 10:28 AM
  • 1. Re: Not Pointing to Index in table
    John Spencer Oracle ACE
    Currently Being Moderated
    The only indexes that could possibly be used for your query are number 2 or 4 (I assume the second 3 should be a 4).

    How selective are they?

    What datatype is trans_num_v? By doing a trim and concatenation on the bind variables you are forcing the comparision to be string based.

    Is variable :b1 the same datatype as main_account_link_code_n?

    Post the formatted plan, preferable with the predicates section included.

    John
  • 2. Re: Not Pointing to Index in table
    873006 Newbie
    Currently Being Moderated
    Hi Team ,

    Thank Johan, Please help we how to create the index i.e

    1) If we have huge data like 100L records. what type of index should create.
    2) If index is there and no using index what to do.?

    guide me about index....

    Edited by: 870003 on Nov 17, 2012 6:51 AM
  • 3. Re: Index in table
    ranit B Expert
    Currently Being Moderated
    SELECT   trans_num_v, serial_num_n, trans_date_d, account_link_code_n,
    main_account_link_code_n, subsidiary_code_v, db_cr_v, overall_amt_n,
    cleared_amt_n, temp_clr_amt_n, old_yr_adjust_amt_n,
    bill_cleared_flg_v, ROWID
    FROM cb_sub_ar_ap
    WHERE trans_num_v = :b4 || TRIM (:b3)
    AND serial_num_n = :b2
    AND main_account_link_code_n = :b1
    AND bill_cleared_flg_v = 'N'
    1) CREATE INDEX SEP_MIG_FINAL_SCHEMA.CB_SUB_AR_AP$2 ON SEP_MIG_FINAL_SCHEMA.CB_SUB_AR_AP
    (ACCOUNT_LINK_CODE_N, TRANS_DATE_D)
    I guess, the 1st index would be missing. coz none of the key columns of the index are used in the 'WHERE' clause.
    Please rectify me if i'm wrong and lets discuss.

    Could you please post the Explain Plan?

    Ranit B.
  • 4. Re: Not Pointing to Index in table
    ranit B Expert
    Currently Being Moderated
    2) If index is there and no using index what to do.?
    There are cases that inspite of the presence of an Index, they are not being used by the Optimizer.
    Please refer -- http://www.orafaq.com/tuningguide/not%20using%20index.html
    1) If we have huge data like 100L records. what type of index should create.
    Please refer this -- http://www.oracle.com/technetwork/articles/sharma-indexes-093638.html

    I myself is also reading that. There's a lot concept behind the Indexes.
    Lets wait for some guru to explain it better.

    HTH
    Ranit B.

    Edited by: ranit B on Nov 17, 2012 8:30 PM
  • 5. Re: Not Pointing to Index in table
    Peter Gjelstrup Guru
    Currently Being Moderated
    John Spencer wrote:
    What datatype is trans_num_v? By doing a trim and concatenation on the bind variables you are forcing the comparision to be string based.
    Why is that, John?

    I thougth it was like, when doing implicit data conversion:

    When comparing a character value with a numeric value, Oracle converts the character data to a numeric value.


    Much easier to find out that this will never give a hit: number_column = 'a'


    BR
    Peter
  • 6. Re: Not Pointing to Index in table
    onedbguru Pro
    Currently Being Moderated
    Implicit data conversion will force the optimizer to NOT choose any index involved in that conversion.

    NUMBER != CHAR/VARCHAR
    1234 != '1234'

    There is a great article by Tom Kyte on implicit data conversion (search for "asktom implicit conversion evil")

    Not only does the optimizer choose a sub-optimal plan, it is possible to use it for sql injection. VERY BAD stuff!!!
  • 7. Re: Not Pointing to Index in table
    onedbguru Pro
    Currently Being Moderated
    Implicit data conversion will force the optimizer to NOT choose any index involved in that conversion.

    NUMBER != CHAR/VARCHAR
    1234 != '1234'

    There is a great article by Tom Kyte on implicit data conversion (search for "asktom implicit conversion evil")

    Not only does the optimizer choose a sub-optimal plan, it is possible to use it for sql injection. VERY BAD stuff!!!
  • 8. Re: Not Pointing to Index in table
    onedbguru Pro
    Currently Being Moderated
    Implicit data conversion will force the optimizer to NOT choose any index involved in that conversion.

    NUMBER != CHAR/VARCHAR
    1234 != '1234'

    There is a great article by Tom Kyte on implicit data conversion (search for "asktom implicit conversion evil")

    Not only does the optimizer choose a sub-optimal plan, it is possible to use it for sql injection. VERY BAD stuff!!!
  • 9. Re: Not Pointing to Index in table
    Peter Gjelstrup Guru
    Currently Being Moderated
    Hi onedbguru,

    My point was that according to rules for implicit conversion, I think the comparison would be carried out like

    1234 := to_number('1234')


    That's' why I asked John, if I read him corrrectly he said it will be like

    to_char(1234) := '1234'

    Which of course would mean that no index on number_column would be used (Unless a FBI)


    And no, I don't think that article is so great - at least not for this topic - but thanks anyway.

    Cannot see the relevanse of mentioning sql injection - binds are being used.


    BR
    Peter

    P.S: Not online right now, I will maybe test for myself, once I get fired up monday morning.

    Then maybe I will do something like this (To convicne myself):
    SQL> create table x (n primary key, y) as select level, level from dual connect by level <= 10
    Table created.
    
    SQL> var b number
    SQL> exec :b := 1
    PL/SQL procedure successfully completed.
    
    SQL> set autotrace traceonly explain
    SQL> select * from x where n = :b || '' and y = 1
    
    Execution Plan
    ----------------------------------------------------------
       0       SELECT STATEMENT Optimizer Mode=ALL_ROWS (Cost=1 Card=1 Bytes=26)
       1    0    TABLE ACCESS BY INDEX ROWID SCOTT.X (Cost=1 Card=1 Bytes=26)
       2    1      INDEX UNIQUE SCAN SCOTT.SYS_C00204376 (Cost=1 Card=1)
    
    
    SQL> select * from x where n || '' = :b and y = 1
    
    Execution Plan
    ----------------------------------------------------------
       0       SELECT STATEMENT Optimizer Mode=ALL_ROWS (Cost=2 Card=1 Bytes=26)
       1    0    TABLE ACCESS FULL SCOTT.X (Cost=2 Card=1 Bytes=26)
    
        TABLE ACCESS FULL KORT.X (Cost=2 Card=1 Bytes=26)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points