Re: How make data transfomation faster?

grrey_at_mail.ru wrote:
> to Ed Prochak

Just dealing with the outer join for the moment.

[]
> >Look closer, I was making an OUTER JOIN
> >or did I put the (+) on the wrong side??
>
> 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
>
> This will never return any row becouse of kind could not be null! Outer
> join just allows you to see rows with null values in join columns, but
> there aren't any null value.

Opps, I did screw that up. That one is going to match with 3's and 4's as well.I should have written the longer query first.
>
> >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)
>
> Still this query won't work. And I have no idea what is right one.

Consider this query:

select A.user_id,A.date_time from log A
where A.kind=1 and

   A.id NOT IN (select B.id from log B

                       where B.kind=2 );

So fi the log table has
id date kind

1    1/1     1
1    1/5     2
2    1/2     1

then logically the pseduo table A has rows id date kind

1    1/1     1
2    1/2     1

and pseudo table B has rows:
id date kind
1 1/5 2

so the result of A.id not in B.id gives the final result id date kind
2 1/2 1

That can be slow. (at least it used to be slow) But it can be faster rewritten using the NOT EXISTS clause, or using the OUTER JOIN as I was trying to do. (Sorry I messed it up in my other posts.)

select A.user_id,A.date_time from log A, log B where A.kind=1 and

   A.id = B.id (+) and
   B.id IS NULL and B.kind(+) = 2 ;

Here the process is different, first is the join of A and B (speaking logically not necessarily how ORACLE might physically do it). The outer join is a table with 6 columns and 2 rows, (before applying the NULL condition:

A.id A.date A.kind B.id B.date B.kind

    1    1/1     1                 1    1/5     2
    2    1/2     1              null    null    null

Now the final where clause "B.id IS NULL" says keep only the nulls

A.id A.date A.kind B.id B.date B.kind

    1    1/1     1                 1    1/5     2
    2    1/2     1              null    null    null

so the first row is filtered out.

A.id A.date A.kind B.id B.date B.kind

    2 1/2 1 null null null

and the Select clause gives the columns we really want: A.id, A.date

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

The fact that the same id can create multiple transactions with both starts (1) and ends(2) makes it more difficult.  IF I can make the assumption that the kind 1 and 2 events follow strict rules about ordering, namely a 2must follow a 1 before ethere can be another 1 (ie, you must close the first credit before starting a second),
THEN it might just need a simple ordering clause.

If we add another start event for id 1 to the table: id date kind

1    1/1     1
1    1/5     2
2    1/2     1
1    1/6     1

and we add this condition to the where clause:  AND a.date < B.date.

Darn. we are close, but this isn't enough to handle multiples.

>
> I was tring do make any valuble result thinking that way, but coudn't
> find it (.

Well since the 1's and 2's process together, why not just put them in the PLSQL LOOP with a cursor query like
select * from log where kind in (1,2)

and then when processing the cancels, have a second loop with select * from log where kind in (3,4)

Basically that moves some of the IF's from your original posted code into the SELECT statement.
>
> >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.
>
> lol :). You thanks me becouse of I interest in my question. That's
> nice. Well, you are welcome :)).

You would be surprised by how many shoot and run questions appear here. Some folks think this is a chat room and when they see no response in a few minutes, they leave, never to return again.

> Actualy I realy appreciate your responces for my question. And thank
> you for your time, you spending here with me.

Well, I appreciate you helping shake a few cobwebs out of my feeble brain.
Sorry I messed up that outer join so badly. This queries I included this time should make things a little clearer. Received on Mon Feb 06 2006 - 13:03:08 CST