Subject Re: [firebird-support] it's possible to have a view cache?
Author Martijn Tonies

> hi all, i'm newbie to databases so sorry if this is a stupid question :)
> i have a table where the records are read-only and where new rows are
> added by a service with a single connection/transaction (so using a
> trigger on update it's not very good for my purpose).
> then i have a view (but maybe there's a better solution for my needs)
> which contains the results of same calculations made on the values of my
> table (at the moment i don't know if i can do that calcolous with sql, for
> example with a store procedure, or if i need to use an UDF).
> i don't know very well how a view works but it seems that is not cached,
> so every time that i do a select on the my view all the data is reed again
> from the original table, so for example if i have a column in the view
> that contains count(*) it will be revaluated on every select.

That is correct, this is what a view does.

> my idea is to have samething as a cache of the view wich will be reloaded
> only when the original table is updated (i mean when the transaction that
> had updated the table has been commited, not on the insertion of every
> single new row), otherwise it will contains the static results of the
> calcolous based on the values of the table.
> what do you suggest?

Firebird does not support this automatically, it seems that what
you want is something known as "materialized views".

From what I understand, you should use a physical database table
and insert rows yourself from the application that does the inserting
in the other tables.

With regards,

Martijn Tonies
Upscene Productions

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!

Database questions? Check the forum: