This discussion is archived
5 Replies Latest reply: Nov 30, 2009 2:37 AM by Aketi Jyuuzou RSS

evaluated order of Pivot and UnPivot in select statement

Aketi Jyuuzou Oracle ACE
Currently Being Moderated
My research which evaluated order of select statement is below.
 1 from
 2 where (Join condition)
 3 start with
 4 connect by
 5 where (filter of rows)
 6 group by
 7 having
 8 model
 9 select
10 order by
My question is Where Pivot clause and UnPivot clause ?

http://download.oracle.com/docs/cd/E16338_01/server.112/e10592/statements_10002.htm
  • 1. Re: evaluated order of Pivot and UnPivot in select statement
    Sven W. Guru
    Currently Being Moderated
    Can you order a specific column and this order influcences the order of the pivot columns?
    If yes, I guest pivot must be executed after the order by clause. But can't test it currently (no 11g at my fingertipps).
  • 2. Re: evaluated order of Pivot and UnPivot in select statement
    Lakmal Rajapakse Expert
    Currently Being Moderated
    Well the pivot is applied to a single table or an inline view or a view - so it will be like any other table or view in the from list - it will be done first
    select b.BUSINESS_OBJECT_CATEGORY_DESCR, a.c, a.d
    from 
     (select PAY_METHOD_TYPE_CODE, BUSINESS_OBJECT_CATEGORY_CODE   
      from target_odi.business_object_pay_methods)
     pivot (count(PAY_METHOD_TYPE_CODE) 
             for PAY_METHOD_TYPE_CODE in  ('D' as d, 'C' as c)) a ,  
    target_odi.business_object_categories  b
    where a.BUSINESS_OBJECT_CATEGORY_CODE = b.BUSINESS_OBJECT_CATEGORY_CODE
    So for the above SQL the pivot is first evaluated before the join between A and B.
  • 3. Re: evaluated order of Pivot and UnPivot in select statement
    730428 Guru
    Currently Being Moderated
    Provided that you can specify columns created by the PIVOT clause both in the select and in the Order By clause, I think the pivot must be executed before them:
    SQL> r
      1  select job, d10,d20,d30 from emp
      2  pivot (sum(sal) for deptno in (10 as D10, 20 as d20, 30 as d30))
      3* order by d20
    
    JOB              D10        D20        D30
    --------- ---------- ---------- ----------
    CLERK                       800
    CLERK                      1100
    MANAGER                    2975
    ANALYST                    3000
    ANALYST                    3000
    SALESMAN                              1600
    PRESIDENT       5000
    MANAGER         2450
    SALESMAN                              1500
    SALESMAN                              1250
    CLERK           1300
    MANAGER                               2850
    SALESMAN                              1250
    CLERK                                  950
    
    Selezionate 14 righe.
    
    
    Piano di esecuzione
    ----------------------------------------------------------
    Plan hash value: 1739977809
    
    -----------------------------------------------------------------------------
    | Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |      |    14 |   518 |     5  (40)| 00:00:01 |
    |   1 |  SORT ORDER BY       |      |    14 |   518 |     5  (40)| 00:00:01 |
    |   2 |   HASH GROUP BY PIVOT|      |    14 |   518 |     5  (40)| 00:00:01 |
    |   3 |    TABLE ACCESS FULL | EMP  |    14 |   518 |     3   (0)| 00:00:01 |
    -----------------------------------------------------------------------------
    Max
  • 4. Re: evaluated order of Pivot and UnPivot in select statement
    Aketi Jyuuzou Oracle ACE
    Currently Being Moderated
    thanks everyone I decided that T will install Oracle11g in my PC B-)
    And I will report my research :D
  • 5. Re: evaluated order of Pivot and UnPivot in select statement
    Aketi Jyuuzou Oracle ACE
    Currently Being Moderated
    OK I conclude that Pivot clause and UnPivot clause are evaluted in from clause
    select * from dual
    unpivot(vals1 for key1 in(dummy,dummy,dummy,dummy,dummy))
    unpivot(vals2 for key2 in(key1,key1))
    pivot(max(key2) for vals2 in('DUMMY' as newDummy))
    pivot(max(newDummy) for vals1 in('DUMMY' as newDummy2)) a
    Join dual b
      on a.newDummy2 is null;
    
    NEWDUMMY2  DUMMY
    ---------  ------
    X          null

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points