Oracle 快速刷新实体化视图筛选器帮助

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;

t1.ClientId > 0

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;

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>

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.