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