今天在浏览oracle优化、调优相关的博客，无意中看到了oracle的pivot函数，学习了，为了以后复习，先收藏了，下面是转载的博客和相关博客。
转载博客：http://blog.itpub.net/17203031/viewspace-754807【请尊重原创，谢谢】
相关博客：http://5073392.blog.51cto.com/5063392/1536828
作为数据库应用开发人员，我们有很大的精力应付在处理各种各样的数据类型，展现各种维度的报表上面。
行转列、列转行是我们经常会遇到的“诡异”需求。标准SQL没有提供此类型操作的支持函数，早期Oracle的版本中，我们也只能通过复杂的变通手段实现。在Oracle11g中，这种情况改变了，我们可以利用pivot函数轻松实现行列互转并且聚合的查询需求。
1、环境准备
我们选择Oracle 11g进行实验。
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
创建数据表T，进行有选择的数据筛选。
SQL> create table t (owner varchar2(30), object_type varchar2(100), value number);
Table created
SQL> insert into t select owner, object_type, object_id from dba_objects where object_type in ('TABLE','INDEX');
10386 rows inserted
SQL> commit;
Commit complete
每个owner最多有两种类型的type值，每个owner和object_type组合包括多条记录。
SQL> select * from t where rownum<5;
OWNER OBJECT_TYP VALUE
----- ---------- ----------
SYS TABLE 20
SYS INDEX 46
SYS TABLE 28
SYS TABLE 15
要求以owner和object_type进行汇总，并且将TABLE和INDEX分别成列，显示汇总值。简而言之，就是一个owner一行记录，每列对应一种object_type取值。
2、行转列处理
按照抽丝剥茧的原则，我们聚合一次owner和object_type。
--聚合汇总结
SQL> select owner, object_type, sum(value) from t group by owner, object_type;
OWNER OBJECT_TYP SUM(VALUE)
-------------------- ---------- ----------
SCOTT INDEX 146392
PRITEST TABLE 172006
（篇幅原因，有省略......）
DICT INDEX 18490973
COMMON TABLE 9076700
63 rows selected
下面就是避免不了的行转列操作了。在11g以前的版本中，我们可能需要借助专门的复杂SQL书写乃至存储过程才能解决。在11g中，我们可以使用pivot函数，一次性的将聚合操作和行转列操作完成。
select *
from t
pivot
(
sum(value) --聚合操作函数
for object_type --行转列标准
in ('TABLE','INDEX') --行转列列取值和顺序
)
上面的SQL就是pivot函数的基本格式。看起来非常简单，主要要定义三个部分的内容。
ü 聚合列取值。需要告诉pivot函数进行转列的过程中，聚合操作的函数和处理对象；
ü 行转列标准。依据那个列进行行转列；
ü 列转行取值。因为要将数据行取值转成列，我们需要告诉Oracle那些取值成列，并且这些取值成列的过程中，列顺序是如何的；
下面我们看执行结果。
SQL> select *
2 from t
3 pivot
4 (
5 sum(value)
6 for object_type
7 in ('TABLE','INDEX')
8 )
9 ;
OWNER 'TABLE' 'INDEX'
-------------------- ---------- ----------
MDSYS 8003137 7378327
MELODY 299266
RMAN 8292775 18094518
OUTLN 1351 1805
（篇幅原因，有省略......）
COMMON 9076700 17161092
34 rows selected
处理成功，我们查看一下执行计划。
SQL> explain plan for select *
2 from t
3 pivot
4 (
5 sum(value)
6 for object_type
7 in ('TABLE','INDEX')
8 );
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3924414983
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 34 | 612 | 13 (8)| 00:00:01 |
| 1 | HASH GROUP BY PIVOT| | 34 | 612 | 13 (8)| 00:00:01 |
| 2 | TABLE ACCESS FULL | T | 10386 | 182K| 12 (0)| 00:00:01 |
----------------------------------------------------------------------------
9 rows selected
从执行计划上看，Oracle为了pivot函数引入了一个专门操作pivot。执行计划中的hast group by pivot操作就是将数据转移到专门的处理区（猜想是PGA）进行流程化的处理。
下面是使用autotrace处理过的情况。
SQL> set autotrace traceonly;
SQL> select *
2 from t
3 pivot
4 (
5 sum(value)
6 for object_type
7 in ('TABLE','INDEX')
8 );
已选择34行。
执行计划
----------------------------------------------------------
Plan hash value: 3924414983
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 34 | 612 | 13 (8)| 00:00:01 |
| 1 | HASH GROUP BY PIVOT| | 34 | 612 | 13 (8)| 00:00:01 |
| 2 | TABLE ACCESS FULL | T | 10386 | 182K| 12 (0)| 00:00:01 |
----------------------------------------------------------------------------
统计信息
----------------------------------------------------------
196 recursive calls
0 db block gets
65 consistent gets
42 physical reads
0 redo size
1503 bytes sent via SQL*Net to client
441 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
34 rows processed
注意在统计信息中，引用了3的sorts操作。
应该说，pivot函数是我们可以找到比较好的行转列的操作方式了。在Oracle端，是要消耗一定的排序操作。当数据集合特别大的时候，我们可以猜测会有一些性能的退化（特别是发生sorts(disk)）。Pivot作为标准Oracle函数，Oracle也会不断进行优化处理。所以，性能一般是可以接受的。
3、进一步的需求
在结果集合中，我们看到了结果标题如下：
OWNER 'TABLE' 'INDEX'
-------------------- ---------- ----------
MDSYS 8003137 7378327
MELODY 299266
Oracle是使用两个列值作为标题。那么我们结果如何引用这个列
名称呢？
SQL> select owner, 'TABLE','INDEX' from (
2 select *
3 from t
4 pivot
5 (
6 sum(value)
7 for object_type
8 in ('TABLE','INDEX')
9 )) where rownum<3;
OWNER 'TABLE' 'INDEX'
-------------------- ------- -------
APEX_030200 TABLE INDEX
APPQOSSYS TABLE INDEX
显然，Oracle将其识别为了字符串。应该怎么做呢？我们需要使用双引号。之所以Oracle会识别错误，就因为’’的常量识别优先级高于列名称优先级。我们需要让Oracle忽略这个特点，就使用双引号。
SQL> select owner, 'TABLE' PRI_TYP,"'TABLE'" PRI_AMT,'INDEX' SEC_TYP,"'INDEX'" SEC_AMT from (
2 select *
3 from t
4 pivot
5 (
6 sum(value)
7 for object_type
8 in ('TABLE','INDEX')
9 )) where rownum<3;
OWNER PRI_TYP PRI_AMT SEC_TYP SEC_AMT
-------------------- ------- ---------- ------- ----------
APEX_030200 TABLE 25757431 INDEX 67809456
APPQOSSYS TABLE 25887 INDEX
实现成功！
4、结论
在现实工作中，我们遇到的需求千奇百怪。在选择工具的时候，要有选择类库、预定义提供的方法函数，不要轻易的选择自定义方法。因为对现成的类库和产品来说，它在实现这个方法的时候倾注了很大心血和能力在其中，其效率一定是比我们自己写的要强很多。这也就要求我们需要不断关注新特性，新方法和改进，不断的学习和进步。
