How will Oracle handle a where clause condition comparing against a subquery with a constant result?

Let's say I have the following:

select primary_id
from myschema.table_a
where row_changed_date > ( select last_refreshed
                           from myschema.last_refresh lr
                           where asset_type = 0 );

The result of th开发者_运维问答e subquery will not change between assets. I technically want to compare directly with a constant value, but I don't want to run a separate query to get that constant. Will Oracle run the subquery for each pass through the iteration? Is there a better way to do this?

You need to ensure that your subquery returns a single row, or use a qualified comparison:

WHERE row_changed_date > ALL ( SELECT last_refreshed ... )

Or:

WHERE row_changed_date > (SELECT MAX(last_refreshed) ...)

Assuming asset_type is a column in the last_refresh table, then Oracle will compute the subquery once. If it is actually a column in the table_a table, then you have a correlated subquery; that has to be evaluated per row.

This answer is actually DBMS-agnostic - the rules apply to Oracle as well as to other SQL DBMS.

In your example using a subquery -- no, Oracle would not run the subquery for every row. A correlated subquery would though.

Use:

SELECT a.primary_id
  FROM MYSCHEMA.TABLE_A a 
  JOIN MYSCHEMA.LAST_REFRESH lr ON lr.last_refreshed < a.row_changed_date
                               AND lr.asset_type = 0

Alternate version using WHERE:

SELECT a.primary_id
  FROM MYSCHEMA.TABLE_A a 
  JOIN MYSCHEMA.LAST_REFRESH lr ON lr.last_refreshed < a.row_changed_date
 WHERE lr.asset_type = 0

Because of using an INNER JOIN, there's no difference in the results or performance between either of these versions of the query. That would not be the case if using OUTER JOINS (LEFT, RIGHT).