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

Thread View: borland.public.interbase.sql

3 messages

[T] Back to Threads [V] Tree View [G] Group Articles

3 total messages Started by Karol Bieniaszewski Tue, 24 Jun 2008 12:30

#160

Author: Karol Bieniaszewski
Date: Tue, 24 Jun 2008 12:30

37 lines
1615 bytes

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

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

#162

Author: Wayne Niddery (TeamB)
Date: Tue, 24 Jun 2008 17:27

33 lines
2088 bytes

"Karol Bieniaszewski" <liviuslivius@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)

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

#166

Author: Karol Bieniaszewski
Date: Wed, 25 Jun 2008 05:18

51 lines
2662 bytes

Uzytkownik "Wayne Niddery (TeamB)" <wniddery@chaffaci.on.ca> napisal w 
wiadomosci news:48612e73@newsgroups.borland.com...
> "Karol Bieniaszewski" <liviuslivius@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)

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

Thread Navigation

This is a paginated view of messages in the thread with full content displayed inline.

Messages are displayed in chronological order, with the original post highlighted in green.

Use pagination controls to navigate through all messages in large threads.

Back to All Threads