Re: Multiple sort orders with one query

From: Mark C. Stock <mcstockX_at_Xenquery>
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

Original text of this message