Home » SQL & PL/SQL » SQL & PL/SQL » enhancing performance of join (oracle 10g)
enhancing performance of join [message #587589] Mon, 17 June 2013 04:58 Go to next message
enxsnx
Messages: 1
Registered: June 2013
Junior Member
Hi Guys,
I just registered this site in order to share and get some help for my oracle sql queries.

I have a table which is updated with new records in each 15minutes from network. In order to get the accurate information I must do some arithmethic calculations on some variables.

For example, if the value for column A is 10 @9.45AM and 15 for @10AM the real result should be 15-10=5 for 10AM; because the values are cumulative so I need to subtract.
Similar to this I have same operations for 9 more attributes in my table as well.
In order to handle this I used view and did necessary operations by using joining table like this (table name is Operations lets say and id is Primary Key)

create view ....
...
from Operations current, Operations prev
where current.datetime(+) = prev.datetime - 1 / 96
and current.id = prev.id

When I use this view, the simple select query takes about 15min since I have 25GB record for this table.
What can I use instead of this join and solve cumulative values issue?

Regards

Report message to a moderator

Re: enhancing performance of join [message #587591 is a reply to message #587589] Mon, 17 June 2013 05:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Welcome to the forum.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel

Report message to a moderator

Re: enhancing performance of join [message #587644 is a reply to message #587589] Mon, 17 June 2013 12:04 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Why did you not mention the column list in your view DDL? What about the DDL for the table?

If you are talking about the performance of views then it is necessary to know a lot of details. Before you post again(following the forum guidelines), have a look at the explanation by T.Kyte on view tuning to have a better understanding.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1455263156298

Report message to a moderator

Re: enhancing performance of join [message #587646 is a reply to message #587644] Mon, 17 June 2013 12:41 Go to previous message
Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
Why did you not mention the column list in your view DDL? What about the DDL for the table?


As I already told to you this OUR (moderators) job NOT yours.
Refrain to post these sentences (above all when it has already been asked).
And do NOT answer to this post, this is boring in the end.
You are wrong, full stop!

Regards
Michel

Report message to a moderator

Previous Topic: update issue
Next Topic: order by
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Aug 28 05:42:26 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.