Oracle SELECT in SELECT statement optimization

Question

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 ?

Boneist · Accepted Answer · 2015-03-26 17:29:52Z

3

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.

answered Mar 26, 2015 at 17:29

Boneist

23.6k1 gold badge28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Thanks, good article. Is there any way to verify that oracle cached it?

Maybe this asktom post will help you.