`

Oracle中Keep的使用

阅读更多
官方文档有如下说明:

FIRST/LAST Functions

The FIRST/LAST aggregate functions allow you to return the result of an aggregate applied over a set of rows that rank as the first or last with respect to a given order specification. FIRST/LAST lets you order on column A but return an result of an aggregate applied on column B. This is valuable because it avoids the need for a self-join or subquery, thus improving performance. These functions begin with a tiebreaker function, which is a regular aggregate function (MIN, MAX, SUM, AVG, COUNT, VARIANCE, STDDEV) that produces the return value. The tiebreaker function is performed on the set rows (1 or more rows) that rank as first or last respect to the order specification to return a single value.

To specify the ordering used within each group, the FIRST/LAST functions add a new clause starting with the word KEEP.

FIRST/LAST Syntax

These functions have the following syntax:

aggregate_function KEEP
( DENSE_RANK LAST ORDER BY
  expr [ DESC | ASC ] [NULLS { FIRST | LAST }]
  [, expr [ DESC | ASC ] [NULLS { FIRST | LAST }]]...
)
[OVER query_partitioning_clause]

-- emp表的数据
SQL> SELECT t.empno,
  2         t.ename,
  3         t.mgr,
  4         t.sal,
  5         t.deptno
  6    FROM emp t
  7   ORDER BY t.sal,
  8            t.deptno;

     EMPNO ENAME                       MGR        SAL     DEPTNO
---------- -------------------- ---------- ---------- ----------
       111 aaa                        2222        800          9
      7369 SMITH                      7902        800         20
      7900 JAMES                      7698        950         30
      7876 ADAMS                      7788       1100         20
      7521 WARD                       7698       1250         30
      7654 MARTIN                     7698       1250         30
      7934 MILLER                     7782       1300         10
      7844 TURNER                     7698       1500         30
      7499 ALLEN                      7698       1600         30
      7782 CLARK                      7839       2450         10
      7698 BLAKE                      7839       2850         30

     EMPNO ENAME                       MGR        SAL     DEPTNO
---------- -------------------- ---------- ---------- ----------
      7566 JONES                      7839       2975         20
      7788 SCOTT                      7566       3000         20
      7902 FORD                       7566       3000         20
      7839 KING                                  5000         10
       222 bbb                        3333       5000         40

-- 1.现在要查询表中工资最高的部门号的最大最小值,工资最低的部门号的最大最小值
-- 因为是DENSE_RANK,会产生重复数据,使用min,max取一条。
-- 这个sql没有使用over子句,后面的例子会使用
SQL> SELECT MIN(t.deptno) KEEP(DENSE_RANK FIRST ORDER BY t.sal) a,
  2         MAX(t.deptno) KEEP(DENSE_RANK FIRST ORDER BY t.sal) b,
  3         MIN(t.deptno) KEEP(DENSE_RANK LAST ORDER BY t.sal) c,
  4         MAX(t.deptno) KEEP(DENSE_RANK LAST ORDER BY t.sal) d
  5    FROM emp t;

         A          B          C          D
---------- ---------- ---------- ----------
         9         20         10         40

-- 2.加上over,对每一行记录做计算,看看效果:
SQL> 
SQL> SELECT t.empno,
  2         t.ename,
  3         t.mgr,
  4         t.sal,
  5         t.deptno,
  6         MIN(t.deptno) KEEP(DENSE_RANK FIRST ORDER BY t.sal) OVER() a,
  7         MAX(t.deptno) KEEP(DENSE_RANK FIRST ORDER BY t.sal) OVER() b,
  8         MIN(t.deptno) KEEP(DENSE_RANK LAST ORDER BY t.sal) OVER() c,
  9         MAX(t.deptno) KEEP(DENSE_RANK LAST ORDER BY t.sal) OVER() d
 10    FROM emp t
 11   ORDER BY t.sal,
 12            t.deptno
 13  ;

EMPNO ENAME        MGR       SAL DEPTNO          A          B          C          D
----- ---------- ----- --------- ------ ---------- ---------- ---------- ----------
  111 aaa         2222    800.00      9          9         20         10         40
 7369 SMITH       7902    800.00     20          9         20         10         40
 7900 JAMES       7698    950.00     30          9         20         10         40
 7876 ADAMS       7788   1100.00     20          9         20         10         40
 7521 WARD        7698   1250.00     30          9         20         10         40
 7654 MARTIN      7698   1250.00     30          9         20         10         40
 7934 MILLER      7782   1300.00     10          9         20         10         40
 7844 TURNER      7698   1500.00     30          9         20         10         40
 7499 ALLEN       7698   1600.00     30          9         20         10         40
 7782 CLARK       7839   2450.00     10          9         20         10         40
 7698 BLAKE       7839   2850.00     30          9         20         10         40
 7566 JONES       7839   2975.00     20          9         20         10         40
 7788 SCOTT       7566   3000.00     20          9         20         10         40
 7902 FORD        7566   3000.00     20          9         20         10         40
 7839 KING               5000.00     10          9         20         10         40
  222 bbb         3333   5000.00     40          9         20         10         40

-- 3.下面对每一个mgr求最大(最小)工资的部门号的最大(最小)值
SQL> SELECT t.empno,
  2         t.ename,
  3         t.mgr,
  4         t.sal,
  5         t.deptno,
  6         MIN(t.deptno) KEEP(DENSE_RANK FIRST ORDER BY t.sal) OVER(PARTITION BY t.mgr) a,
  7         MAX(t.deptno) KEEP(DENSE_RANK FIRST ORDER BY t.sal) OVER(PARTITION BY t.mgr) b,
  8         MIN(t.deptno) KEEP(DENSE_RANK LAST ORDER BY t.sal) OVER(PARTITION BY t.mgr) c,
  9         MAX(t.deptno) KEEP(DENSE_RANK LAST ORDER BY t.sal) OVER(PARTITION BY t.mgr) d
 10    FROM emp t
 11   ORDER BY t.sal,
 12            t.deptno
 13  ;

EMPNO ENAME        MGR       SAL DEPTNO          A          B          C          D
----- ---------- ----- --------- ------ ---------- ---------- ---------- ----------
  111 aaa         2222    800.00      9          9          9          9          9
 7369 SMITH       7902    800.00     20         20         20         20         20
 7900 JAMES       7698    950.00     30         30         30         30         30
 7876 ADAMS       7788   1100.00     20         20         20         20         20
 7654 MARTIN      7698   1250.00     30         30         30         30         30
 7521 WARD        7698   1250.00     30         30         30         30         30
 7934 MILLER      7782   1300.00     10         10         10         10         10
 7844 TURNER      7698   1500.00     30         30         30         30         30
 7499 ALLEN       7698   1600.00     30         30         30         30         30
 7782 CLARK       7839   2450.00     10         10         10         20         20
 7698 BLAKE       7839   2850.00     30         10         10         20         20
 7566 JONES       7839   2975.00     20         10         10         20         20
 7902 FORD        7566   3000.00     20         20         20         20         20
 7788 SCOTT       7566   3000.00     20         20         20         20         20
 7839 KING               5000.00     10         10         10         10         10
  222 bbb         3333   5000.00     40         40         40         40         40

分享到:
评论

相关推荐

    转oracle keep池.docx

    Oracle数据库的KEEP池是Oracle 8.0及更高版本中引入的一个高级特性,它允许数据库管理员(DBA)对数据缓存进行更精细化的管理。KEEP池的主要目的是为那些经常被访问但又容易因缓存竞争而被挤出缓存的段提供一个专门的...

    直接拷贝数据文件实现Oracle数据迁移

    在确认数据完整无误后,你可以在新环境中开始正常使用Oracle数据库。 总的来说,直接拷贝数据文件的迁移方式适用于特定条件下的Oracle数据迁移,它简化了传统备份恢复过程,但仍然需要谨慎操作,以避免数据丢失或不...

    Oracle数据库管理员技术指南

    8.2.4 使用 DBMS_SHARED_POOL.KEEP 的技巧 8.2.5 怎样生成进行固定操作的脚本 8.2.6 使用 DBMS_SHARED_POOL .UNKEEP 的技巧 8.3 优化数据排序的技术 8.3.1 在内存中进行全部或大部分排序 8.3.2 最小化排序时...

    Oracle 12c备份恢复-RMAN工具技术手册

    本文档旨在提供 Oracle 12c 备份恢复的技术手册,主要介绍 RMAN 工具的概念、架构、备份类型和使用方法。 一、RMAN 概念 RMAN(Recovery Manager)是 Oracle 推荐的备份和恢复工具。使用 RMAN 可以进行数据库备份...

    ORACLE RMAN 简明使用手册

    其中,`CONTROL_FILE_RECORD_KEEP_TIME` 参数可以控制备份记录在控制文件中的保存时间长度,一旦超出设定时间,这些记录会被新的备份信息覆盖。 #### Recovery Catalog Recovery Catalog 可以用于保存 RMAN ...

    Oracle内存分配与调整--大牛冯春培的一篇文章

    - Database Buffers:数据缓冲区,根据不同的Oracle版本,该部分可能包含db_block_buffer、db_block_size、buffer_pool_keep、buffer_pool_recycle、db_cache_size等。 - Redo Buffers:重做日志缓冲区(log_buffer...

    Oracle数据库参数设置技术手册

    CPU_COUNT 是 Oracle数据库参数设置技术手册中另一个重要的参数,它控制着数据库的 CPU 使用率,对数据库的性能和稳定性有着重要的影响。数据库管理员可以根据实际情况调整 CPU_COUNT 的值,以提高数据库的性能和...

    oracle数据库的内存调整

    PGA 则主要包含用户全局区(User Global Area, UGA),在Oracle使用共享服务器模式(Multithreaded Server, MTS)时,部分UGA会放入large_pool_size中。UGA存储了用户的会话信息,如游标、变量和PL/SQL工作区等。 ...

    Oracle数据库sqlplus常用命令

    Oracle数据库sqlplus是Oracle数据库管理系统中的一种命令行工具,用于执行SQL语句、查看数据库状态、执行数据库管理任务等。以下是Oracle数据库sqlplus常用命令的知识点总结: 获取数据库信息 1. 获取数据库名和...

    Oracle内存管理(深入解析)

    Buffer Cache用于存储最近使用的数据块,Oracle使用LRU算法管理这些缓存,确保高效的缓存命中率。Buffer Cache被划分为Default、Keep和Recycle三个池,以满足不同类型的存储需求。Shared Pool用于存储解析的SQL语句...

    ORACLE quota 表空间限制

    在 Oracle 数据库中,为了更好地管理和控制用户对不同表空间的使用情况,引入了 Quota 的概念。Quota 主要用于限制用户在一个或多个表空间内所能使用的最大空间量。通过设置合适的 Quota,不仅可以有效地防止用户...

    韩顺平Oracle教学笔记.docx

    SQL语句是Oracle数据库中最基本的操作语言,包括select、insert、update和delete四种基本语句。select语句用于查询数据,insert语句用于添加数据,update语句用于修改数据,delete语句用于删除数据。 2. 数据类型:...

    oracle数据库调优配置

    7. **Db_keep_cache_size**: 配置为224MB,用于保持经常使用的数据块不被替换,提高热数据的访问速度。 8. **Workarea_size_policy**: 设置为AUTO,使Oracle能够根据工作区的实际需求动态调整其大小,避免了固定...

    任务计划+批处理实现Oracle数据库的定时备份

    Oracle数据库作为全球广泛使用的数据库管理系统之一,其稳定性和安全性备受赞誉。为了确保数据的安全,定期备份是必要的操作。本文将详细介绍如何利用任务计划和批处理脚本来实现Oracle数据库的定时备份。 一、任务...

    oracle初始化参数详解

    `buffer_pool_keep`保留某些对象在内存中以减少I/O,`buffer_pool_recycle`则用于清理并重用内存。 8. **commit_point_strength**:在分布式事务中,控制提交点的确定,影响事务的一致性和分布。 9. **compatible*...

    oracle性能调优之buffer cache

    Buffer Cache 是 Oracle 中的一种缓存机制,负责将磁盘上的数据 block 读取到内存中,以提高数据库的访问速度。在本文中,我们将详细介绍 Buffer Cache 的工作原理、状态、管理和优化方法。 Buffer Cache 的工作...

    Oracle 10g的内存调整报告

    - **Streams Pool**:如果使用了Oracle Streams特性,则该内存区域会被用来缓存相关信息,大小可以通过`streams_pool_size`参数控制。 - **Log Buffer**:用于存放重做日志条目,大小可以通过`log_buffer`参数来控制...

    Oracle自动内存管理ASMM

    例如,如果数据库发现SQL解析缓存(library cache)中的数据使用频繁,它就会自动增加这部分内存的分配,以提高查询效率。反之,如果某个内存区域未充分利用,则可以将其资源释放给其他需要的组件。 ##### 3.2 内存...

    Oracle的数据缓冲区是如何工作

    在早期版本中,新数据块被添加到最近使用列表的前端,但在Oracle 8i之后,新数据块被插入到缓冲区链的中间,引入了“热区”和“冷区”的概念。这一改变提高了对常用数据块的处理效率。 “热区”是指最近被频繁访问...

Global site tag (gtag.js) - Google Analytics