CODEWITHSUNDEEP
sqloracle-databasesubquery
antohoho
antohoho

Reputation: 1020

Oracle SELECT in SELECT statement optimization

Does oracle optimize select statement within a select?

for example simple query

SELECT
 test.a as a,
 test.b as b,
 (SELECT id from inner_table WHERE inner_table.code = 'active')
FROM test

lets say table test has 1000 records, what would Oracle do?

Execute select each time, for each record? Cached it?

Or there is a way to use it ones ?

Upvotes: 1

Views: 529

Answers (1)

Boneist
Boneist

Reputation: 23588

Yes, Oracle will cache scalar sub-queries. Tom Kyte has a good piece on the subject.

In your case, I would expect that the subquery will be exececuted once and the cached value used for subsequent rows.

Upvotes: 3

Related Questions