Oracle ORA-12015 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-12015: Cannot Create a Fast Refresh Materialized View from a Complex Query

ORA-12015 is thrown when you attempt to create a Materialized View with the REFRESH FAST option, but the underlying query contains constructs that Oracle cannot support for incremental (fast) refresh. Fast Refresh relies on Materialized View Logs to track row-level changes, and this mechanism only works with queries that follow strict structural rules. If your query is too complex — using UNION, analytic functions, or unsupported aggregate patterns — Oracle raises ORA-12015 and refuses to create the MV.

Top 3 Causes

1. UNION / UNION ALL or Set Operators in the Query

Oracle's Fast Refresh engine cannot track incremental changes when the query merges multiple result sets using UNION, UNION ALL, INTERSECT, or MINUS. Since changes must be mapped to a single source log, set operators make this impossible.

-- This will raise ORA-12015
CREATE MATERIALIZED VIEW mv_bad_union
REFRESH FAST ON DEMAND
AS
SELECT emp_id, emp_name FROM employees
UNION ALL
SELECT emp_id, emp_name FROM contractors;

-- Fix: Switch to COMPLETE REFRESH
CREATE MATERIALIZED VIEW mv_good_union
REFRESH COMPLETE ON DEMAND
START WITH SYSDATE NEXT SYSDATE + 1/24
AS
SELECT emp_id, emp_name FROM employees
UNION ALL
SELECT emp_id, emp_name FROM contractors;

2. Analytic Functions or Subqueries

Analytic functions such as RANK(), ROW_NUMBER(), LAG(), and LEAD() are evaluated over the entire result set, making row-level incremental tracking impossible. Similarly, subqueries in the SELECT or WHERE clause break the Fast Refresh requirement.

-- This will raise ORA-12015
CREATE MATERIALIZED VIEW mv_bad_rank
REFRESH FAST ON DEMAND
AS
SELECT
    emp_id,
    salary,
    RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk
FROM employees;

-- Fix: Use COMPLETE REFRESH, or separate the analytic logic into a view
CREATE MATERIALIZED VIEW mv_emp_base
REFRESH FAST ON DEMAND
AS
SELECT emp_id, dept_id, salary FROM employees;

-- Wrap analytic function in a regular view on top of the MV
CREATE OR REPLACE VIEW v_emp_ranked AS
SELECT emp_id, salary,
       RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk
FROM mv_emp_base;

3. Missing MV Log or Incorrect Aggregate Structure

For aggregate-based Fast Refresh MVs, Oracle requires a Materialized View Log on the base table and COUNT(*) must be included in the SELECT list. Using AVG() without COUNT(*) or referencing columns not covered by the MV Log are common mistakes that trigger ORA-12015.

-- Step 1: Create the MV Log on the base table
CREATE MATERIALIZED VIEW LOG ON sales
WITH ROWID, SEQUENCE (product_id, amount)
INCLUDING NEW VALUES;

-- Step 2: Create the MV with COUNT(*) included (required for Fast Refresh)
CREATE MATERIALIZED VIEW mv_sales_agg
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT
    product_id,
    COUNT(*)       AS total_rows,   -- mandatory for fast refresh
    SUM(amount)    AS total_amount,
    COUNT(amount)  AS cnt_amount    -- needed to derive AVG later
FROM sales
GROUP BY product_id;

Quick Fix Solutions

Situation	Fix
UNION / UNION ALL in query	Switch to `REFRESH COMPLETE`
Analytic functions present	Use `REFRESH COMPLETE` or separate into a plain view
No MV Log on base table	Run `CREATE MATERIALIZED VIEW LOG ON <table>`
Missing `COUNT(*)` in aggregate MV	Add `COUNT(*)` to SELECT list

Pre-validate before creating the MV:

-- Run explain to check fast refresh capability before creating
BEGIN
    DBMS_MVIEW.EXPLAIN_MVIEW(
        mv      => 'SELECT product_id, COUNT(*), SUM(amount) FROM sales GROUP BY product_id',
        stmt_id => 'MY_MV_TEST'
    );
END;
/

-- Check the results
SELECT capability_name, possible, msgtxt
FROM mv_capabilities_table
WHERE statement_id = 'MY_MV_TEST';

Prevention Tips

Always run DBMS_MVIEW.EXPLAIN_MVIEW before creating a Fast Refresh MV. This built-in procedure tells you exactly which part of your query prevents Fast Refresh, saving you from trial-and-error at deployment time.
Establish team coding standards for Materialized Views. Document which query patterns support Fast Refresh (simple joins, GROUP BY with COUNT(*), single-table selects) and which do not (UNION, subqueries, analytic functions, DISTINCT). Include an MV design checklist in your code review process to catch issues early.

Related Errors

ORA-12014 – MV Log missing PRIMARY KEY information needed for Fast Refresh.
ORA-23413 – No Materialized View Log exists on the base table.
ORA-32401 – MV Log does not include the required columns for the MV definition.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

DEV Community