Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> 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 - 12:58:15 CST