Pattern to ensure executing ONLY required SQL statements ?

[Paul Reddin]

Hi,

We have a whole set of Views we would like to structure a follows, but
they don't seem to optimise in the way we would like:

e.g

CREATE VIEW1 (col1,col2,col3 ....)
as
(
(SELECT col1 from tab1 where tab1.col1 = XYZ.col1),
(SELECT col1 from tab2 where tab2.col1 = XYZ.col1),
(SELECT col1 from tab3 where tab3.col1 = XYZ.col1), ...
FROM
XYZ
)


Now if we simply select ONLY col1 column from the view
e.g
SELECT col1 from VIEW1...

I would expect only the first SELECT in the view to be executed, but
instead
the view calculates all of the statements ??
- NB. the SELECTS are fairly heavyweight so we really don't want to
have to execute them unnecessarily.

Is there a way to structure this so only the required selects are
executed?

UNION or TABLE FUNCTION perhaps?

Many Thanks

Paul.

[Paul Reddin]

Re: Pattern to ensure executing ONLY required SQL statements ?

Hi,

I think I've maybe realised my own explanation for this, after thinking about it
a little more...

I don't think the optimiser can ignore the 2nd an 3rd selects , as of course they
will affect the cardinality of col1.

Maybe, if we performed a
SELECT DISTINCT col1 FROM view1 ...

The other selects would then be redundant and not be performed.

Ultimately, where we are trying to go with this also, is to be able to perform a
COALESCE on statements where we only execute those statements up to
the point of the first NON NULL result.

i.e COALESCE ( (select1...), (select2 ....) ,(select3...)) always executes all
statements even if select1 is NON NULL.

Does this make sense sound a familiar problem?

Thanks.

Paul
Paul.

paul@abacus.co. uk (Paul Reddin) wrote in message news:<1fd2a603. 0312090713.156e cf56@posting.go ogle.com>...[color=blue]
> Hi,
>
> We have a whole set of Views we would like to structure a follows, but
> they don't seem to optimise in the way we would like:
>
> e.g
>
> CREATE VIEW1 (col1,col2,col3 ....)
> as
> (
> (SELECT col1 from tab1 where tab1.col1 = XYZ.col1),
> (SELECT col1 from tab2 where tab2.col1 = XYZ.col1),
> (SELECT col1 from tab3 where tab3.col1 = XYZ.col1), ...
> FROM
> XYZ
> )
>
>
> Now if we simply select ONLY col1 column from the view
> e.g
> SELECT col1 from VIEW1...
>
> I would expect only the first SELECT in the view to be executed, but
> instead
> the view calculates all of the statements ??
> - NB. the SELECTS are fairly heavyweight so we really don't want to
> have to execute them unnecessarily.
>
> Is there a way to structure this so only the required selects are
> executed?
>
> UNION or TABLE FUNCTION perhaps?
>
> Many Thanks
>
> Paul.[/color]

[AK]

Re: Pattern to ensure executing ONLY required SQL statements ?

Paul,[color=blue]
> I don't think the optimiser can ignore the 2nd an 3rd selects , as of course they
> will affect the cardinality of col1.
>[/color]

I would disagree, the subselect won't affect the cardinality at all,
The cardinality is gonna be 1 value from a subselect per a result set row.

Should you ever get more thatn 1 row in a subselect, you'd get an error

[Serge Rielau]

Re: Pattern to ensure executing ONLY required SQL statements ?

Paul,

Which version and platform are you on? DB2 for Multiplatforms has the
capability to drop unreferenced scalar subqueries.
I recall to have added some further improvements to it in FP4.
You may want to try that. If that doesn't work I'd love to see a full
repro-script.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab