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.