How make data transfomation faster?

From: <grrey_at_mail.ru>
Date: 1 Feb 2006 05:16:02 -0800
Message-ID: <1138799762.846622.32910@o13g2000cwo.googlegroups.com>

Hi all.
There is a table with about 3,000,000 recs in a month:

create table Log (

User_id		number(12),
Date_time	date,
kind		number(1)  --1 for start_date, 2 for finish_date, 3 for

start_date

cancel, 4 for finish date cancel
);

I need get this table:

create table stat(

User_id 	number(12),
Start_Date	date,
Finish_Date	date);

Now making it that way:

create index stat_indx on stat(User_id,finish_date);

begin

  for rec in (select * from log) loop

    if rec.kind=1 then

      insert into stat(user_id,start_date)
      values (rec.user_id,rec.date_time);

    end if;

    if rec.kind=2 then

      update stat
         set finish_date=rec.Date_time
       where user_id=rec.user_id
         and finish_date is null;

    end if;

    if rec.kind=3 then

      delete from stat
       where user_id=rec.user_id
         and finish_date is null;

    end if;

    if rec.kind=4 then

      update stat
         set finish_date=null
       where user_id=rec.user_id
         and finish_date = (select max(finish_date) from stat
                             where user_id=rec.user_id);

    end if;

  end loop;

end;

but it's too slow. How it could be done faster? I'v tried TABLE OF, but it takes too much memory.

PS. Sorry, if there was that post already. I just don't know how to find it. If anyone knows that, please, tell me. Received on Wed Feb 01 2006 - 07:16:02 CST