ANSI & ORACLE MV - still a long way to go..

Anything new is always interesting and so is the my current project at a client place - which is sort of hybrid between Transactional and reporting system. Thanks to this project, I got a break from traditional TUNING tasks and am over-working on catching up with datawarehousing (DW) techniques & implementing them.

Here is something interesting what I figured last week (thanks to a question from client's dev team)..

-- Create 3 tables with 10 rows each
-- & add Primary keys

SQL> Create table test_tb1 as
2 select rownum c1, owner c2 from dba_objects where rownum<11
3 /

Table created.

SQL> alter table test_tb1 add (
2 constraint test_tb1_pk primary key(c1)
3 );

Table altered.

SQL> Create table test_tb2 as
2 select rownum c1, object_name c2 from dba_objects where rownum<11
3 /

Table created.

SQL> alter table test_tb2 add (
2 constraint test_tb2_pk primary key(c1)
3 );

Table altered.

SQL> Create table test_tb3 as
2 select rownum c1, object_type c2,created from dba_objects where rownum<11
3 /

Table created.

SQL> alter table test_tb3 add (
2 constraint test_tb3_pk primary key(c1)
3 )
4 /

Table altered.

-- this exercise is about MATERIALIZED VIEWS..so to have FAST REFRESHING (aka incremental refreshing) Materialized view implemented , we should first have MV LOG created on base tables.

--So here we create them

SQL> ed
Wrote file afiedt.buf

1 Create materialized view log on test_tb1
2 with primary key,ROWID,sequence
3* (c2) including new values
SQL> /

Materialized view log created.

SQL> ed
Wrote file afiedt.buf

1 Create materialized view log on test_tb2
2 with primary key,ROWID,sequence
3* (c2) including new values
SQL> /

Materialized view log created.

SQL> ed
Wrote file afiedt.buf

1 Create materialized view log on test_tb3
2 with primary key,ROWID,sequence
3* (c2,created) including new values
SQL> /

Materialized view log created.

-- Now that we have base tables and MV logs ready, lets try creating a Materialized view..(using ANSI syntax)

SQL> ed
Wrote file afiedt.buf

1 Create materialized view test_mv1
2 refresh fast on demand
3 enable query rewrite
4 as
5 select t1.rowid t1_rowid
6 , t2.rowid t2_rowid
7 , t3.rowid t3_rowid
8 , t1.c2 owner
9 , t2.c2 name
10 , t3.c2 objtype
11 , t3.created
12 from test_tb1 t1
13 JOIN test_tb2 t2
14 ON t1.c1 = t2.c1
15 JOIN test_tb3 t3
16* on t1.c1 = t3.c1
SQL> /
JOIN test_tb3 t3
*
ERROR at line 15:
ORA-12015: cannot create a fast refresh materialized view from a complex query

-- that fails ..because optimizer thinks its a complex query (I am not good as ANSI..and not sure if there is any way to rewrite within ANSI standard itself )..but the point is , I didnt expect that to be considered as complex query


-- that was the problem reported from Dev team to me..and I just thought to test if it was ANSI SQL which was creating the problem. So here I rewrite the same SQL using traditional joins

SQL> ed
Wrote file afiedt.buf

1 Create materialized view test_mv1
2 refresh fast on demand
3 enable query rewrite
4 as
5 select t1.rowid t1_rowid
6 , t2.rowid t2_rowid
7 , t3.rowid t3_rowid
8 , t1.c2 owner
9 , t2.c2 name
10 , t3.c2 objtype
11 , t3.created
12 from
13 test_tb1 t1,
14 test_tb2 t2,
15 test_tb3 t3
16 Where
17 t1.c1 = t2.c1
18* and t1.c1 = t3.c1
SQL> /

Materialized view created.

-- WORKS!! Thats surprising..


SQL> drop materialized view test_mv1;

Materialized view dropped.

-- Lets try the same with outer joins too

SQL> ed
Wrote file afiedt.buf

1* drop materialized view test_mv1
SQL> Create materialized view test_mv1
2 refresh fast on demand
3 enable query rewrite
4 as
5 select t1.rowid t1_rowid
6 , t2.rowid t2_rowid
7 , t3.rowid t3_rowid
8 , t1.c2 owner
9 , t2.c2 name
10 , t3.c2 objtype
11 , t3.created
12 from
13 test_tb1 t1,
14 test_tb2 t2,
15 test_tb3 t3
16 Where
17 t1.c1 = t2.c1
18 and t1.c1 = t3.c1(+)
19 /

Materialized view created.

-- Selecting from MV

SQL> select rownum,owner,objtype from test_mv1;

ROWNUM OWNER OBJTYPE
---------- ------------------------------ -------------------
1 SYS TABLE
2 SYS INDEX
3 SYS TABLE
4 SYS TABLE
5 SYS CLUSTER
6 SYS INDEX
7 SYS TABLE
8 SYS INDEX
9 SYS INDEX
10 SYS INDEX

10 rows selected.

SQL> select * from test_tb1;

C1 C2
---------- ------------------------------
1 SYS
2 SYS
3 SYS
4 SYS
5 SYS
6 SYS
7 SYS
8 SYS
9 SYS
10 SYS

10 rows selected.

-- Testing MV functionality by adding more data to base tables and
-- seeing if MV works fine.

SQL> insert into test_tb1 select 10+rownum c1 , 'NEW DATA' c2 from test_tb1
2 /

10 rows created.

SQL> select * from test_tb1;

C1 C2
---------- ------------------------------
1 SYS
2 SYS
3 SYS
4 SYS
5 SYS
6 SYS
7 SYS
8 SYS
9 SYS
10 SYS
11 NEW DATA
12 NEW DATA
13 NEW DATA
14 NEW DATA
15 NEW DATA
16 NEW DATA
17 NEW DATA
18 NEW DATA
19 NEW DATA
20 NEW DATA

20 rows selected.

SQL> commit;

Commit complete.

SQL> select rownum,owner,objtype from test_mv1;

ROWNUM OWNER OBJTYPE
---------- ------------------------------ -------------------
1 SYS TABLE
2 SYS INDEX
3 SYS TABLE
4 SYS TABLE
5 SYS CLUSTER
6 SYS INDEX
7 SYS TABLE
8 SYS INDEX
9 SYS INDEX
10 SYS INDEX

10 rows selected.

SQL> begin
2 dbms_mview.refresh(list=>'TEST_MV1',METHOD=>'f');
3 end;
4 /

PL/SQL procedure successfully completed.

SQL> select rownum,owner,objtype from test_mv1;

ROWNUM OWNER OBJTYPE
---------- ------------------------------ -------------------
1 SYS TABLE
2 SYS INDEX
3 SYS TABLE
4 SYS TABLE
5 SYS CLUSTER
6 SYS INDEX
7 SYS TABLE
8 SYS INDEX
9 SYS INDEX
10 SYS INDEX

10 rows selected.

SQL> insert into test_tb2 select 10+rownum c1, 'New data' c2 from test_tb2
2 /

10 rows created.

SQL> select * from test_tb2;

C1 C2
---------- -------------------------
1 ICOL$
2 I_USER1
3 CON$
4 UNDO$
5 C_COBJ#
6 I_OBJ#
7 PROXY_ROLE_DATA$
8 I_IND1
9 I_CDEF2
10 I_PROXY_ROLE_DATA$_1
11 New data
12 New data
13 New data
14 New data
15 New data
16 New data
17 New data
18 New data
19 New data
20 New data

20 rows selected.

SQL> commit;

Commit complete.

SQL> begin
2 dbms_mview.refresh(list=>'TEST_MV1',METHOD=>'f');
3 end;
4 /

PL/SQL procedure successfully completed.

SQL> select rownum,owner,objtype from test_mv1;

ROWNUM OWNER OBJTYPE
---------- ------------------------------ -------------------
1 SYS TABLE
2 SYS INDEX
3 SYS TABLE
4 SYS TABLE
5 SYS CLUSTER
6 SYS INDEX
7 SYS TABLE
8 SYS INDEX
9 SYS INDEX
10 SYS INDEX
11 NEW DATA
12 NEW DATA
13 NEW DATA
14 NEW DATA
15 NEW DATA
16 NEW DATA
17 NEW DATA
18 NEW DATA
19 NEW DATA
20 NEW DATA

20 rows selected.


-- works fine!!!

Moral: Though ANSI standard has been introduced in Oracle since 9.1 version, its always better to check back to traditional join syntax when sometimes you get unexpected error with sql/optimizer.