You Asked

Hi,

I have the following sql and it creates the view successfully.
It's only selecting out of t1 right now, but it's a simplified version of my sql.
I will select out of the other tables once I get this figure out.

create materialized view mv
build immediate
refresh fast
  as
    select
      t1.AppId,
      t1.LineOfBusiness,
      t1.ClientId
    from
      t1,
      t2,
      t3,
      t4
    where
      t2.AppId = t1.AppId and
      t4.Brand(+) = t2.Brand and
      t4.Account(+) = t2.Account and
      t3.ProfileId(+) = t1.ProfileId
    group by
      t1.AppId,
      t1.LineOfBusiness,
      t1.ClientId;

However I also want to filter by

t1.ClientId > 0

When I add that filter like below I get

ORA-12015: cannot create a fast refresh materialized view from a complex query

create materialized view mv
build immediate
refresh fast
  as
    select
      t1.AppId,
      t1.LineOfBusiness,
      t1.ClientId
    from
      t1,
      t2,
      t3,
      t4
    where
      t2.AppId = t1.AppId and
      t4.Brand(+) = t2.Brand and
      t4.Account(+) = t2.Account and
      t3.ProfileId(+) = t1.ProfileId and
      t1.ClientId > 0
    group by
      t1.AppId,
      t1.LineOfBusiness,
      t1.ClientId;

How can I include that filter in the query?

Thanks!

and Connor said...

I dont think you'll be able to. You can see what possiblities are available for different permutations using DBMS_MVIEW


SQL> create table t1 as select * from dba_objects where object_id is not null;

Table created.

SQL> create table t2 as select * from dba_objects where object_id is not null;

Table created.

SQL> create table t3 as select * from dba_objects where object_id is not null;

Table created.

SQL>
SQL> alter table t1 add primary key ( object_id );

Table altered.

SQL> alter table t2 add primary key ( object_id );

Table altered.

SQL> alter table t3 add primary key ( object_id );

Table altered.

SQL>
SQL> create materialized view log on t1 with primary key, rowid, sequence  (owner,object_name,object_type) including new values;

Materialized view log created.

SQL> create materialized view log on t2 with primary key, rowid, sequence (last_ddl_time) including new values;

Materialized view log created.

SQL> create materialized view log on t3 with primary key, rowid, sequence (created) including new values;

Materialized view log created.

SQL>
SQL> delete MV_CAPABILITIES_TABLE;

18 rows deleted.

SQL>
SQL> begin
  2    dbms_mview.explain_mview(
  3    q'{select
  4        t1.owner,
  5        t1.object_name,
  6        t2.last_ddl_time,
  7        t3.created, count(*) cnt
  8      from
  9        t1,
 10        t2,
 11        t3
 12      where
 13        t2.object_id = t1.object_id and
 14        t3.object_id(+) = t2.object_id
 15      group by
 16        t1.owner,
 17        t1.object_name,
 18        t2.last_ddl_time,
 19        t3.created}');
 20  end;
 21  /

PL/SQL procedure successfully completed.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select capability_name,possible from mv_capabilities_table;

CAPABILITY_NAME                P
------------------------------ -
PCT                            N
REFRESH_COMPLETE               Y
REFRESH_FAST                   Y
REWRITE                        Y
PCT_TABLE                      N
PCT_TABLE                      N
PCT_TABLE                      N
REFRESH_FAST_AFTER_INSERT      Y
REFRESH_FAST_AFTER_ONETAB_DML  Y
REFRESH_FAST_AFTER_ANY_DML     Y
REFRESH_FAST_PCT               N
REWRITE_FULL_TEXT_MATCH        Y
REWRITE_PARTIAL_TEXT_MATCH     Y
REWRITE_GENERAL                Y
REWRITE_PCT                    N
PCT_TABLE_REWRITE              N
PCT_TABLE_REWRITE              N
PCT_TABLE_REWRITE              N

18 rows selected.

SQL>
SQL>
SQL> delete MV_CAPABILITIES_TABLE;

