Re: How make data transfomation faster?

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Sat, 4 Feb 2006 11:03:20 +0100
Message-ID: <43e47be8$0$17058$626a54ce@news.free.fr>

<grrey_at_mail.ru> a écrit dans le message de news: 1138799762.846622.32910_at_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.
|

Here's a query that might gives you a hint of how to achieve it in SQL:

SQL> select user_id, date_time, kind from log order by user_id, date_time   2 /
   USER_ID DATE_TIME KIND

---------- ----------- ----------
         1 02-FEB-2006          1
         1 04-FEB-2006          2
         2 25-JAN-2006          1
         2 28-JAN-2006          2
         2 30-JAN-2006          1
         2 31-JAN-2006          2
         3 28-JAN-2006          1
         3 29-JAN-2006          3
         3 30-JAN-2006          1
         3 01-FEB-2006          2
         4 27-JAN-2006          1
         4 28-JAN-2006          2
         4 29-JAN-2006          4
         4 30-JAN-2006          2
         5 15-JAN-2006          1
         5 16-JAN-2006          2
         5 17-JAN-2006          1
         5 18-JAN-2006          3
         5 19-JAN-2006          1
         5 20-JAN-2006          2
         5 21-JAN-2006          1
         5 22-JAN-2006          2
         5 23-JAN-2006          4
         5 24-JAN-2006          2
         5 25-JAN-2006          1
         5 26-JAN-2006          3
         5 27-JAN-2006          1
         5 28-JAN-2006          3
         5 29-JAN-2006          1
         5 30-JAN-2006          3
         5 31-JAN-2006          1
         5 01-FEB-2006          2
         5 02-FEB-2006          4
         5 03-FEB-2006          2
         6 04-FEB-2006          1

35 rows selected.

SQL> select user_id, start_time, end_time   2 from ( select user_id, start_time,

  3                lead(end_time)
  4                  over (partition by user_id
  5                        order by nvl(start_time,end_time))
  6                  end_time
  7         from ( select user_id,
  8                       case
  9                       when kind = 1 and
 10                            nvl(lead(kind)
 11                                  over (partition by user_id
 12                                        order by date_time),0) != 3
 13                         then date_time
 14                       end start_time,
 15                       case
 16                       when kind = 2 and
 17                            nvl(lead(kind)
 18                                  over (partition by user_id
 19                                        order by date_time),0) != 4
 20                         then date_time
 21                       end end_time
 22                from log )
 23         where start_time is not null
 24            or end_time is not null )

 25 where start_time is not null
 26 order by user_id, start_time
 27 /
   USER_ID START_TIME END_TIME

---------- ----------- -----------
         1 02-FEB-2006 04-FEB-2006
         2 25-JAN-2006 28-JAN-2006
         2 30-JAN-2006 31-JAN-2006
         3 30-JAN-2006 01-FEB-2006
         4 27-JAN-2006 30-JAN-2006
         5 15-JAN-2006 16-JAN-2006
         5 19-JAN-2006 20-JAN-2006
         5 21-JAN-2006 24-JAN-2006
         5 31-JAN-2006 03-FEB-2006
         6 04-FEB-2006

10 rows selected.

Regards
Michel Cadot Received on Sat Feb 04 2006 - 04:03:20 CST