Forum Stats

  • 3,839,755 Users
  • 2,262,532 Discussions
  • 7,901,052 Comments

Discussions

Categories

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
· Share on TwitterShare on Facebook

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.
    · Share on TwitterShare on Facebook

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.
    · Share on TwitterShare on Facebook
  • 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.
    · Share on TwitterShare on Facebook
  • 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
    · Share on TwitterShare on Facebook
  • 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
    · Share on TwitterShare on Facebook
  • 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
    · Share on TwitterShare on Facebook
This discussion has been closed.