Forum Stats

  • 3,839,777 Users
  • 2,262,536 Discussions
  • 7,901,054 Comments

Discussions

evaluated order of Pivot and UnPivot in select statement

Aketi Jyuuzou
Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
edited Nov 30, 2009 5:37AM in SQL & PL/SQL
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

Best Answer

  • Lakmal Rajapakse
    Lakmal Rajapakse Member Posts: 827 Silver Badge
    Answer ✓
    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.

Answers

  • Sven W.
    Sven W. Member Posts: 10,541 Gold Crown
    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).
    Sven W.
  • Lakmal Rajapakse
    Lakmal Rajapakse Member Posts: 827 Silver Badge
    Answer ✓
    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.
  • 730428
    730428 Member Posts: 2,087
    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
    730428
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    thanks everyone I decided that T will install Oracle11g in my PC B-)
    And I will report my research :D
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    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
This discussion has been closed.