Posted by 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>
Learning is not a spectator sport 