Ok so I have a ‘master table called SKIN_ loosely defined as follows:
Table SKIN_
----------------------------------------
ID bigint not null (primary key)
COLOUR_ID bigint (optional lookup field to COLOUR_ table)
For the purposes of convenience, I have created a view SKIN on SKIN_ to include
the COLOUR name:
View SKIN
----------------------------------------
create or alter view SKIN
as
select
S.ID,
COL.NAME
from SKIN_ S
left outer join COLOUR_ COL on COL.ID = S.SOLD_COLOUR_ID;
It uses left outer join COLOUR_ because COLOUR_ID is optional.
Then I have a table called COLLECTION_ that refers to a subset of SKIN_ items:
Table COLLECTION_
----------------------------------------
ID bigint not null (primary key)
SKIN_ID bigint (not null & indexed)
I want to return all rows in the view SKIN where the SKIN_ID is in COLLECTION_.
For this I tried 2 queries, both of which yield same results in terms of query
plan:
select S.*
from SKIN S
where exists
(
select * from COLLECTION_ COLL
where S.ID = COLL.SKIN_ID
)
And
select S.*
from COLLECTION_ COLL
join SKIN S on S.ID = COLL.SKIN_ID
in both cases FB does a full scan (300K rows) of SKIN_ to return the 1 or 2
required. Hardly efficient
If I change the view SKIN to:
create or alter view SKIN
as
select
S.ID
from SKIN_ S
join COLOUR_ COL on COL.ID = S.SOLD_COLOUR_ID;
by removing the outer join, then the proper indexes are used and the results
are returned. But removing the outer join in the view will drop the rows where
COLOUR_ID is null and is not want I require.
If I put the left outer join on COLOUR_ in the query
select S.*
from COLLECTION_ COLL
join SKIN S on S.ID = COLL.SKIN_ID
left outer join COLOUR_ COL on COL.ID = S.SOLD_COLOUR_ID
I get proper use of indexes and the desired result, but lose the convenience of
a view.
The same thing happens in many other queries where I use views and IMHO I find
it non-sensical. I have come to the conclusion that FB does not choose great
query plans when using views. Any others experience the same?
It is really hard to tune queries if the results _seem_ unpredictable and
there are no visibilty in how FB executes the query.
Help would be appreciated
Louis
