Explain plan - OR causes FULL table scan
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Explain plan - OR causes FULL table scan

  1. #1
    Join Date
    Nov 2001
    Location
    New Brunswick, NJ
    Posts
    67

    Smile

    I have a sql similar to the following:

    select * from table a, table b
    where a.pk = b.fk
    and (a.code = 'X' and b.val > 1)
    or (a.code = 'Y' and b.val < 1)

    When I do the explain plan with the last line in there, I get full table scans. Why??


    Is there a good book on how to tune up SQL and understanding the explain plan?

    Thanks,

    Paul

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Does enclosing your OR-ed condition into parenthesis change the execution plan? It certainly does change the queries logic, and I'm sure it returns the resultset you actualy wanted. In other words, I think your original query does not return rows that you actualy want!

    select * from table a, table b
    where a.pk = b.fk
    and
    (
    (a.code = 'X' and b.val > 1)
    or (a.code = 'Y' and b.val < 1)
    )

    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Nov 2001
    Location
    New Brunswick, NJ
    Posts
    67
    Thank you!!

    That was my problem.

    Explain plan now shows by index range scan.

    Perfect.

Posting Permissions

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


Click Here to Expand Forum to Full Width