outer join order
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: outer join order

  1. #1
    Join Date
    Jun 2000
    Posts
    417

    outer join order

    We have a query which does something similar to


    where
    a.id (+) = b.id
    and b.id (+) = c.id
    and c.other_id (+) = d.other_id
    and expensive_function (arbitrary_parameter, d.column) >= arbitrary_number


    It's my understanding that the only valid path given all the outer joins is to access table D first, in this case with a full table scan since there are no other predicates. Unfortunately given this order the query will probably never return since it needs to execute an expensive function on every row in the relativly large table.

    Is it possible to somehow change the path to filter D last? If I understand outer join restrictions correctly I don't think so, but I'm a little hazy so I thought I'd ask. So far with a few quick ORDERED tests nothing works.

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    whatever order the tables are accessed in, the outer joins mean that the function is going to execute for every row in d anyway -- that's what they do of course.

    It might be possible to avoid execution of the expensive function for some rows by either ...

    i) puttinga function-based index on the d column of ...

    create index my_index on d (expensive_function (arbitrary_parameter, d.column))

    .. although that might not be a viable alternative.

    ... or ...

    ii) avoiding execution of the function for some rows based on the values of d.column and arbitrary_parameter. For example, if you knew that if in al cases for arbitrary_parameter > d.column, then ...

    NOT(expensive_function (arbitrary_parameter, d.column) >= arbitrary_number)

    .. you could change the predicate to ...

    Code:
    and case
           when (arbitrary_parameter > d.column)
              then -9999999999999999999999999
        else
           expensive_function (arbitrary_parameter, d.column) 
        end >= arbitrary_number
    This would avoid both the expensive_function's execution time and the context switch to PL/SQL for some portion of your rows.

    ... or ...

    iii) expressing expensive_function as SQL.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Jun 2000
    Posts
    417
    Thanks I figured D was stuck at every row without an additional predicate.

    I thought about function based indexes but that's not really an option since the inputs are arbitrary. Eg, when the user runs the query, he's going to give arbitrary_parameter and arbitrary_number. Since those values aren't known ahead of time they can't be indexed for.

    expensive_function is basically a poor mans version of spatial functions. If spatial were an option this query probably wouldn't be a problem, but that is a whole other issue.

    I'll look into the case statement, that may be able to cut down on the executions but I'm not sure how much total benefit there would be.

    Thanks for the reply.

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