By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,812 Members | 856 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,812 IT Pros & Developers. It's quick & easy.

Scalar sub query in db2

P: n/a
In oracle, there is a performance improvement if scalar subqueries are
used instead of joins. Does this hold good for Db2 (8.2) too?

Aug 26 '08 #1
Share this Question
Share on Google+
2 Replies


P: n/a
ns******@gmail.com wrote:
In oracle, there is a performance improvement if scalar subqueries are
used instead of joins. Does this hold good for Db2 (8.2) too?
Inherently a scalar subquery is not a join.
I.e. a scaar subquery will raise an error if more than one match is
found. You cannot (easily) model that with an OUTER JOIN (you need the
OUTER to handle the NULL)

DB2 will convert scalar subqueries to outer joins (with some spice added
for semantics as noted above) at its own discretion.
So obviously in DB2 a scalar subquery is far from "always better".

Cheers
Serge


--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Aug 26 '08 #2

P: n/a
>In Oracle, there is a performance improvement if scalar subqueries are used instead of joins. Does this hold good for Db2 (8.2) too? <<

I don't think that statement is true in general for any SQL product.
There are two kinds of scalar subqueries:

1) evaluates to a constant -- (SELECT MAX(a) FROM Foobar) AS big_a

2) correlated to a containing query -- (SELECT MAX(a) FROM Foobar AS F
WHERE F.x = G.x) AS big_a_in_G

Then you can put them in the FROM clause or the SELECT clause, etc. If
I have a clustered table in Oracle, joins are already done, etc. There
are too many options for a general statement like that.
Aug 27 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.