Home » SQL & PL/SQL » SQL & PL/SQL » Order by in cursor (Oracle 9.2)
Order by in cursor [message #322496] Fri, 23 May 2008 14:37 Go to next message
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
Member
I have seen people writing cursors where they use an order by clause. What could possibly be the use/misuse of order by in a cursor expect for slowing down the process.
Unless if the cursor fetches N rows and want to do something with first N/2 row and something else with the remaining N/2 rows and so on.
order by performs sort operation and can be expensive where the cursor fetches large number of records.
Re: Order by in cursor [message #322499 is a reply to message #322496] Fri, 23 May 2008 15:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You use ORDER BY when you need to have the result in a specific order.

Regards
Michel
Re: Order by in cursor [message #322502 is a reply to message #322499] Fri, 23 May 2008 16:08 Go to previous messageGo to next message
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
Member
When you are going to process all the records from the cursor, does it really matter in what order you want it or get it
Re: Order by in cursor [message #322503 is a reply to message #322496] Fri, 23 May 2008 16:16 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
> does it really matter in what order you want it or get it
Having the records in a known & specific order can directly impact the amount of processing that needs to be done.
For example, say you have two tables (TABLE_A & TABLE_B) which share a common field; KEY_ID
& you want to know which KEY_ID values only in TABLE_A & which KEY_ID values only in TABLE_B.
By returning the rows in KEY_ID order allow you to make a single pass across each table to produce the desired results.
Any other approach increases the number of reads to produce the correct answer.
Re: Order by in cursor [message #322519 is a reply to message #322502] Sat, 24 May 2008 00:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Say you have to find the phone number of every of your last classmates, if the telephone directory was not ordered it would be rather long to find them, doesn't it?

Regards
Michel
Re: Order by in cursor [message #322590 is a reply to message #322519] Sat, 24 May 2008 11:57 Go to previous messageGo to next message
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
Member
So Michel, what you are saying is that order by clause is useful when we know the kind of data in the table and expect to reach the required record from cursor record set quickly by sorting so we don't have to loop through more number of records.Though I feel it is a special case.
Re: Order by in cursor [message #322591 is a reply to message #322590] Sat, 24 May 2008 12:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
This is just an example.
There are many cases you have to get the result in a specific order.
For instance, when you have to apply computing that is not commutative (the result depends on the order of the operations).
Something common financial computing or in warehouse.

Regards
Michel
Re: Order by in cursor [message #322901 is a reply to message #322591] Mon, 26 May 2008 14:56 Go to previous messageGo to next message
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
Member
I guess you are referring to analytical funtions
Re: Order by in cursor [message #322958 is a reply to message #322901] Mon, 26 May 2008 23:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Analytical is one example of this in SQL but it is also true in PL/SQL program.

Regards
Michel
Re: Order by in cursor [message #323164 is a reply to message #322590] Tue, 27 May 2008 09:08 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you were (for example) producing statements of a customers transactions, for all customers, wouldn't it make sense to order the transactions cursor by customer id and transaction date?

There are many, many times when the order that data is returned in is either useful or important.
Previous Topic: Trigger not firing
Next Topic: how to calculate sales for a week
Goto Forum:
  


Current Time: Fri Feb 07 07:56:08 CST 2025