[20170703]pivot与order by字段.txt

--//11G开始支持pivot,上午写一个脚本,来自链接http://blog.itpub.net/267265/viewspace-1063539/
--//做了一点点改写.
select * from
(
WITH pivot_stats
     AS (SELECT owner,
                object_name,
                statistic_name,
                VALUE
           FROM v$segment_statistics)
SELECT *
  FROM pivot_stats PIVOT (SUM (VALUE)
                   FOR statistic_name
                  IN  ('logical reads',
                       'buffer busy waits',
                       'gc buffer busy',
                       'db block changes',
                       'physical reads',
                       'physical writes',
                       'physical read requests',
                       'physical write requests',
                       'physical reads direct',
                       'physical writes direct',
                       'optimized physical reads',
                       'gc cr blocks received',
                       'gc current blocks received',
                       'ITL waits',
                       'row lock waits',
                       'space used',
                       'space allocated',
                       'segment scans'))
where owner =upper('&&1') order by &&2
)
where rownum<=20;

--//我发现这样的输出字段命名如下:
SCOTT@book> @ &r/seg_stat1 scott 3
old  30: where owner =upper('&&1') order by &&2
new  30: where owner =upper('scott') order by 3

OWNER  OBJECT_NAME          'logical reads' 'buffer busy waits' 'gc buffer busy' 'db block changes' 'physical reads' 'physical writes' 'physical read requests' 'physical write requests' 'physical reads direct' 'physical writes direct'
------ -------------------- --------------- ------------------- ---------------- ------------------ ---------------- ----------------- ------------------------ ------------------------- ----------------------- ------------------------
'optimized physical reads' 'gc cr blocks received' 'gc current blocks received' 'ITL waits' 'row lock waits' 'space used' 'space allocated' 'segment scans'
-------------------------- ----------------------- ---------------------------- ----------- ---------------- ------------ ----------------- ---------------
SCOTT  DEPT                              32                   0                0                  0                6                 0                        3                         0                       0                        0
                         0                       0                            0           0                0            0                 0               0

SCOTT  PK_DEPT                           48                   0                0                  0                2                 0                        2                         0                       0                        0
                         0                       0                            0           0                0            0                 0               0

--//我这里指定第3个字段排序.如何使用字段名呢?尝试才发现使用单引号写才ok.做一个记录:

select * from
(
WITH pivot_stats
     AS (SELECT owner,
                object_name,
                statistic_name,
                VALUE
           FROM v$segment_statistics)
SELECT *
  FROM pivot_stats PIVOT (SUM (VALUE)
                   FOR statistic_name
                  IN  ('logical reads',
                       'buffer busy waits',
                       'gc buffer busy',
                       'db block changes',
                       'physical reads',
                       'physical writes',
                       'physical read requests',
                       'physical write requests',
                       'physical reads direct',
                       'physical writes direct',
                       'optimized physical reads',
                       'gc cr blocks received',
                       'gc current blocks received',
                       'ITL waits',
                       'row lock waits',
                       'space used',
                       'space allocated',
                       'segment scans'))
where owner =upper('&&1') order by 'logical reads'
)
where rownum<=20;