Home » SQL & PL/SQL » SQL & PL/SQL » Order By Taking time (Oracle)
Order By Taking time [message #658622] Thu, 22 December 2016 00:37 Go to next message
mskphani
Messages: 5
Registered: March 2016
Junior Member
Hello Team,

I have a one billion records in a table. I need to select them in an order and then perform the required calculations and insert them in another table. Since there is huge data order by clause is taking forever. We have partiotned the table and created partition based indexes.

For example
table x:

no v1
1 10
2 40
2 50
1 20
2 45
2 70

I have put the data in the table x in the order and then aggregate the data for the column 'v1' of the table x based on column 'no'. The final out put will contain two records for 1 and 2 as shown below.

1 30
2 205

Could you please help me in improving the performance of the query by using order by.

Thanks
Siva Mutyala


Re: Order By Taking time [message #658627 is a reply to message #658622] Thu, 22 December 2016 01:12 Go to previous messageGo to next message
Michel Cadot
Messages: 65969
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I have put the data in the table x in the order
There is no order in a heap table.

Quote:
Could you please help me in improving the performance of the query by using order by.
At least show us the query, are yu able to debug code you don't see?
For any performances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

As Blackswan already told to you in your previous topic:
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

Also always post your Oracle version, with 4 decimals, as solution depends on it.

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.

In the end, feedback to your (previous) topics and tell us how you solved your issue and thank people who took time to help you.

[Updated on: Thu, 22 December 2016 01:15]

Report message to a moderator

Re: Order By Taking time [message #658633 is a reply to message #658627] Thu, 22 December 2016 03:51 Go to previous messageGo to next message
cookiemonster
Messages: 13339
Registered: September 2008
Location: Rainy Manchester
Senior Member
Sounds suspiciously like you simply need:
INSERT INTO table2 (no, v1) SELECT no, sum(v1) from table1 group by no;

If that's not what you need then you need to supply a lot more information.

But - you do not need to insert data into a table in a particular order. The data will not be stored in the order of insertion, you can't make that happen. So relying on it is a waste of time.
Re: Order By Taking time [message #658707 is a reply to message #658633] Fri, 23 December 2016 11:13 Go to previous messageGo to next message
mskphani
Messages: 5
Registered: March 2016
Junior Member
Hi You are correct. There are multiple tables involved and moreover there are 1 billion records. In this case how can I handle the group by effectively ?
Re: Order By Taking time [message #658708 is a reply to message #658707] Fri, 23 December 2016 11:19 Go to previous message
Michel Cadot
Messages: 65969
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Doing it the best way.

Previous Topic: DB-LINK to mysql
Next Topic: How to bypass the selective rows from SQL Analytic function
Goto Forum:
  


Current Time: Fri Nov 16 18:56:29 CST 2018