CODEWITHSUNDEEP
sqloracle-database
zarpendorff
zarpendorff

Reputation: 61

Oracle Queries: How can I influence the order, in which WHERE-conditions are validated?

my question can best be illustrated by the following example of a query in Oracle SQL:

SELECT A.id 
FROM A, B
WHERE A.primary_key = B.foreign_key
AND B.primary_key = 'specific value'
AND A.some_expensive_attribute = '1'

Let's assume that A is not a table, but a view and that the calculation of A.some_expensive_attribute is expensive considering the time it takes to compute it for one single row of A.

In my particular application, the view A is huge while there are only a few rows in B with B.primary_key = 'specific value'. Thus, it takes up to 10 minutes to compute the result.

However, when I change the last line/condition to the following (completely) redundant subselect:

AND (
     SELECT some_expensive_attribute 
        FROM A as A2 
     where A2.primary_key = A.primary_key
    ) = '1'

... it only takes less than a second. I figured that implementing the redundant subselect on the A-row itself changes the order to check the where conditions.

My question is: Is it possible to tell Oracle "Check this condition at the end!" ?

Please note that in the actual application (a PDM-system), I can not substitute the last line like this. Thus, this workaround is no real solution.

Thanks!

Upvotes: 2

Views: 133

Answers (2)

vishad
vishad

Reputation: 1164

Usually Oracle selects the best possible plan based on the cost consideration.

If the plan is not optinmal then you should gather stats on the underlying tables in query. For stats gathering you can use dbms_stats.gather_table_stats procedure.

Upvotes: 0

Emmanuel
Emmanuel

Reputation: 14209

You may try a hint:

SELECT /*+ ordered */ A.id 
FROM B, A -- order changed
WHERE A.primary_key = B.foreign_key
AND B.primary_key = 'specific value'
AND A.some_expensive_attribute = '1'

It does not change the way the conditions in the where clause are computed, but it changes the way joins are done and here B precedes A. This is worth a try.

Upvotes: 1

Related Questions