| Order By Taking time [message #658622] |
Thu, 22 December 2016 00:37  |
 |
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  |
 |
Michel Cadot
Messages: 68645
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 |
cookiemonster
Messages: 13920
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.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
