ITPub博客

首页 > Linux操作系统 > Linux操作系统 > row_number() partition order by 的使用 

row_number() partition order by 的使用 

原创 Linux操作系统 作者:tengrid 时间:2009-05-18 19:14:58 0 删除 编辑


##取shared pool中每个namespace中占空间最大的两个object
set linesize 1000
col name for a50
select *
  from (select row_number() over
         (partition by namespace
           order by sharable_mem desc) row_within
          ,namespace,sharable_mem,substr(name,1,40) name
       from v$db_object_cache
       order by sharable_mem desc
       )
 where row_within <= 2
  order by namespace,row_within;

ROW_WITHIN NAMESPACE                    SHARABLE_MEM NAME
---------- ---------------------------- ------------ --------------------------------------------------
         1 BODY                                83520 PRVT_ADVISOR
         2 BODY                                48508 PRVT_HDM
         1 CLUSTER                               570 C_OBJ#_INTCOL#
         2 CLUSTER                               570 C_FILE#_BLOCK#
         1 CURSOR                             121568 SELECT source,        (case when time_se
         2 CURSOR                             117736 SELECT source,        (case when time_se
         1 INDEX                               10027 WRH$_TABLESPACE_STAT_PK
         2 INDEX                               10021 WRH$_PARAMETER_PK
         1 INVALID NAMESPACE                    2018 WRH$_PARAMETER
         2 INVALID NAMESPACE                    2000 WRH$_SYSSTAT
         1 PUB_SUB                               564 DATABASE

ROW_WITHIN NAMESPACE                    SHARABLE_MEM NAME
---------- ---------------------------- ------------ --------------------------------------------------
         2 PUB_SUB                                 0 BOSS
         1 RSRC PLAN                             565 SYS_GROUP
         2 RSRC PLAN                               0 OTHER_GROUPS
         1 RULESET                                 0 ALERT_QUE_R
         1 TABLE/PROCEDURE                    498764 STANDARD
         2 TABLE/PROCEDURE                    293971 ANYDATA

17 rows selected.

<这里需要补充对row_number()函数的解释及它的使用场景>

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/94384/viewspace-600295/,如需转载,请注明出处,否则将追究法律责任。

上一篇: MySQL安全性指南
请登录后发表评论 登录
全部评论

注册时间:2009-05-18

  • 博文量
    136
  • 访问量
    379081