Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186

    Unanswered: can I use two indexes in a query with an or in?

    Code:
    create table addr (
    id	number,
    addr1 varchar2(50),
    addr2 varchar2(50)
    );
    
    create index addr1 on addr (addr1);
    create index addr2 on addr (addr2);
    when I run an explain plan this does a full table scan, can I make the OR use either index?

    Code:
    select /*+index(addr addr1) index(addr addr2)*/
    addr.id
    from	addr
    where (addr.addr1 like :paddress1
    	or addr.addr2 like :paddress1)
    cheers
    Robert
    Last edited by robert xr4x4; 05-17-07 at 09:47.
    There are 10 types of people in the world, those that know Binary and those that don't.

  2. #2
    Join Date
    Feb 2007
    Location
    Bratislava, Slovakia
    Posts
    85
    try this function based index :
    create index addr3 on addr (addr1||addr2);

    select
    x.id
    from addr x
    where (x.addr1||x.addr2 like address1
    or x.addr1||x.addr2 like address1)

    second option is divide statement to two part and union all result eg

    select x.id from addr x where (x.addr1 like address1)
    union all
    select y.id from addr y where (y.addr2 like address2)

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    don't forget to analyze the table and index to reset statistics!
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    @baloo99, next time, check the "Disable smilies in text" checkbox, please.

  5. #5
    Join Date
    Feb 2007
    Location
    Bratislava, Slovakia
    Posts
    85

    thx for tip

    Littlefoot: i am new in this forum... sorry. i used quick reply and here i cannot disable smiles...

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    In that case, use [code] tags when posting SQL code. It will also disable smilies.
    Code:
    See? :)

  7. #7
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186
    cheers, that works a treat
    There are 10 types of people in the world, those that know Binary and those that don't.

Posting Permissions

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