5 Replies Latest reply: Nov 30, 2009 4:37 AM by Aketi Jyuuzou RSS

    evaluated order of Pivot and UnPivot in select statement

    Aketi Jyuuzou
      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.
          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
            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
              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
                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
                  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