Re: Variable Order By clause

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 11 Aug 1999 15:34:48 GMT
Message-ID: <37bd9737.14285060_at_newshost.us.oracle.com>


A copy of this was sent to David Gilbert <nepherim42_at_hotmail.com> (if that email address didn't require changing) On Wed, 11 Aug 1999 10:05:23 -0400, you wrote:

>Thanks Jan, this works fine.
>
>Any idea why there is a restriction on using variables in the ORDER BY
>clause?
>

because a static sql query is parsed and the optimized plan for it is developed. If the items in the order by changed from run to run of the same query -- the plan itself would have to change. One time it might be able to use an index, the next time not. One time it might not have to do a table access by rowid to pick up extra data to then order on -- the next time not.

They would be 2 completely different queries accessing the data in very (possibly) different ways. that means you would have to use dynamic sql to get the plans to be compiled for each individual query.

using the decode as below allow it to come up with one plan (you always reference the same exact set of columns and the use of the function precludes using any indexes whatsoever).

>
>Jan H Malmberg wrote:
>>
>> Try something like
>>
>> SELECT
>> seq_no,
>> issue_type
>> FROM known_issues
>> WHERE system_id = 99
>> ORDER BY DECODE(ord1,1,seq_no,issue_type),
>> DECODE(ord2,2,issue_type,seq_no);
>>
>> It can of course only handle predefined situations but it avoids dynamic
>> sql.
>>

-- 
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
 
Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation
Received on Wed Aug 11 1999 - 17:34:48 CEST

Original text of this message