-1

I've already got a working db view :

CREATE OR REPLACE FORCE VIEW "USER_DATA_VIEW" ("APP_USER_ID", "FIRST_NAME", "LAST_NAME", "FULL_NAME", "LOCATION_ID", "LOCATION_NAME") AS 
  SELECT U.APP_USER_ID, U.FIRST_NAME, U.LAST_NAME, U.FIRST_NAME || ' ' || U.LAST_NAME FULL_NAME, U.LOCATION_ID, LOC.LOCATION_NAME
   FROM APP_USER U
   JOIN LOCATION LOC ON U.LOCATION_ID = LOC.LOCATION_ID
;

Since the user/location data doesn't change often, and it's heavily relied upon from other views and part of the app, I want to try to cache to see if I can get more performance from it.

Not being an oracle person, I m reading that materialized views might just do this.

How can I create materialized view from the existing view with the index on app_user_id column and with no refresh option?

I've tried this :

CREATE MATERIALIZED VIEW USER_DATA_VIEW_MT
BUILD IMMEDIATE
NO REFRESH
AS SELECT U.APP_USER_ID, U.FIRST_NAME, U.LAST_NAME, U.FIRST_NAME || ' ' || U.LAST_NAME FULL_NAME, U.LOCATION_ID, LOC.LOCATION_NAME
 FROM APP_USER U
 JOIN LOCATION LOC ON U.LOCATION_ID = LOC.LOCATION_ID

And couple other versions but wasn't able to create it.

3

NO REFRESH is not valid syntax, it is NEVER REFRESH. But I guess you want to refresh the mview occassionally, so REFRESH ON DEMAND is a better option.

This alone is not enough, you also need to set the parameter query_rewrite_enabled to stale_tolerated:

alter session set query_rewrite_integrity=stale_tolerated;

You also mentioned using an index, yes, you can create indexes on materialized views.

Your query is really simple, just 2 tables with a join, I do not think you need a materialized view + query rewrite for this. You could just simply index the base tables properly.

By default, query rewrite works with up-to-date materialized views, so it can provide fresh and actual data (correct results). You need to explicitly allow the database in your session to provide you old data (incorrect results) from the mview using stale_tolerated. Your mview will remain stale regardless of this parameter until you refresh it explicitly. This is required for query rewrite to work with stale data.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.