oracle 使用rownum减少自定义函数的调用

最新推荐文章于 2023-11-01 11:55:52 发布
最新推荐文章于 2023-11-01 11:55:52 发布
阅读量665 收藏
点赞数
分类专栏： sql tunning 文章标签： oracle 优化 function
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/toratto/article/details/10399743
版权
ROWNUM Pseudocolumn --rownum 伪列
For each row returned by a query, the  ROWNUM  pseudocolumn returns a number 

indicating the order in which Oracle selects the row from a table or set of joined rows. 

---每一个查询的返回的行都会带有一个伪列

---伪劣的编号从一开始，从下面的例子可以得出，rownum是从1开始递增如果下一行有值则返回。

scott@ORCL> select * from dept where rownum>5;


no rows selected


scott@ORCL> select * from dept where rownum<5;


    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON



scott@ORCL> create table t1
  2  ( prc_chk_key number(9) not null,
  3   prod_key     number(12) not null,
  4    cmpt_loc_key number(5) not null,
  5    loc_key     number(5)  not null,
  6     prc_chk_dt date
  7  )
  8  /

Table created.


scott@ORCL> insert into t1 select 2,3,4,5,sysdate
  2  from all_objects where rownum<=50;
from all_objects where rownum<=50

scott@ORCL> create table t2
  2  (prc_chk_key number(9) not null,
  3   prc_chk_typ_desc varchar2(35) not null,
  4   cmpt_loc_key  number(5),
  5   loc_key  number(5) not null
  6  ) 
  7  /

scott@ORCL> insert into t2 select  2,'x', 4,5 from all_objects where rownum<=50;

现在我们创建一个函数，返回值为一个常量，使用dbms_application_info 使返回值每次都加1，能够

是我们看到函数F被调用了多少次。

 1  create or replace function F(
  2  v_prod_key  in number default null,
  3  v_proc_chk_key IN number default NULL,
  4  v_return  IN varchar2 default null,
  5  v_want_sr IN  varchar2 default  null,
  6  v_version  IN number) RETURN varchar2
  7  AS
  8   BEGIN
  9   DBMS_APPLICATION_info.set_client_info(userenv('client_info')+1);
 10  return 'x';
 11* end;
scott@ORCL> /




scott@ORCL> EXEC DBMS_APPLICATION_info.set_client_info(0);


  1  select /*+ use_hash(al1,al2) */
  2  al2.prc_chk_typ_desc prc_chk_type_desc,
  3  al1.prc_chk_dt  prc_chk_dt,
  4  al1.cmpt_loc_key  cmpt_loc_key,
  5  al1.prod_key  upc_prod_key,
  6  al1.loc_key  loc_key,
  7  max(F(al1.PROD_KEY,al1.prc_chk_key,'QTY','D',1)),
  8  max(F(al1.PROD_KEY,al1.prc_chk_key,'AMT','D',1)),
  9  max(F(al1.PROD_KEY,al1.prc_chk_key,'CODE','D',1)),
 10  max(F(al1.PROD_KEY,al1.prc_chk_key,'PRC','D',1))
 11  from t1 al1,t2 al2
 12  where al1.cmpt_loc_key=al2.cmpt_loc_key
 13  and al1.loc_key=al2.loc_key
 14  and al1.prc_chk_key =al2.prc_chk_key
 15  group by al2.prc_chk_typ_desc,al1.prc_chk_dt,al1.cmpt_loc_key,al1.prod_key,
 16* al1.loc_key
scott@ORCL> /




Execution Plan
----------------------------------------------------------
Plan hash value: 2197463864


----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    50 |  5950 |     8  (25)| 00:00:01 |
|   1 |  SORT GROUP BY      |      |    50 |  5950 |     8  (25)| 00:00:01 |
|*  2 |   HASH JOIN         |      |    50 |  5950 |     7  (15)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T1   |    50 |  3050 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T2   |    50 |  2900 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   2 - access("AL1"."CMPT_LOC_KEY"="AL2"."CMPT_LOC_KEY" AND
              "AL1"."LOC_KEY"="AL2"."LOC_KEY" AND
              "AL1"."PRC_CHK_KEY"="AL2"."PRC_CHK_KEY")


