Sorting Cursor Results

[ninfan]

Does anyone know if there is a way to sort the results of a cursor? When I include an ORDER BY statement in the cursor SQL, it seems to be ignored. The records seem to be returned in the physical order that they occur in the database. This is not necessarily the order they were entered or any other logical order.

In this particular case, it is important to me that I be able to process the cursor results in a certain order.

By the way, I'm using Oracle 8i. Thank you for any thoughts on this issue.

 

[kundan]

Hi Ninfan,
Is it the case that Order By never works the way you want it to work or does it work exactly opposite way then expected. I guess it will work exactly the opposite way.
Here's my explanation which can be totally wrong so please confirm.
Life of a Cursor:
(1) Declare
(2) Open
(3) Fetch INTO
(4) Close
Lets assume we have data (physical) as Emp_id {3,2,1,4}
In (1) you said Order By Emp_ID
In (2) when you open the Cursor the the data is retrived as Emp_Id {1,2,3,4}
In (3) it will fetch you the data as Emp_id {4,3,2,1} -- kind of STACK operation!
Cursor will return the last retrived data first.

Therefore if you want in Order by Emp_ID, then you will have to give ORDER BY EMP_ID DESC

 

[stevecal]

You could go lateral at this one. Make a copy table and insert the contents of the source table into the copy table using a SELECT with an appropriate ORDER BY clause. Your cursor should then be applied to the copy table...

 

[ninfan]

Thank you Kundan and Stevecal for the ideas. I will try the sort descending and see what impact it has. That is a great idea. If that doesn't go the way I want I'll go with Plan B and insert my results into a working table before I perform my operations.

Thanks again.

 

[sem]

1. Order by REALLY means ORDER BY, so order by emp_id returns (1,2,3,4);
2. It does not matter in wich order you insert data: Oracle DOES NOT GUARANTEE that you retreive data in the same order it was inserted;
3. The ORDER BY statement in cursor CAN NOT be ignored, just check it thoroughly. Maybe the problem in you NLS, but if you provide complete expression, I'll try to help you

 

[MikeJones]

I have to agree with sem here, points 2 & 3 show that there must be something up with your code so that the order by is not being used as it wants to be, post your code!

On another note you can get oracle to order your output without actually using an order by statement (Sweet!) but you will need to use an index and the index_asc hint.

As a brief explaination of why this will work an index is an ordered set (although oracle no longer garentees that in future it will be sorted low high so the Index_asc hint must be used) so access via it (on the driving table) will ensure rows are calculated in an order.

HTH.

 

[sem]

To MikeJones:
As for using hints, it's a good idea if you have a full control on the database. If somebody drops index your query fails, so IMHO the better way is to let Oracle know both what (order by) and how (+index..)

 

[MikeJones]

Sem,

I disagree, someone dropping an index out of a system is as bad as dropping a table, either way the system is probably fairly knackered given that no matter how factually accurate a system is, if it is so slow as to be un queryable the data may well as not be there.

For that reason I would say that having control over which indexes you have does not constitue full DB control but full Application control. I would consider full DB control to be more general things which are common to all DB's rather than a specific DB.

I'd also have to say that using an index over the order by can, in some circumstances have huge performance boosts.Due to the cost of a sort operation. So if you always specify an order by you may not be getting the best from your Oracle DB.



Mike.

 

[sem]

Mike,

I agree that the speed always matters, but the result sometimes is more significant. Though under some circumstances the index mentioned in a hint is not used even while existing (choose+lack of statistics, invalid state). In this case it is up to DBA to find out the cause of the bad performance, not of the unpredictable results. As for using hints with order by, if everythig goes right - utilizing indexes eliminates need in sorts, otherwise ignoring it impacts JUST performance.
Dmytro.

P.S.
I'm not sure that our discussion helps ninfan in his trouble. I'm sorry for starting it so suggest to suspend it untill ninfan's update.