Re: Can I use a Mat. View Like this ?

Hi

Do you mean, take values in the MV, as the new values in my_gtt ?

I don't why not.
As those are pre-calculated in the MV you would save a lot of time.

Try not to fetch the same row from the same table repeatedly, as I think the functions in calc_pkg are doing

-- 
Syltrem

OpenVMS 7.3-1 + Oracle 8.1.7.4
http://pages.infinit.net/syltrem (OpenVMS related web site, en français)
---zulu is not in my email address---
"Robert" <rchin_at_panix.com> a écrit dans le message de
news:cp78n3$gcr$1_at_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

>

>