Query Rewrite with materialized view based on remote objects

Query Rewrite with materialized view based on remote objects

This article is about different scenarios of query rewrite with materialized view created based on remote queries or objects

First a database link is created using query

create database link remotedb_link connect to scott identified by tiger using 'tns_entry_for_remotedb';

Can verify whether database link is valid using below query

select ename,dname,e.deptno from scott.emp e,dept@APPDBTSTS_SCOTT d
where e.deptno=d.deptno;

Now create a materialized view based on above query, which include remote object

SQL>  create materialized view APP_SCOTT_MV_remote
    enable query rewrite as
    select ename,dname,e.deptno from scott.emp e,dept@APPDBTSTS_SCOTT d
    where e.deptno=d.deptno;

Materialized view created.

Execute utlxrw.sql scrip to create a table named 'rewrite_table' which holds the output of 'explain_reqrite' procedure

@/u01/app/oracle/dbhome_1/rdbms/admin/utlxrw.sql

Table created.

Excecute explain_rewrite using lines mentioned under

declare
    qrytxt varchar2(100) := 'select ename,dname,e.deptno from scott.emp e,dept@APPDBTSTS_SCOTT d where e.deptno=d.deptno';
    begin
    dbms_mview.explain_rewrite(qrytxt,'APP_SCOTT_MV_remote','ID1');
    end;
    /

To check the output of above lines, query rewrite_table

select message from rewrite_table;

MESSAGE
--------------------------------------------------------------------------------
QSM-01150: query did not rewrite
QSM-01353: no query rewrite on remote objects unless integrity mode is set to st
ale_tolerated

Above error message shows query rewrite is disabled for remote object unless and to make query rewrite happen, parameter query_rewrite_integrity needs to be set as stale_tolerated which can be done at session level using statment here under

alter session set query_rewrite_integrity=stale_tolerated;

Rerun explain_rewrite

SQL> declare
    qrytxt varchar2(100) := 'select ename,dname,e.deptno from scott.emp e,dept@APPDBTSTS_SCOTT d where e.deptno=d.deptno';
    begin
    dbms_mview.explain_rewrite(qrytxt,'APP_SCOTT_MV_remote','ID1');
    end;
    /

PL/SQL procedure successfully completed.

SQL> select message from rewrite_table;

MESSAGE
--------------------------------------------------------------------------------

QSM-01151: query was rewritten
QSM-01209: query rewritten with materialized view, APP_SCOTT_MV_REMOTE, using te
xt match algorithm

Another finding during my pratice session for this article was an error
"QSM-01355:  multi-mv rewrite not possible in the presence of remote objects"

i.e. when trying "join back" scenario with remote materialized view using code below

declare
  2  qrytxt varchar2(100) := 'select empno,e.deptno from scott.emp e,dept@APPDBTSTS_SCOTT d where e.deptno=d.deptno';
  3  begin
  4  dbms_mview.explain_rewrite(qrytxt,'APP_SCOTT_MV_remote','ID1');
  5  end;
  6  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.89
SQL> select message from rewrite_table;

Got error message as:

MESSAGE
--------------------------------------------------------------------------------
QSM-01150: query did not rewrite
QSM-01082: Joining materialized view, APP_SCOTT_MV_REMOTE, with table, EMP, not
possible

QSM-01102: materialized view, APP_SCOTT_MV_REMOTE, requires join back to table,
EMP, on column, EMPNO

QSM-01355: multi-mv rewrite not possible in the presence of remote objects

I am unable to find any valid reason about it yet, if some one knows then I will appriciate your sharing.