> The structure of the view is something like CREATE VIEW PersonView ..
> SELECT *
> FROM PersonTable P
> LEFT JOIN City ON City.ID =  P.CityID
> LEFT JOIN Hobby ON Hobby.ID =  P.HobbyID ...
> and so on for the 20 codified tables. City.ID is a primary key, like all IDs for
> remaining codifications.
>
> How can I optimize this view? My problem is that Firebird uses a really slow
> plan because it reads ALL codification records.
> For example, supposing PersonTable has 10 records.
> SELECT COUNT(*) FROM PersonView
>
> I would expect that Firebird only read 10 record, however it reads 10 +
> 10x20  = 210 records.

Where are you getting those details?

Firebird reports every row that is being read, including internal index rows/data.

Also the query PLAN details makes the distinction between "fetches" reads which were resolved by engine page cache and "reads" (actual reads from disk/or OS disk cache)

So, without seeing the fetch details, it is not possible to say how your "problem" can be solved.

> The real problem is that I have millions of records in the database, and a
> simple consult can take minutes when it could take few seconds with an
> optimized plan.

With that size of database, it is likely that the database cache size setting is low and should be increased.

To answer that question, you will need to specific which version of the engine you are running? Classic, SuperClassic or SuperServer.


Sean