Home » SQL & PL/SQL » SQL & PL/SQL » Find values in other records of a table
Find values in other records of a table [message #184297] Wed, 26 July 2006 01:23 Go to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
Hi,

The code below is very slow. I want to find the xvalue_6m, which is the xvalue 6 month ago.

(The problem in my data set is, that there are several xdate missing. I think that the lead / lag function does not work in that case, I need to find the xvalue which belongs exactly to the xdate_6m)


select a.xdate,
       ( select b.xvalue,
         from tab b
         where a.xdate_6m = b.xdate) as xvalue_6m
from tab a


Is there a faster solution in oracle with 'partition over'?


Thanks for your help
Stefan
Re: Find values in other records of a table [message #184304 is a reply to message #184297] Wed, 26 July 2006 01:51 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
did you try using self-join?
Re: Find values in other records of a table [message #184309 is a reply to message #184304] Wed, 26 July 2006 02:02 Go to previous messageGo to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
Yes, but the code is slow aswell

select a.xdate,
       b.xvalue
from tab a left join tab b
where a.xdate_6m = b.xdate_6m


I'm looking for a faster solution. Is there any?

Thanks
Stefan
Re: Find values in other records of a table [message #184318 is a reply to message #184309] Wed, 26 July 2006 02:32 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Just to check, you do have an index on xdate_6m (or xdate_6m, xdate) for that second query, don't you.
Re: Find values in other records of a table [message #184319 is a reply to message #184318] Wed, 26 July 2006 02:33 Go to previous messageGo to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
yes
Re: Find values in other records of a table [message #184323 is a reply to message #184309] Wed, 26 July 2006 02:47 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Just an interruption ..


select a.xdate,
       ( select b.xvalue,
         from tab b
         where a.xdate_6m = b.xdate) as xvalue_6m
from tab a


IS NOT EQUAL TO


select a.xdate,
       b.xvalue
from tab a left join tab b
where a.xdate_6m = b.xdate_6m


It should be ..

select a.xdate,
       b.xvalue
from tab a  INNER  join tab b
ON  a.xdate_6m =b.XDATE 


May be a typo ...

Thumbs Up
Rajuvan.




Re: Find values in other records of a table [message #184324 is a reply to message #184323] Wed, 26 July 2006 02:50 Go to previous messageGo to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
Yes you are right, sorry fot that!
But nevertheless, isn't there a faster solution?

Thanks
Stefan
Re: Find values in other records of a table [message #184326 is a reply to message #184324] Wed, 26 July 2006 02:54 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
With the indexes, this should be pretty quick.

How much data is there in the two tables?

How long is it taking?

Can you post an Explain plan.
Previous Topic: Problem with query
Next Topic: Query is extremely slow (URGENT HELP NEEDED)
Goto Forum:
  


Current Time: Thu Dec 08 12:21:27 CST 2016

Total time taken to generate the page: 0.06038 seconds