SUB query execution strategy in Oracle on a table with huge number of tuple

Question

We have a table with good amount of rows(150,000+) and each row has to be selected based on a SUB query on another table. The values return by SUB query is independent to the column in this table. So, will oracle run the SUB query for every tuple ?

Example

TableZ

id,
location

TableA (150K+)

name,
id,
type

TableB

type,
color

Query

select * from TableZ 
join
 (select name, id, type from TableA where type is null or type in 
     (select type from TableB where color='red')
 ) tblA_RED on TableZ.id=tblA_RED.id

My question is how many time will the SUB query select type from TableB where color='red' execute ?

For SQL code questions give DDL for important indexing information. Also give EXPLAIN output when query execution/implementation/optimization is relevant. — philipxy, Apr 08 '19 at 06:27
Without [additional information](https://stackoverflow.com/a/34975420/4808122) there is not much to say. Oracle can even **perform the query zero time** as in 12.1 it can be rewritten in [Null Accepting Semi Join](https://hourim.wordpress.com/2017/12/22/null-accepting-semi-join/) — Marmite Bomber, Apr 08 '19 at 07:45

Gro · Answer 1 · 2019-04-07T23:20:58.343

Typically a DB engine would process query (select type from TableB where color='red') only once and use the result to create an inline view equivalent for (select name, id, type from TableA where type is null or type in (select type from TableB where color='red') ) and finally execute the outer select by joining with TableZ.

You may want to add distinct in the query that picks up type from TableB like so

(select distinct type from TableB where color='red')

This may give slightly better performance

Gordon Linoff · Accepted Answer · 2019-04-07T23:55:50.243

The specific answer to your question is that Oracle should evaluate the subquery only once.

However, your query is phrased with unnecessary subqueries. You can start with:

select z.*, a.name, a.id, a.type
from TableZ z join
     TableA a
     on z.id = a.id
where a.type in (select b.type from TableB b where b.color = 'red');

This is unlikely to affect performance, but it simplifies what you are doing. Next, TableB does not appear to have duplicate values, so I would suggest:

select z.*, a.name, a.id, a.type
from TableZ z join
     TableA a
     on z.id = a.id left join
     TableB b
     on b.type = a.type
where b.color = 'red' or a.type is null;

Phrasing the query as a join often gives the optimizer more choice -- and more choice often means faster queries.

OP includes the condition "where type is null or type or type in ( ..." so I think you may need to outer join TableB and add "and a.type is null to the WHERE clause). Otherwise spot on — Jim Castro, Apr 07 '19 at 23:38

SUB query execution strategy in Oracle on a table with huge number of tuple

2 Answers2