Note
-----
   - dynamic sampling used for this statement (level=2)




Statistics
----------------------------------------------------------
         51  recursive calls
          0  db block gets
        117  consistent gets
          0  physical reads
          0  redo size
       1289  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

现在我们查看函数被调用了几次

scott@ORCL> select userenv('client_info') data from dual;


DATA
----------------------------------------------------------------
10000

如果行数远远多于五十的话那么函数的调用次数将是相当惊人的。

下面我们重写这条sql语句，先初始化环境。

scott@ORCL> EXEC DBMS_APPLICATION_info.set_client_info(0);


 1   select /*+ use_hash(al1,al2) */
  2      al2.prc_chk_typ_desc prc_chk_type_desc,
  3      al1.prc_chk_dt  prc_chk_dt,
  4      al1.cmpt_loc_key  cmpt_loc_key,
  5      al1.prod_key  upc_prod_key,
  6      al1.loc_key  loc_key,
  7      max(a),
  8      max(b),
  9     max(c),
 10     max(d)
 11     from (
 12     select al1.*,
 13   F(al1.PROD_KEY,al1.prc_chk_key,'QTY','D',1) a,
 14   F(al1.PROD_KEY,al1.prc_chk_key,'AMT','D',1) b,
 15   F(al1.PROD_KEY,al1.prc_chk_key,'CODE','D',1) c,
 16   F(al1.PROD_KEY,al1.prc_chk_key,'PRC','D',1) d,
 17   ROWNUM r from t1 al1
 18  ) al1,t2 al2
 19     where al1.cmpt_loc_key=al2.cmpt_loc_key
 20     and al1.loc_key=al2.loc_key
 21     and al1.prc_chk_key =al2.prc_chk_key
 22     group by al2.prc_chk_typ_desc,al1.prc_chk_dt,al1.cmpt_loc_key,al1.prod_key,
 23*   al1.loc_key
scott@ORCL> /




Execution Plan
----------------------------------------------------------
Plan hash value: 2566946263


------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |  2500 |    19M|     8  (25)| 00:00:01 |
|   1 |  SORT GROUP BY        |      |  2500 |    19M|     8  (25)| 00:00:01 |
|*  2 |   HASH JOIN           |      |  2500 |    19M|     7  (15)| 00:00:01 |
|   3 |    TABLE ACCESS FULL  | T2   |    50 |  2900 |     3   (0)| 00:00:01 |
|   4 |    VIEW               |      |    50 |   393K|     3   (0)| 00:00:01 |
|   5 |     COUNT             |      |       |       |            |          |
|   6 |      TABLE ACCESS FULL| T1   |    50 |  3050 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   2 - access("AL1"."CMPT_LOC_KEY"="AL2"."CMPT_LOC_KEY" AND
              "AL1"."LOC_KEY"="AL2"."LOC_KEY" AND
              "AL1"."PRC_CHK_KEY"="AL2"."PRC_CHK_KEY")


Note
-----
   - dynamic sampling used for this statement (level=2)




Statistics
----------------------------------------------------------
         55  recursive calls
          0  db block gets
        158  consistent gets
          0  physical reads
          0  redo size
       1120  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

查看函数被调用的此次数

scott@ORCL> select userenv('client_info') data from dual;


DATA
----------------------------------------------------------------
200


第二sql语句在内联视图中使用了rownum，oracle为了分配rownum给每一行必须实体化内联视图。从执行计划中可以看到

t1进行全表扫描然后转化为实体化视图，结果集合t2进行哈希连接。

