How to set ALL_ROWS for a materialized view in Oracle?

user_1864029 0
Viewed 163

How can I make sure that a materialized view is refreshed using the optimizer_mode = ALL_ROWS?

Background: I'm migrating an mview from an ALL_ROWS database to a FIRST_ROWS database and don't want to lose the setting, as the refresh takes orders of magnitude longer with FIRST_ROWS / nested loops compared to ALL_ROWS / hash joins.

The mview is build on top of a view, and a couple of similar mviews are refreshed in a PL/SQL procedure.

I've tried out some minimal examples, it looks like the precedence is

  1. Hint /*+ ALL_ROWS */ in materialized view
  2. If that is not set, a hint in the view is observed
  3. If neither mview nor view have such a hint, the session setting is observed

Is this correct?

I've tried out thee views without and with hints:

CREATE OR REPLACE VIEW v_def   AS SELECT /*+            */ * FROM all_objects;
CREATE OR REPLACE VIEW v_all   AS SELECT /*+ ALL_ROWS   */ * FROM all_objects;
CREATE OR REPLACE VIEW v_first AS SELECT /*+ FIRST_ROWS */ * FROM all_objects;

And five mviews without and with hints:

CREATE MATERIALIZED VIEW mv_def       BUILD DEFERRED AS SELECT /*+            */ * FROM v_def;
CREATE MATERIALIZED VIEW mv_all       BUILD DEFERRED AS SELECT /*+            */ * FROM v_all;
CREATE MATERIALIZED VIEW mv_first     BUILD DEFERRED AS SELECT /*+            */ * FROM v_first;
CREATE MATERIALIZED VIEW mv_all_first BUILD DEFERRED AS SELECT /*+ ALL_ROWS   */ * FROM v_first;
CREATE MATERIALIZED VIEW mv_first_all BUILD DEFERRED AS SELECT /*+ FIRST_ROWS */ * FROM v_all;

When I refresh the mviews with a procedure ...

CREATE OR REPLACE PROCEDURE p_def IS 
BEGIN
  dbms_mview.refresh('mv_def',       atomic_refresh=>FALSE); 
  dbms_mview.refresh('mv_all',       atomic_refresh=>FALSE);
  dbms_mview.refresh('mv_first',     atomic_refresh=>FALSE);
  dbms_mview.refresh('mv_all_first', atomic_refresh=>FALSE);
  dbms_mview.refresh('mv_first_all', atomic_refresh=>FALSE);
END;
/

CREATE OR REPLACE PROCEDURE p_all IS 
BEGIN
  EXECUTE IMMEDIATE 'ALTER SESSION SET optimizer_mode = ALL_ROWS';
  p_def;
END;
/

CREATE OR REPLACE PROCEDURE p_first IS 
BEGIN
  EXECUTE IMMEDIATE 'ALTER SESSION SET optimizer_mode = FIRST_ROWS';
  p_def;
END;
/

... I get the following results:

mview          p_dev        p_all        p_first
-------------  ----------   ----------   ----------
MV_DEF         first_rows     all_rows   first_rows
MV_ALL           all_rows     all_rows     all_rows
MV_FIRST       first_rows   first_rows   first_rows
MV_ALL_FIRST     all_rows     all_rows     all_rows
MV_FIRST_ALL   first_rows   first_rows   first_rows

The setting of optimizer_mode came from the query:

SELECT e.value as optimizer_mode, c.sql_id, substr(c.sql_text,1,100) as sql
  FROM v$sql c 
  LEFT JOIN v$sql_optimizer_env e 
    ON e.sql_id = c.sql_id ANd e.name = 'optimizer_mode'
 WHERE regexp_like(c.sql_text, 'BYPASS.*(v_def|v_all|v_first)');

So, I need to protect the mviews from the database setting FIRST_ROWS, right? I can do this either in the PL/SQL procedure that refreshes the mviews with an ALTER SESSION statement, hoping that nobody else will ever refresh the mviews directly. Or I change the queries of the mviews, adding a hint /*+ ALL_ROWS */, right?

materialized-views oracle11g oracle
1 Answers

You are correct, the precedence for optimizer settings, in order of increasing priority, is:

  1. System parameter
  2. Session setting
  3. Query block level hint
  4. Statement level or parent query block level hint

If the /*+ ALL ROWS*/ hint is set in the outer-most query, that hint will override other hints and settings.

How do we prove the precedence rules are true?

Although I can't find a clear reference to the above rules in the official documentation, most of the the rules are fairly obvious. The first three rules make sense and we've probably all seen them in action before. It makes sense that configuration is set and applied at a high-level and then optionally overridden at a more low-level: first for the entire system, then for a specific session, and finally for a single query.

The unusual precedence is the last one, where a high-level statement hint overrides a low-level query block hint. Luckily, we can use the 19c hint report to demonstrate that this rule is true.

Simple hint test

The following test case shows the FIRST_ROWS hint being used and showing up in the "Hint Report" section of the execution plan.

--drop table test_table;
create table test_table(a number);

explain plan for select /*+ first_rows */ * from test_table;

select * from table(dbms_xplan.display(format => 'basic +hint_report'));

Plan hash value: 3979868219
 
----------------------------------------
| Id  | Operation         | Name       |
----------------------------------------
|   0 | SELECT STATEMENT  |            |
|   1 |  TABLE ACCESS FULL| TEST_TABLE |
----------------------------------------
 
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
 
   0 -  STATEMENT
           -  first_rows

Parent hint overrides child hint

Although you've already created a test case where an ALL_ROWS hint in the parent query block overrides the FIRST_ROWS hint in a child block, the following test case makes it clear that that behavior is not just an accident. The "Hint Report" very clearly explains that "first_rows / hint overridden by another in parent query block".

explain plan for select /*+ all_rows */ * from (select /*+ first_rows */ * from test_table);

select * from table(dbms_xplan.display(format => 'basic +hint_report'));

Plan hash value: 3979868219
 
----------------------------------------
| Id  | Operation         | Name       |
----------------------------------------
|   0 | SELECT STATEMENT  |            |
|   1 |  TABLE ACCESS FULL| TEST_TABLE |
----------------------------------------
 
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (U - Unused (1))
---------------------------------------------------------------------------
 
   0 -  STATEMENT
         U -  first_rows / hint overridden by another in parent query block
           -  all_rows

While this answer isn't a definitive proof of the behavior, I believe that it is sufficient evidence for us to feel confident that using the ALL_ROWS hint in the outer-most query will always work.

user_4091720
Related