kick_tisho wrote:

> Hi,
>
> i have the following situation. Let's say i have the following
> sample procedure.
>
> CONTACTS_GET(:P_ORDER)
>
> In it i have a query like this
>
> FOR
> SELECT
> CASE :P_ORDER
> WHEN 1 THEN NAME ELSE NULL
> END,
> CASE :P_ORDER
> WHEN 2 THEN LASTUPDATED ELSE NULL
> END,
> ID
> FROM CONTACTS
> ORDER BY 1,2
>
> so, when i have :P_ORDER = 1 it will order by name, and when i
> have :P_ORDER = 2 it will order by lastupdated. The problem in this
> scenario is that it won't use the indexes.
>
> If somebody has a solution how to transform the query without using
> DSQL, to preserve the logic, and to have indexed order i'll be
> greatfull if he share it with me.
>

Having the ORDER BY using an index isn't always a good thing - a sort is
usually quite fast. Quite often, I make an effort to avoid using an index
for an ORDER BY.
You could also start with a test on P_ORDER, and have two different paths
depending on the value. You'll have more (duplicate) code, but you'll
avoid the CASE.


--
Aage J.