ForKing_
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql的rownum函数_MySQL中的ROWNUM的实现_MySQL
weixin_42357719的博客
02-02 1776
MySQL 几乎模拟了 Oracle,SQL Server等商业数据库的大部分功能，函数。但很可惜，到目前的版本(5.1.33)为止,仍没有实现ROWNUM这个功能。下面介绍几种具体的实现方法.建立实验环境如下mysql> create table tbl (-> id int primary key,-> col int-> );Query OK, ...
【ORACLE语法】ORACLE 中ROWNUM用法,ORACLE自定义分页怎么写
hwwjason的博客
02-25 268
ROWNUM 查询的时候，一定要查询出第一行，否者查不出任何记录，所以做分页查询的时候必须做子查询。 sql举例如下 select * from (select ROWNUM as rn, t.* from H_CAPITAL_CITY_IDCARD t where ROWNUM <= 3) u where u.rn > 2 and rn <= 3; mybatis中的写法如下 <select id="getCapitalCityIDCard" resultType="j
Row_number()函数用法
热门推荐
Zhuxxxxx的博客
03-28 8万+
Row_number()函数是SQL SERVER系统函数中的一种，它为结果集的分区中的每一行分配一个连续的整数。简单的说就是生成一个独表，序号以每个分区的第一行开头，下面是其基本语法： 正在上传…重新上传取消 ·PARTITION BY子句将结果集划分为分区。 ROW_NUMBER()函数分别应用于每个分区，并重新初始化每个分区的行号。PARTITION BY子句是可选的。如果未指定，ROW_NUMBER()函数会将整个结果集视为单个分区。 ·ORDER BY子句定义结果集的每个分区...
Oracle rownum函数
csh的博客
04-03 1万+
rownum函数是oracle中常用到的一个返回编号的函数，系统会按顺序分配为从查询返回的行的编号，返回的第一行的编号是1，第二行是2等等，这个函数可以用来限制查询返回的总行数，也就是获取第几行到第几行的数据。 还有就是rownum函数不能以任何表的名称作为前缀。 看下简单的例子：列出工资排名前10的员工的工资 select rownum,u.* from ( ...
(Oracle）rownum释疑
Loger
02-06 1440
对于rownum来说它是oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2，依此类推，这个伪字段可以用于限制查询返回的总行数，且rownum不能以任何表的名称作为前缀。 (1) rownum 对于等于某值的查询条件 如果希望找到学生表中第一条学生的信息，可以使用rownum=1作为条件。但是想找到学生表中第二条学生的信息，使用rownum=2结果查不到数据。因为
Python调用SQLPlus来操作和解析Oracle数据库的方法
01-20
先来看一个简单的利用python调用sqlplus来输出结果的例子： import os import sys from subprocess import Popen, PIPE sql = set linesize 400 col owner for a10 col object_name for a30 select owner, object_...
oracle函数,序列与分页代码实现及相关练习题
最新发布
06-12
在Oracle中进行分页查询时，通常使用 `ROWNUM` 函数来实现。`ROWNUM` 是Oracle提供的一个伪列，用于返回行的顺序号。分页查询的基本思路是先通过子查询获取行号，然后根据行号进行筛选。 ##### 基本分页查询语法： ...
Java中如何调用Oracle的5个小视频.rar
02-03
通常的方法是通过设置SQL语句的`LIMIT`和`OFFSET`子句，但Oracle数据库不支持这两个关键字，而是使用`ROWNUM`和子查询或者`DBMS_ROW_NUMBER`包。视频可能演示了如何构建动态SQL来实现分页查询。 2. **JAVA中调用有...
mysql之row_number函数
m0_61143853的博客
11-01 7794
将字段按照COLUMN1分组COLUMN2排序后分配一个从1开始升序的编号。结果会按每一个用户进行分区，按价格降序排序后，按顺序编号。3.将商品按用户分组后价格降序并编号且只查询编号为1的记录。可以看到在上述基础上过滤出了编号为1的记录。1.将商品表中的价格按升序排序并编号。2.将商品按用户分组后价格降序并编号。结果新增了一列来标记行号。
oracle中的rownum函数
ZhangRui的博客
03-13 3万+
1.rownum函数是oracle中常用到的一个返回编号的函数，系统会按顺序分配为从查询返回的行的编号，返回的第一行的编号是1，第二行是2，后面以此类推x,这个函数可以用来限制查询返回的总行数，也就是获取第几行到第几行的数据。 2.rownum 函数不能以任何表名作为前缀 3.rownum 其实也可以认为是一个起始值为1的单调递增数列，每次增加1 4.如果dept表中的数据有8条 select * from dept where rownum=1; // 输出1条 select * from...
sql 窗口函数ROW_NUMBER() 分组/排序/计数
weixin_46484674的博客
09-07 1万+
分区的，使用它 为返回的结果集中的每一行 生成一个序列号（为行分配序号），第一个数字以1开头。CTE、存储过程中使用。分组、排序、计数都能使用
oracle rownum函数介绍
12-25 7645
最近由于一个查询语句来使用rownum来进行查询，要求结果是查到地址1和地址2，地址信息保存在一张表 现在要求在一条记录中显示地址1和地址2，地址可能为空   地址1很好获取，只需在查询的结果中加入rownum = 1 即可，但是获取地址2的时候就麻烦了如果加上 rownum between 2 and 2 ，不会查到结果， 加上 rownum > 1 and rownum 是这样的：
关于Oracle中的rownum函数
tcl_wj的专栏
11-02 773
项目中使用hibernate，在实现分页查询时其生成的SQL用到了Oracle的ROWNUM函数 ，但是却遇到了一个奇怪的问题：       关联视图后翻到后一页查询不到结果，但去掉select子查询中的order by子句或者加上order by子句将其中一个CHAR(1)类型字段条件的参数由字符类型改为数字类型（即 state=1改成state=1方式）就能查询到结果。      
mysql的rownum函数_MySQL 中的窗口函数 ROWNUM 实现
weixin_36413157的博客
01-19 3820
MySQL 8.0 后开始有窗口函数的。窗口函数是作用域于每条语句上的，会返回相同数据量的行数。聚合函数会根据计算结果返回一个值。窗口函数的一般格式：function_name()over(write SQL)按照功能划分，可以把 MySQL 支持的窗口函数分为如下几类：序号函数：row_number() / rank() / dense_rank()分布函数：percent_rank() / c...
Oracle的rownum和分析函数
软件工程 plus 人工智能
09-13 2687
racle的rownum字段是个比较奇怪的字段。拿一张有26条记录的Test表来举例。       select * from Test where rownum >=1;      select * from Test where rownum >=2;      sel
巧用rownum实现分析函数row_number()的功能
06-26 209
用rownum实现分析函数row_number()的功能，即根据表中某一列输出一个唯一编号。 SQL> select * from t; DOCID NAME CRENO...
Oracle-Mysql:ROWNUM 函数的使用
Odyssues_lee的博客
09-19 7397
  对于rownum来说它是oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2，依此类推，这个伪字段可以用于限制查询返回的总行数，且rownum不能以任何表的名称作为前缀。 (1) rownum 对于等于某值的查询条件 如果希望找到学生表中第一条学生的信息，可以使用rownum=1作为条件。但是想找到学生表中第二条学生的信息，使用rownum=2结果查不到数据。...
SQL中row_number函数用法
baidu_41797613的博客
09-26 4万+
row_number函数用法1、函数讲解2、LeetCode实战 1、函数讲解 语法：ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN) 简单的说，row_number()从1开始，为每条分组记录返回一个数字，举例： ROW_NUMBER() OVER(ORDER BY xlh DESC) 这里的用法是先将xlh列进行降序排序，再将降序后的每条记录返回一个序号。 row_number() OVER (PARTITION BY COL1 ORDE
Java调用Oracle存储过程：分页与调用技巧详解
**Oracle高效分页存储过程实例**展示了如何在存储过程中实现分页查询，通常使用`ROWNUM`伪列或`DBMS_QUERY`包的`FOR SYSTEM LOOP`结构来实现。 最后，存储过程与函数的主要区别在于： - 函数是可调用的表达式，有...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值