Complex materialized views? Try a table first

connor_mc_d's picture

Just a quick post today that arose from an AskTOM question a little while back. Over the years and across the evolution of various versions of the Oracle database, the amount of “power” you could pack into a CREATE TABLE statement as grown. For example, I can do a CREATE-TABLE-AS-SELECT, along with a partitioning clause, including an explicit CREATE INDEX command and add constraints all in one atomic operation.



SQL> create table t
  2    (id1,
  3     id2,
  4     owner,
  5     object_name,
  6     constraint t_pk primary key ( id1) using index
  7        ( create index t_pk on t ( id1, owner) )
  8    )
  9  partition by range ( id2 )
 10  (
 11    partition p1 values less than ( 100000 ),
 12    partition p2 values less than ( 200000 )
 13  )
 14  as
 15    select
 16      object_id id1,
 17      object_id id2,
 18      owner,
 19      object_name
 20  from dba_objects
 21  where object_id is not null;

Table created.

That’s pretty cool, but one of the most common times you will be writing DDL that includes a “select * from” as part of the DDL definition, is during the creation of materialized views, because the view text naturally will contain a select statement. Unfortunately, we are not quite as “generous” when it comes to accepting all of the various physical implementation options when it comes to DDL for materialized views. Trying to create a materialized view that matches our table T above is a struggle.


SQL> create materialized view t_mv
  2    (id1,
  3     id2,
  4     owner,
  5     object_name,
  6     constraint t_mv_pk primary key ( id1) using index
  7        ( create index t_mv_pk on t_mv ( id1, owner) )
  8    )
  9  partition by range ( id2 )
 10  (
 11    partition p1 values less than ( 100000 ),
 12    partition p2 values less than ( 200000 )
 13  )
 14  refresh complete on demand
 15  as
 16    select
 17      id1,
 18      id2,
 19      owner,
 20      object_name
 21  from t;
   constraint t_mv_pk primary key ( id1) using index
              *
ERROR at line 6:
ORA-00907: missing right parenthesis


SQL>
SQL> create materialized view t_mv
  2    (id1,
  3     id2,
  4     owner,
  5     object_name,
  6     constraint t_mv_pk primary key ( id1) using index
  7        ( create index t_mv_pk on t_mv ( id1, owner) )
  8    )
  9  refresh complete on demand
 10  partition by range ( id2 )
 11  (
 12    partition p1 values less than ( 100000 ),
 13    partition p2 values less than ( 200000 )
 14  )
 15  as
 16    select
 17      id1,
 18      id2,
 19      owner,
 20      object_name
 21  from t;
   constraint t_mv_pk primary key ( id1) using index
              *
ERROR at line 6:
ORA-00907: missing right parenthesis

When you encounter this limitation, don’t forget that one of the nice things you can do with materialized view creation is pre-create the underlying table with all of its flexibility:



SQL> create table t_mv
  2    (id1,
  3     id2,
  4     owner,
  5     object_name,
  6     constraint t_mv_pk primary key ( id1) using index
  7        ( create index t_mv_pk on t_mv ( id1, owner) )
  8    )
  9  partition by range ( id2 )
 10  (
 11    partition p1 values less than ( 100000 ),
 12    partition p2 values less than ( 200000 )
 13  )
 14  as
 15    select
 16      id1,
 17      id2,
 18      owner,
 19      object_name
 20  from t;

Table created.

And then once that it done, you can use the PREBUILT TABLE clause to create your materialized view which will now satisfy all of the underlying physical structure elements you desired.



SQL> create materialized view t_mv
  2  on prebuilt table
  3  refresh complete on demand
  4  as select
  5      id1,
  6      id2,
  7      owner,
  8      object_name
  9  from t;

Materialized view created.

SQL>
SQL>
To prevent automated spam submissions leave this field empty.