Can I use a Mat. View Like this ?

From: Robert <rchin_at_panix.com>
Date: Wed, 8 Dec 2004 11:05:05 -0500
Message-ID: <cp78n3$gcr$1@reader1.panix.com>

8.1.7.4/OLTP system

---

I have a process that does thing like this:

UPDATE my_gtt --<-- Global Temp Table

   SET c20 = calc_pkg.func20(tranx_id),
       c21 = calc_pkg.func21(tranx_id),
       c22 = calc_pkg.func22(tranx_id),
       c23 = calc_pkg.func23(tranx_id),
       c24 = calc_pkg.func24(tranx_id),
       c25 = calc_pkg.func25(tranx_id),
       c26 = calc_pkg.func26(tranx_id),
       ............

where those SQL functions look up several other tables. - SLOW but boss insists calc functions be maintained.

Can I create MV like this :

1)
create mat. view mv_pre_calc
as
select
  tranx_id,

  calc_pkg.func20(tranx_id) l_c21,
  calc_pkg.func21(tranx_id) l_c22,
  calc_pkg.func22(tranx_id) l_c23,
  calc_pkg.func23(tranx_id) l_c24,
  calc_pkg.func24(tranx_id) l_c25,
  calc_pkg.func25(tranx_id) l_c26,
  calc_pkg.func26(tranx_id) l_c27

  .....
from TBL_TRANSACTIONS --<-- has tranx_id as PK

2) create an PK/unique index on mv_pre_calc.tranx_id 3) Use this MV DIRECTLY to do correlated-update in my process above ?

thanks
robert Received on Wed Dec 08 2004 - 10:05:05 CST