18 rows deleted.

SQL>
SQL> begin
  2    dbms_mview.explain_mview(
  3    q'{select
  4        t1.owner,
  5        t1.object_name,
  6        t2.last_ddl_time,
  7        t3.created, count(*) cnt
  8      from
  9        t1,
 10        t2,
 11        t3
 12      where
 13        t2.object_id = t1.object_id and
 14        t3.object_id(+) = t2.object_id
 15        and t1.object_type = 'TABLE'
 16      group by
 17        t1.owner,
 18        t1.object_name,
 19        t2.last_ddl_time,
 20        t3.created}');
 21  end;
 22  /

PL/SQL procedure successfully completed.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select capability_name,possible from mv_capabilities_table;

CAPABILITY_NAME                P
------------------------------ -
PCT                            N
REFRESH_COMPLETE               Y
REFRESH_FAST                   N
REWRITE                        Y
PCT_TABLE                      N
PCT_TABLE                      N
PCT_TABLE                      N
REFRESH_FAST_AFTER_INSERT      N
REFRESH_FAST_AFTER_ONETAB_DML  N
REFRESH_FAST_AFTER_ANY_DML     N
REFRESH_FAST_PCT               N
REWRITE_FULL_TEXT_MATCH        Y
REWRITE_PARTIAL_TEXT_MATCH     Y
REWRITE_GENERAL                Y
REWRITE_PCT                    N
PCT_TABLE_REWRITE              N
PCT_TABLE_REWRITE              N
PCT_TABLE_REWRITE              N

18 rows selected.

SQL>
SQL>

Perhaps look at a cascading set, eg

SQL> create materialized view mv
  2  build immediate
  3  refresh fast
  4    as
  5  select
  6        t1.owner,
  7        t1.object_name,
  8        t2.last_ddl_time,
  9        t3.created, count(*) cnt
 10      from
 11        t1,
 12        t2,
 13        t3
 14      where
 15        t2.object_id = t1.object_id and
 16        t3.object_id(+) = t2.object_id
 17      group by
 18        t1.owner,
 19        t1.object_name,
 20        t2.last_ddl_time,
 21        t3.created
 22  /

Materialized view created.

SQL>
SQL> create index mv_ix on mv ( owner ,object_name, last_ddl_time , created);

Index created.

SQL> alter table mv add constraint mv_pk primary key ( owner ,object_name, last_ddl_time , created) deferrable;

Table altered.

SQL> create materialized view log on mv with rowid, primary key, sequence including new values;

Materialized view log created.

SQL> create materialized view mv1
  2  build immediate
  3  refresh fast
  4    as select * from mv where owner = 'SYSTEM';

Materialized view created.

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Elaborate reasons

A reader, January 23, 2017 - 8:25 am UTC

Please elaborate. Why oracle consider adding a filter on a driving table consider it as complex qry for a fast refresh?

January 23, 2017 - 12:17 pm UTC

It just does. Thats *why* we have the mv_capabilities_table to allow assessment of what's possible and what's not.

From the docs:

"When Fast Refresh is Possible

Not all materialized views may be fast refreshable. Therefore, use the package DBMS_MVIEW.EXPLAIN_MVIEW to determine what refresh methods are available for a materialized view.

If you are not sure how to make a materialized view fast refreshable, you can use the DBMS_ADVISOR.TUNE_MVIEW procedure, which provides a script containing the statements required to create a fast refreshable materialized view."

Thank you Connor

A reader, January 23, 2017 - 6:24 pm UTC

Fast Refresh materialized view filter help

David Martirosyan, January 25, 2017 - 8:08 pm UTC

Thanks Connor,

Actually I think I can start with a fast refresh mview instead of t1 with the filter applied like below.
I have left out the create mat view, table pk, and table logs parts.

select * from t1 where object_type = 'TABLE'

Then use that mview for the final fast refresh mview instead of t1.

Do you think that will work?

January 26, 2017 - 11:50 pm UTC

Sounds plausible

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library

Breadcrumb

Question and Answer