Re: Multiple sort orders with one query
Date: Thu, 2 Jun 2005 08:19:33 -0400
Message-ID: <RZmdnQelhYrFZAPfRVn-qw_at_comcast.com>
"Noel" <tbal_at_go2.pll-l> wrote in message
news:d7mh01$i0s$1_at_inews.gazeta.pl...
> Uzytkownik B Rabbit napisal:
>
>> I have a report in Oracle Reports 6.0 that does the same query 3
>> different times, each with a different sort order. However, sometimes
>> the query takes a long time and running the query 3 times is
>> unacceptable. My question is, is there a way to just run the query one
>> time, then somehow change the sort order dynamically so that I can
>> output the query's results in the 3 different sort orders?
>>
>
> You might write output of query to a temporary table, and base your report
> on that table.
>
> But first try to optimize your query.
>
> --
> Noel
you can also use subquery factoring with UNIONs if
[_] your version supports it
[_] you can't or don't want to create a temporary table with the structure
of your report data source
[_] you can base the 3 sections of your report on a single query
example (note the need to generate a string-based sort key for each UNIONed
SELECT):
SQL> with emps as (
2 select empno, ename, hiredate, dname
3 from emp, dept
4 where emp.deptno = dept.deptno
5 )
6 select 1 as section, lpad(empno,4) as sortkey, emps.*
7 from emps
8 union all
9 select 2, ename, emps.*
10 from emps
11 union all
12 select 3, to_char(hiredate, 'yyyymmdd'), emps.*
13 from emps
14 order by 1, 2
15 /
SECTION SORTKEY EMPNO ENAME HIREDATE DNAME
---------- ---------- ---------- ---------- --------- -------------- 1 7369 7369 smith 17-DEC-80 RESEARCH 1 7499 7499 ALLEN 20-FEB-81 SALES 1 7521 7521 WARD 22-FEB-81 SALES 1 7566 7566 JONES 02-APR-81 RESEARCH 1 7654 7654 MARTIN 28-SEP-81 SALES 1 7698 7698 BLAKE 01-MAY-81 SALES 1 7782 7782 CLARK 09-JUN-81 ACCOUNTING 1 7788 7788 SCOTT 19-APR-87 RESEARCH 1 7839 7839 KING 17-NOV-81 ACCOUNTING 1 7844 7844 TURNER 08-SEP-81 SALES 1 7876 7876 ADAMS 23-MAY-87 RESEARCH 1 7900 7900 JAMES 03-DEC-81 SALES 1 7902 7902 FORD 03-DEC-81 RESEARCH 1 7934 7934 MILLER 23-JAN-82 ACCOUNTING 2 ADAMS 7876 ADAMS 23-MAY-87 RESEARCH 2 ALLEN 7499 ALLEN 20-FEB-81 SALES 2 BLAKE 7698 BLAKE 01-MAY-81 SALES 2 CLARK 7782 CLARK 09-JUN-81 ACCOUNTING 2 FORD 7902 FORD 03-DEC-81 RESEARCH 2 JAMES 7900 JAMES 03-DEC-81 SALES 2 JONES 7566 JONES 02-APR-81 RESEARCH 2 KING 7839 KING 17-NOV-81 ACCOUNTING 2 MARTIN 7654 MARTIN 28-SEP-81 SALES 2 MILLER 7934 MILLER 23-JAN-82 ACCOUNTING 2 SCOTT 7788 SCOTT 19-APR-87 RESEARCH 2 TURNER 7844 TURNER 08-SEP-81 SALES 2 WARD 7521 WARD 22-FEB-81 SALES 2 smith 7369 smith 17-DEC-80 RESEARCH 3 19801217 7369 smith 17-DEC-80 RESEARCH 3 19810220 7499 ALLEN 20-FEB-81 SALES 3 19810222 7521 WARD 22-FEB-81 SALES 3 19810402 7566 JONES 02-APR-81 RESEARCH 3 19810501 7698 BLAKE 01-MAY-81 SALES 3 19810609 7782 CLARK 09-JUN-81 ACCOUNTING 3 19810908 7844 TURNER 08-SEP-81 SALES 3 19810928 7654 MARTIN 28-SEP-81 SALES 3 19811117 7839 KING 17-NOV-81 ACCOUNTING 3 19811203 7902 FORD 03-DEC-81 RESEARCH 3 19811203 7900 JAMES 03-DEC-81 SALES 3 19820123 7934 MILLER 23-JAN-82 ACCOUNTING 3 19870419 7788 SCOTT 19-APR-87 RESEARCH 3 19870523 7876 ADAMS 23-MAY-87 RESEARCH
42 rows selected.
Execution Plan
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=42 Bytes=1596) 1 0 TEMP TABLE TRANSFORMATION
2 1 LOAD AS SELECT 3 2 MERGE JOIN (Cost=6 Card=14 Bytes=476) 4 3 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (TABLE) (Cost=2 Card=4 Bytes=52) 5 4 INDEX (FULL SCAN) OF 'PK_DEPT' (INDEX (UNIQUE)) (Cost=1 Card=4) 6 3 SORT (JOIN) (Cost=4 Card=14 Bytes=294) 7 6 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=294) 8 1 SORT (ORDER BY) (Cost=6 Card=42 Bytes=1596) 9 8 UNION-ALL 10 9 VIEW (Cost=2 Card=14 Bytes=532) 11 10 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6611_3F8E71B' (TABLE (TEMP)) (Cost=2 Card=14 Byt 12 9 VIEW (Cost=2 Card=14 Bytes=532) 13 12 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6611_3F8E71B' (TABLE (TEMP)) (Cost=2 Card=14 Byt 14 9 VIEW (Cost=2 Card=14 Bytes=532) 15 14 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6611_3F8E71B'(TABLE (TEMP)) (Cost=2 Card=14 Byt
++ mcs Received on Thu Jun 02 2005 - 14:19:33 CEST