Re: How make data transfomation faster?

grrey_at_mail.ru wrote:
[]
>
> 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.

yuck, that sinks some of my logic below. How yould you know which of the two starts matches a single finish (kind=2)???
>
> > (and honestly I don't understand what is supposed to happen when
> >kind=4)
>
> I described it in answer to sim.

I'm slightly more confused by your answer to sim. maybe a description of what this represents would help. Are these like session logs? library book checkouts? (in which case the iD would be the book/ and it can be check out a second time by the same user before he returns it? I'm breaking the laws of physics here so I must be way off in this example.)
>
> >-- 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.

Look closer, I was making an OUTER JOIN
or did I put the (+) on the wrong side??

> ....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.

The query is finding rows of kind = 1 that DO NOT have a matching row of kind = 2
IOW, making the SQL engine do some of the logic you coded as IF statements in PLSQL.
(but since your log table allows duplicates, the second INSERT will fail)

>
> >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.

Well the fact that there can be duplicates makes it more difficult but I again suggest you view the log table as really 4 different tables and try to proceed from there.

Thanks for the feedback BTW. Too often we see posts with questions, and after posting possible solutions, we get no replies from the original poster. So again thanks.

HTH,
   ed Received on Fri Feb 03 2006 - 00:11:22 CST