Setting expiration time when caching view

Question

I want to cache the result from a view in Oracle. My view is declared as:

CREATE VIEW SOME_VIEW AS
SELECT
    /*+ RESULT_CACHE */
    u.name
    c.info
FROM
    Users u
    LEFT OUT JOIN Contacts c ON c.user_id = u.id

Now I want to explicitly set the cache expiration time of the for this view. If I have understood it right, the expiration is applied to the cache globaly.

Is it possible to set the expiration time per query?

Answer 1

Result Set Caching stashes the result set of a query in-memory. Any query which returns this result set (or a subset of it) hits the cache instead of the database. The lifespan of the cache is from the first time the source query is executed until the underlying tables are changed through DML. This makes the /*+ RESULT_CACHE */ hint ideal for queries which are expensive to execute (or executed very often) and which select from tables whose data doesn't change very often.

Consequently there isn't any mechanism for us to invalidate the result set cache: the database manages it for us invisibly. Nevertheless, if you really wanted to invalidate the cache periodically (your database is performing too well or something) maybe you could schedule a job to execute a meaningless update on one of the tables the view relies on:

update Contacts c
set c.user_id = c.user_id + 0