| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Static ORDER BY Clause
A copy of this was sent to "Jeff" <janderson_at_iname.com>
(if that email address didn't require changing)
On Thu, 19 Aug 1999 00:40:45 -0700, you wrote:
>For performance reasons we are trying to rewrite all of our packages to use
>bind variables so that all of our queries are static. We have been able to
>do this everywhere except in the ORDER BY clause. According to Oracle
>Support we cannot use a bind variable in the ORDER BY clause. The
>application we are developing allows the user to select up to 2 columns out
>of 150 to sort by so we need to be able to change the columns used in the
>ORDER BY clause. Does anyone have any ideas on how we can allow our users to
>request different sort columns and still create a static query?
>
>Thanks,
>Jeff
>
>
Not optimally. the problem is that:
select * from T where ... ORDER BY c1;
might generate one plan (use an index with c1 on the leading edge so as to skip the sort) while:
select * from T where .... order by c2;
would generate a completely DIFFERENT plan (use a different index so as to order by c2 or if more applicable, use a different index to satisfy the where clause)...
also, consider if c1 and c2 are different datatypes -- they are really very different queries.
Once 'trick' you can use (but bear in mind, it'll NEVER use an index to sort) is something like:
create table t ( c1 varchar2(25), c2 date, c3 number );
select * from T where .... order by
decode( :bind_variable_1, 1, c1, 2, to_char(c2,'yyyymmddhh24miss'), 3,
to_number(c3,'000000000000000000000009'),decode( :bind_variable_2, 1, c1, 2, to_char(c2,'yyyymmddhh24miss'), 3,
to_number(c3,'000000000000000000000009')
That way, you set up your bind variables and if it is = 1, then we sort by c1. if = 2, then sort by c2 (but note we must coerce c2 -- a date -- into a string to keep everthing type compatible). if = 3, then sort by c3 (but note the coercing of c3 as well, to sort c3 as a string). This has some issues (like in that the above does not work well for negative numbers for example).
SQL> select to_char(-1,'000000009') from dual
2 union all
3 select to_char( 1,'000000009') from dual
4 order by 1;
TO_CHAR(-1
SQL> select -1 from dual union all select 1 from dual odrer by 1;
-1
----------
-1
1
so, your decode for numbers would have to be even more complex.
to get the optimal plan for your queries (ones that can use indexes when available to bypass a sort), you will NOT use this technique.
--
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 Fri Aug 20 1999 - 06:30:12 CDT
 |
 |