Results 1 to 7 of 7

Thread: Index

  1. #1
    Join Date
    Jul 2003
    Posts
    46

    Unanswered: Index

    Hi,
    I have query like that :
    select * from a,b
    where
    b.c_1 (+)= LPAD(a.c_2,9) and
    a.c_3 = 'N' and
    (a.c_6 like '%aaaa%' or
    a.c_5 like '%aaaa%');
    The execution of the above sql involves full access to table a.
    I tried some indexes on columns c_6, c_5 but there were no effects.
    I noticed that problem is that I use '%' from each side of the string in like statement and I use 'or' but I do not know how to solve this.

  2. #2
    Join Date
    Nov 2003
    Location
    Mumbai
    Posts
    12
    Hi

    Index will not be used on columns (c_5, c_6) as they are used with Wild Card characters. Use of % will disable index.

    Regards,

    SK

  3. #3
    Join Date
    Jul 2003
    Posts
    46

    Index

    Hi
    But when I use
    select * from a,b
    where
    b.c_1 (+)= LPAD(a.c_2,9) and
    a.c_3 = 'N' and
    a.c_6 like 'aaaa%'
    execution uses index(c_6) and I use '%' (but only from right side of the string).

  4. #4
    Join Date
    Nov 2003
    Location
    Mumbai
    Posts
    12
    Hello

    Even if u use wild card character on right hand side, still Oracle will disable that index...........

    As u say the execution uses index on column c_6, have u seen the EXPLAIN_PLAN for this SQL statement...

    See what indexes are used...then you will come to know whether indexes are used or full table scan is being done.

  5. #5
    Join Date
    Jul 2003
    Location
    England
    Posts
    152
    The only column you can build a useful index on is a.c_3 - as it is the only one with a value.

    Wildcards (%) if used on both sides will not use any index, but if the wildcard is on the right hand side (ie aaa%) the index will be used (usually - depends on what mood the optimiser is in!)

    Hope this helps
    Regards
    Dbabren

  6. #6
    Join Date
    Jul 2003
    Posts
    46

    Re: Index

    Thanks for your help.
    As I understand There is now way to use index when you use '%' both sides.
    But I have also problem with 'or' statement :

    select * from a,b
    where
    b.c_1 (+)= LPAD(a.c_2,9) and
    a.c_3 = 'N' and
    (a.c_6 like 'aaaa%' or
    a.c_5 like 'aaaa%');

    Oracle does not use index on column c_6 and c_5 executing above sql;
    It uses index when there is only one condition for c_6 OR c_5 or there is
    condition for c_6 AND c_5.
    And I use explain plan to notice which operations and objects are used during execution of my sql.

  7. #7
    Join Date
    Jul 2003
    Location
    England
    Posts
    152
    doesn't like ors either I'm afraid! Might be better to write your query as an union - at least then each half of the union will use the apt index
    Regards
    Dbabren

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •