CODEWITHSUNDEEP
oracle-databasenetezza
Victor
Victor

Reputation: 17107

Does oracle /netezza execute subqueries only once

In this query:

select L1.*, L2.* from (select t1.col1, max(t1.col2) from ($subquery) t1, group by t1.col1) L1, ($subquery) L2 where L1.col1 = L2.col1

Will Oracle/Netezza execute the $subquery only once? Or twice?

Upvotes: 0

Views: 106

Answers (1)

Lajos Veres
Lajos Veres

Reputation: 13725

Oracle will execute two times. (Actually this isn't 100% accurate. It will execute only the main (compound) query once, but probably it will access the subquery's table's 2 different times/way.)

You should use with for optimizing this way.

(Otherwise you could use MAX() OVER (PARTITION BY ....) for similar things as well.)

Upvotes: 1

Related Questions