[20170703]pivot与order by字段.txt

简介: [20170703]pivot与order by字段.txt --//11G开始支持pivot,上午写一个脚本,来自链接http://blog.itpub.net/267265/viewspace-1063539/ --//做了一点点改写.

[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;

目录
相关文章
|
SQL 关系型数据库 Oracle
[20140812]union all与order by.txt
[20140812]union all与order by.txt --昨天同事问一个sql的问题,关于union all与order by的问题.做一个简单的记录: SCOTT@test> @ver BANNER -------------------...
685 0
|
SQL Oracle 关系型数据库
select count(*)、count(1)、count(主键列)和count(包含空值的列)有何区别?
下班路上看见网上有人问一个问题: oracle 10g以后count(*)和count(非空列)性能方面有什么区别? 乍一看,确实有些含糊,Oracle中往往小问题蕴含着大智慧,如何破云见日? 最直接的方法,我想就是通过10053事件,来看下不同SQL对应的执行计划和资源消耗等情况,进而看看是否有些信息可以为我们所用。
1254 0
|
Java 关系型数据库 Linux
|
Java 关系型数据库 Linux
|
Java 关系型数据库 Linux
|
SQL Serverless 数据库
【count(列名)、count(1)和 count(星号)有什么区别】
【count(列名)、count(1)和 count(星号)有什么区别】
248 0
|
10月前
ROW_NUMBER() OVER()函数用法详解 (分组排序 例子多)
ROW_NUMBER() OVER()函数用法详解 (分组排序 例子多)
154 0
|
关系型数据库 Oracle 开发工具
[20170508]listagg拼接显示字段.txt
[20170508]listagg拼接显示字段.txt --//记得前一阵子,要给表增加一个字段,并赋值.采用表在线重定义.要使用函数dbms_redefinition.
899 0
|
SQL 数据库
除了会排序,你对ORDER BY的用法可能一无所知!(一)
小伙伴们在进行SQL排序时,都能很自然的使用到ORDER BY。不管是默认ASC的升序,还是DESC降序,几乎都是信手拈来。 今天给大家分享一些你可能不知道的ORDER BY用法。
除了会排序,你对ORDER BY的用法可能一无所知!(一)
|
XML SQL 数据库
除了会排序,你对ORDER BY的用法可能一无所知!(二)
小伙伴们在进行SQL排序时,都能很自然的使用到ORDER BY。不管是默认ASC的升序,还是DESC降序,几乎都是信手拈来。 今天给大家分享一些你可能不知道的ORDER BY用法。
除了会排序,你对ORDER BY的用法可能一无所知!(二)