Re: Help rewriting query

From: Andy Hassall <andy_at_andyh.co.uk>
Date: Tue, 15 Nov 2005 21:03:14 +0000
Message-ID: <hhikn1tu0fnmj87ad6ektk6rkms8mtvci5@4ax.com>

On 15 Nov 2005 04:12:30 -0800, "News" <Contact_404_at_hotmail.com> wrote:

>Could anyone help with rewriting queries below that are equivalent to
>avoid self-join or subquery in the second query so the materialized
>view fast refresh would be possible ?
>
>SELECT *
>FROM t1 tab1, t1 tab2, t3 tab3
>WHERE tab3.c31 = tab1.c11
>and tab3.c32 = tab2.c12;
>
>SELECT *
> FROM (t3 inner JOIN t1 ON t3.c31 = t1.c11)
>inner JOIN t1 tab2 ON t3.c32 = tab2.c12;
>
>If it's not possible, any solution other than duplicate tables ?

 Can't say I've done more than the bare basics with materialised views, so odds are there's something fundamentally wrong with this, but how about the sequence below?

 The DBMS_MVIEW.EXPLAIN_MVIEW procedure gives various hints on why a particular materialised view feature isn't possible. Here, it insisted that the ROWID is included in the select list.

 Columns are derived from the cryptic numeric column names in the query you posted, presuambly that was just an example.

SQL> create table t1 (c1 number primary key, c11 number, c12 number);

Table created.

SQL> create materialized view log on t1 with rowid;

Materialized view log created.

SQL> create table t3 (c1 number primary key, c31 number, c32 number);

Table created.

SQL> create materialized view log on t3 with rowid;

Materialized view log created.

SQL> begin

  2      delete from mv_capabilities_table;
  3      dbms_mview.explain_mview(
  4          'SELECT tab1.c1, tab1.c11, tab1.c12, ' ||
  5          '       tab2.c1 c1_2, tab2.c11 c11_2, tab2.c12 c12_2, ' ||
  6          '       tab3.c1 c1_3, tab3.c31 c31_3, tab3.c32 c32_3, ' ||
  7          '       tab1.rowid r1, tab2.rowid r2, tab3.rowid r3 ' ||
  8          'FROM   t1 tab1, t1 tab2, t3 tab3 ' ||
  9          'WHERE  tab3.c31 = tab1.c11 and tab3.c32 = tab2.c12 '
 10      );
 11      commit;

 12 end;
 13 /

PL/SQL procedure successfully completed.

SQL> select capability_name, possible
  2 from MV_CAPABILITIES_TABLE
  3 where capability_name like 'REFRESH_FAST%'   4 order by capability_name;

CAPABILITY_NAME                P
------------------------------ -
REFRESH_FAST                   Y
REFRESH_FAST_AFTER_ANY_DML     Y
REFRESH_FAST_AFTER_INSERT      Y
REFRESH_FAST_AFTER_ONETAB_DML  Y
REFRESH_FAST_PCT               N

SQL> create materialized view mv
  2 refresh fast
  3 as

  4  SELECT tab1.c1, tab1.c11, tab1.c12,
  5         tab2.c1 c1_2, tab2.c11 c11_2, tab2.c12 c12_2,
  6         tab3.c1 c1_3, tab3.c31 c31_3, tab3.c32 c32_3,
  7         tab1.rowid r1, tab2.rowid r2, tab3.rowid r3

  8 FROM t1 tab1, t1 tab2, t3 tab3
  9 WHERE tab3.c31 = tab1.c11 and tab3.c32 = tab2.c12;

Materialized view created.

SQL> select mview_name, refresh_method from user_mviews;

MVIEW_NAME                     REFRESH_
------------------------------ --------
MV                             FAST

-- 
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

Received on Tue Nov 15 2005 - 15:03:14 CST