RESULT CACHE

posted Jan 31, 2012, 1:47 PM by Sachchida Ojha
NEW 11g FEATURE PROVIDES ABILITY TO CACHE THE RESULT OF A QUERY WITH REAL TIME UPDATE.

For Example,

SQL> select /*+ result_cache */  country, state, city, county, customer_id,vendor_id,product
from location,customer
where location.id = customer.location_id
and vendor.product_id=customer.product_id
and customer.product_id=product.id;

when the QUERY is executed for the first time, it has to go to all the base tables, but the result is cached and subsequent executions go against the result cache, not the base tables much like MVs. But that's where the resemblance with MVs ends.

When the underlying data changes, the result cache is automatically refreshed without the DBA's intervention, so results are always accurate.

This is the best suited for tables that do not change frequently.


Comments