How to avoid self-joins ?

From: News <Contact_404_at_hotmail.com>
Date: 4 Nov 2005 04:16:06 -0800
Message-ID: <1131106566.847984.181090@g43g2000cwa.googlegroups.com>

Fast refresh is not possible when the same table is referenced more than once in a MV.

select CAPABILITY_NAME,possible,MSGTXT from mv_capabilities_table where mvname = <...>
shows :

CAPABILITY_NAME                P MSGTXT
------------------------------ -
---------------------------------------------
...
REFRESH_FAST_AFTER_INSERT      N multiple instances of the same table

or view

" Oracle9i Database Error Messages " says:

QSM-02007 multiple instances of the same table or view

Cause: The capability in question is not supported when the same table or view occurs more than once in the FROM list. Action: Re-phrase the query to avoid multiple instances of the same table

How please rephrase such query

SQL> select * from t1;

       C11 C12
---------- ----------

         1          1
         2          2
         3          3

3 rows selected.

SQL> select * from t3;

       C31 C32
---------- ----------

         1          1
         1          2
         2          3

3 rows selected.

SQL> SELECT *
   FROM t1 tab1, t1 tab2, t3 tab3
  WHERE tab3.c31 = tab1.c11
    and tab3.c32 = tab2.c12;
  2 3 4

       C11 C12 C11 C12 C31 C32
---------- ---------- ---------- ---------- ---------- ----------

         1          1          1          1          1          1
         1          1          2          2          1          2
         2          2          3          3          2          3

3 rows selected. Received on Fri Nov 04 2005 - 06:16:06 CST