On Demand Refresh FAQS

1. What is On Demand Refresh in Oracle?

On Demand Refresh in Oracle refers to the manual triggering of a refresh operation for a materialized view. The refresh is performed only when explicitly requested by the user or administrator, rather than relying on automatic refresh intervals or schedules.

 

2. How do I trigger an On Demand Refresh in Oracle?

You can trigger an On Demand Refresh using the DBMS_MVIEW.REFRESH procedure:

EXEC DBMS_MVIEW.REFRESH('materialized_view_name');

Where:

• 'materialized_view_name' is the name of the materialized view you want to refresh.
• By default, this will attempt a Force Refresh (Fast Refresh if possible, or Complete Refresh if needed).

To specify a refresh method:

• Fast Refresh: EXEC DBMS_MVIEW.REFRESH('materialized_view_name', 'F');
• Complete Refresh: EXEC DBMS_MVIEW.REFRESH('materialized_view_name', 'C');
• Force Refresh (default): EXEC DBMS_MVIEW.REFRESH('materialized_view_name', 'R');

 

3. When is On Demand Refresh used?

You would use On Demand Refresh when:

• You want to manually control when the materialized view is refreshed.
• You need the materialized view to be up-to-date for a specific operation (e.g., running reports, generating analytics).
• You want to minimize unnecessary refreshes to save resources.
• You need a refresh on an ad-hoc basis rather than on a fixed schedule.

 

4. What is the difference between On Demand Refresh and Scheduled Refresh?

• On Demand Refresh is triggered manually, only when needed, and gives you full control over when the refresh occurs.
• Scheduled Refresh is configured to refresh the materialized view automatically at set intervals (e.g., hourly, daily).

 

5. How does On Demand Refresh work?

When you trigger an On Demand Refresh:

1. Oracle checks if the materialized view can be refreshed using the specified method (Fast, Complete, or Force).
2. If Fast Refresh is possible, Oracle applies the changes incrementally.
3. If Fast Refresh is not possible, Oracle falls back to Complete Refresh, which rebuilds the materialized view from scratch.

 

6. Can I schedule an On Demand Refresh?

Yes, you can combine On Demand Refresh with DBMS_SCHEDULER to schedule the refresh operation at specific times. This allows you to automate the process without needing to trigger it manually each time.

Example:

BEGIN

   DBMS_SCHEDULER.create_job (

      job_name        => 'refresh_sales_mv',

      job_type        => 'PLSQL_BLOCK',

      job_action      => 'BEGIN DBMS_MVIEW.REFRESH(''sales_mv'', ''R''); END;',

      start_date      => SYSTIMESTAMP,

      repeat_interval => 'FREQ=DAILY; BYHOUR=0; BYMINUTE=0;',

      enabled         => TRUE

   );

END;

/

This job will refresh the materialized view daily at midnight.

 

7. What are the advantages of On Demand Refresh?

• Control: You have full control over when the refresh happens.
• Resource Efficiency: Refresh only occurs when necessary, reducing unnecessary resource consumption.
• Avoid Stale Data: You can ensure the materialized view is up-to-date only when you need the latest data.
• Flexibility: It allows for manual triggering based on business needs, such as before generating reports or running analytics.

 

8. What are the disadvantages of On Demand Refresh?

• Manual Effort: The refresh is triggered manually, requiring intervention to initiate it.
• Risk of Stale Data: If the refresh is not triggered regularly, the materialized view may not reflect the latest data.
• Possible Human Error: If the refresh is forgotten or not triggered at the right time, there is a risk that the materialized view could become out of sync.

 

9. How can I monitor the refresh status of a materialized view?

You can use the DBA_MVIEW_REFRESH_TIMES view to monitor the refresh status and timestamps of materialized views:

SELECT * FROM DBA_MVIEW_REFRESH_TIMES

WHERE mview_name = 'YOUR_MVIEW_NAME';

This shows when the materialized view was last refreshed and what refresh method was used.

 

10. Can I perform an On Demand Refresh for multiple materialized views?

Yes, you can refresh multiple materialized views by running separate DBMS_MVIEW.REFRESH calls for each view. For example:

EXEC DBMS_MVIEW.REFRESH('view1');

EXEC DBMS_MVIEW.REFRESH('view2');

Alternatively, you can use PL/SQL blocks to refresh multiple views in a single call.

 

11. What happens if I don't refresh the materialized view regularly?

If the materialized view isn't refreshed regularly, it might not reflect the latest changes made to the underlying data. This could lead to inconsistencies, especially in scenarios where real-time data accuracy is crucial (e.g., reports or dashboards).

 

12. Can I control the refresh type (Fast, Complete, Force) for On Demand Refresh?

Yes, you can specify the refresh type when performing an On Demand Refresh:

• Fast ('F'): Incrementally refreshes the materialized view by applying changes.
• Complete ('C'): Rebuilds the entire materialized view from scratch.
• Force ('R'): Attempts a Fast Refresh, but falls back to a Complete Refresh if needed.

 

13. How does the Fast Refresh work with On Demand Refresh?

Fast Refresh applies only the changes (inserts, updates, deletes) from the base tables to the materialized view. It is typically faster and more resource-efficient. However, for Fast Refresh to work, materialized view logs must be present on the base tables.

 

14. Can I prevent an automatic refresh if I prefer only On Demand Refresh?

Yes, you can configure the materialized view to not automatically refresh. To prevent automatic refreshes, you can set the refresh method to ON DEMAND when creating or altering the materialized view. This ensures that the materialized view is only refreshed when manually triggered.

Example:

CREATE MATERIALIZED VIEW mv_name

BUILD IMMEDIATE

REFRESH ON DEMAND

AS SELECT ...;

 

15. Is On Demand Refresh suitable for large materialized views?

On Demand Refresh can be particularly useful for large materialized views because you can control when the refresh occurs. However, if you are using Complete Refresh for large views, it can be resource-intensive. It's important to carefully monitor and manage the refresh operation to avoid performance degradation.