Oppolzer - Informatik / Blog

Hi Bernd

SELECT * and SELECT 1 in an EXISTS-subquery are always treated the same.

Mit freundlichen Grüßen
W.J.


Von: Bernd Oppolzer [mailto:bernd.oppolzer@t-online.de]
Gesendet: Sonntag, 28. Oktober 2012 00:07
An: db2-l@lists.idug.org
Betreff: [DB2-L] - RE: Nasty Select * or Are They? In Table Expressions

Another related question is:

it is always sufficient to code SELECT 1 - that is, a constant -
on a subquery with WHERE EXISTS or WHERE NOT EXISTS.
Is there a performance penalty, if someone codes SELECT colx
on such a subquery, especially if without colx the subquery would be
index-only, but with colx it is not?

I always hope that DB2 is smart enough to throw away the unnecessary
column references on the subquery's select list, but I'm not sure ...

does someone know for sure? Of course, in my own programs, I try
to avoid all unnecessary column references in subquery select lists.

Kind regards

Bernd



Am 27.10.2012 23:52, schrieb P.V.:

In most cases, the optimizer should spot this, i.e., should remove those columns
from the NTE "SELECT *" which are not used later on. I have not tested this
extensively, though. Someone having experience with performance differences
between NTE SELECT * and NTE SELECT col1,2,3 ?

In Reply to D.N.:

P.,
Putting the select * within the table expression will cause more memory to be used.
Whether it affects your performance or not will depend on whether the table expression
is materialized or not and how much additional memory and how big it makes the temp table.
For instance, materializing a very lengthy row 100K times vs a 100 byte row 10 times.
I don't think it violates your standard as the outer SELECT goes by what columns
you want selected. So, give it a try both ways and see the difference in your cost.

-- P.V.

-----End Original Message-----


-----End Original Message-----