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

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.

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).

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.

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

Aketi Jyuuzou

thanks everyone I decided that T will install Oracle11g in my PC B-)
And I will report my research

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