Re: How make data transfomation faster?

From: <grrey_at_mail.ru>
Date: 2 Feb 2006 15:04:24 -0800
Message-ID: <1138921464.245387.256340@g43g2000cwa.googlegroups.com>

to sim
>
>SELECT USER_ID,
> MAX(DECODE(KIND,1,DATE_TIME,NULL)) START_DATE,
> MAX(DECODE(KIND,2,DATE_TIME,NULL)) FINISH_DATE
>FROM LOG
>WHERE KIND IN (1,2)
>GROUP BY USER_ID
this won't work becouse this will retern last, not confirmed row for each user.
I think, I just wasn't clear with kinds.

kind=1 - is start
kind=2 - is finish
kind=3 - is start cancel. So if there were a row with kind=1 previosly

I should ignore it. In my procedure I just delete a row wich was inserted when kind was 1.
kind=4 - is finish cancel. Same as kind=3, but for finish. In my procedure I changing finish_date to null just for cancel previos update when kind was 2.

to robert
>Did you try to do it with four different SQL statements - one per "kind"
>(1 insert, 2 update, 1 delete)?

No I didn't. I was tried to find that startmens, but I couldn't. If you can help me, please, do it. With just 4 startments it will much faster.

to Ed
>
>hopefully these generic column names are just for your example.

yes they are.

>PL/SQL often is slower than SQL.

I understand that, but cant find SQL query for this situation.

>Note I'm making this critical assumption:
>there is never a repeated ID,kind row. IOW this never happens:
>ID datetime kind
>1 xxxxxxx 1
>1 yyyyyyy 1

This could be. One user can start more tan once.

> (and honestly I don't understand what is supposed to happen when
>kind=4)

I described it in answer to sim.

>-- for kind=1
>for rec in (select A.user_id,A.date_time from log A, log B
> where A.id=B.id(+) --- yes an outer join
> and A.kind=1 and B.kind IS NULL )
>INSERT INTO stat (user_id,start_date)
> values (rec.user_id,rec.date_time);
>--- note this one could be done in a single SQL statement, but I left
>it this way
>--- as a pattern to guide you for the other KINDs of records.

"B.kind IS NULL" - this couldn't be. kind always has a value. your select will not return any rows. And I actualy don't completely understand what are you trying to do here.

>better yet would be to change your data model to
>avoid this whole mess.

There is no way to change log table. So I have to change existing data to a beter model, what I'm actualy trying to do with beter model - stat table. Received on Thu Feb 02 2006 - 17:04:24 CST