Plan for query from VIEW problem .. dynamic sql error sql error code = -104 Token unknown .. PLAN

[Karol Bieniaszewski]

Hi

i create View with select with union of two tables

some like

CREATE VIEW XXX(AAA)
SELECT A.AAA FROM TABLE_A A /* first query */
UNION
SELECT B.BBB FROM TABLE_B B /* second query */

now i try to select from thises View

select * from XXX B
i get plans
PLAN (B A NATURAL)
PLAN(B B NATURAL)

but if i try

select
*
from
XXX B
PLAN (B A NATURAL)
PLAN(B B NATURAL)

i get error "dynamic sql error sql error code = -104 Token unknown .. PLAN

how can i store plan for this type of query???

in real project i have
e.g good plan for second query of view but for first is wrong plan and i can
not change it :(

please help
Karol Bieniaszewski

[Wayne Niddery (TeamB)]

"Karol Bieniaszewski" <livius...@poczta.onet.pl> wrote in message
news:4860e8bb$1...@newsgroups.borland.com...

>
> but if i try
>
> select
> *
> from
> XXX B
> PLAN (B A NATURAL)
> PLAN(B B NATURAL)
>
> i get error "dynamic sql error sql error code = -104 Token unknown .. PLAN
>
> how can i store plan for this type of query???

It is showing you the plans for each view in the query. When trying to
specify your own plan, you can only specify *one* as far as I know - for
your own query, not the underlying views.

So likely this would be ok:

select * from
XXX B

PLAN (B NATURAL)

However, you really should not be specifying your own plans. In almost all
cases a query can be optimized simply by adjusting the structure or by
adding appropriate indexes. Using you own plans should be ab absolutely last
resort only.

--
Wayne Niddery - TeamB (www.teamb.com)
Winwright, Inc. (www.winwright.ca)

[Karol Bieniaszewski]

Uzytkownik "Wayne Niddery (TeamB)" <wnid...@chaffaci.on.ca> napisal w
wiadomosci news:4861...@newsgroups.borland.com...

yes but indexes have wrong calculation of selectivity
if i select something with foreign key
e.g. table have 10000 records and only 90 have value but rest is null
then selectivity is very bad - but almost queries look for this 90 not for
null
and then plan are in most case NATURAL :(
i suppose that indexes should have two selectivity for "not null" and "with
null"

now i create selectable stored procedure to fix my problems
views are bad becaouse of plans :/

Karol Bieniaszewski