materialized view has some limitations in my case: 1. Overhead of logging (to the mlog) in order to do fast refresh , (Direct insert reduce this overhead , but this is a limitation) 2. We are using PEL (partition exchange ) method to load data - this may cause stale in the MV . 3. MV - must be in the same time range of the source table , and we have to keep the summarised data for longer period. ( I could use consider fresh when doing a drop parititon , but this will complicate the process. ________________________________ From: Johan Eriksson [mailto:johan.eriksson@xxxxxxxxxxxx] Sent: Wed 11/30/2005 11:30 AM To: Amihay Gonen Cc: oracle-l@xxxxxxxxxxxxx Subject: Re: Temporary table vs pl\sql table Hi when reading your post it sounds like you could benefit from using a materialized view instead? /johan On Wed, 2005-11-30 at 10:42 +0200, Amihay Gonen wrote: > Hi , I've a process which need to do a lot of processing (summaries) > on a large set of data. > > The main flow is as follow: > > copy the new rows since last run (up to defined row count) to a > temporary table. > Run various selects with group by on the temporary table and > merge them into summary table. > > I wander (I will conduct some tests ofcourse ) what type of temporary > table will be better: > > 1) create ... temporary table or ... > 2) create type test is table of > > The first option has more i/o and go throw the buffer cache , but > doesn't consume a lot of memory > the second option doesn't has I/O but consume more memory for the PGA. > > Any ideas ? > > >