Bulk Collect(oracle批量查询)

2024-01-27 19:48

本文主要是介绍Bulk Collect(oracle批量查询),希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

原文:http://hi.baidu.com/easydba/blog/item/a81ddbf47e40866bddc474c5.html

 

Oracle8i中首次引入了Bulk Collect特性,该特性可以让我们在PL/SQL中能使用批查询,批查询在某些情况下能显著提高查询效率。现在,我们对该特性进行一些简单的测试和分析。

1. 首先,我们创建一个表,并插入100000条记录
在SQL/Plus中执行下列脚本:

drop table empl_tbl
/
create table empl_tbl(last_name varchar2(20),
first_name varchar2(10),
salary number(10))
/

begin
for i in 3000..102999 loop
insert into empl_tbl(last_name,first_name,salary) values('carl'||(i-3000),'wu'||(103000-i),i);
end loop;
end;
/
commit
/
select count(*) from empl_tbl;
/

2. 使用三种方法计算表中某一字段含有多少个不重复值

2.1 使用常规的Distinct来实现

SQL> select count(distinct last_name) "Distinct Last Name" from empl_tbl;

Distinct Last Name
------------------
            100000

Executed in 0.36 seconds

我们可以看到,常规方法需要0.36秒查出该表中有100000个不重复的Last_name值。

2.2 使用游标来实现

我们执行下面语句来统计Last_name字段的不重复值个数:

declare
all_rows number(10);
temp_last_name empl_tbl.last_name%type;
begin
all_rows:=0;
temp_last_name:=' ';
for cur in (select last_name from empl_tbl order by last_name) loop
    
      if cur.last_name!=temp_last_name then
       all_rows:=all_rows+1;
      end if;
      temp_last_name:=cur.last_name;
    
end loop;
dbms_output.put_line('all_rows are '||all_rows);
end;

请注意上面代码中的黑体部分使用了一个For Loop游标,为了提高程序可读性,我们没有显示定义游标变量。

执行结果:
all_rows are 100000
PL/SQL procedure successfully completed
Executed in 1.402 seconds

游标需要1.4秒才能查出该表中有100000个不重复的Last_name值,所耗时间是Distinct查询的3倍多。

2.3 使用Bulk Collect批查询来实现

示例代码如下:
declare
all_rows number(10);
--首先,定义一个Index-by表数据类型
type last_name_tab is table of empl_tbl.last_name%type index by binary_integer;
last_name_arr last_name_tab;
--定义一个Index-by表集合变量
temp_last_name empl_tbl.last_name%type;

begin
all_rows:=0;
temp_last_name:=' ';
--使用Bulk Collect批查询来充填集合变量
select last_name bulk collect into last_name_arr from empl_tbl;

for i in 1..last_name_arr.count loop
      if temp_last_name!=last_name_arr(i) then
       all_rows:=all_rows+1;
      end if;
      temp_last_name:=last_name_arr(i);
end loop;
dbms_output.put_line('all_rows are '||all_rows);
end;

请注意上面代码中,我们首先定义了一个Index-by表数据类型last_name_tab,然后定义了一个该集合数据类型的变量last_name_arr,最后我们使用Bulk Collect批查询来充填last_name_arr,请注意它的使用语法。

执行结果:
all_rows are 100000
PL/SQL procedure successfully completed
Executed in 0.28 seconds
从上面执行结果,我们可以看到,Bulk Collect批查询只需要0.28秒就能查出该表中有100000个不重复的Last_name值,所耗时间只有游标查询的1/5,同时它比Distinct常规查询的速度也要快。

3. 测试结果分析
为什么会出现上面的结果呢?我们可以使用Oracle的SQL_Trace来分析一下结果。在SQL命令行中,使用alter session set sql_trace=true语句打开Oracle的Trace,然后在命令行中执行上面三种查询并使用TKPROF工具生成Trace报告。

3.1 常规Distinct查询结果分析 ********************************************************************************select count(distinct last_name)
from
empl_tbl


call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.28       0.32        198        425          4           1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total        3      0.28       0.32        198        425          4           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 62

Rows     Row Source Operation
------- ---------------------------------------------------
      1 SORT GROUP BY
100000   TABLE ACCESS FULL EMPL_TBL

********************************************************************************
上述查询首先对empl_tbl进行全表扫描,然后分组排序得出结果。SQL解析、执行的时间都可忽略不计,主要时间花在读取数据上,因为当前SQL只是一个查询,没有任何增、删改操作。在数据读取阶段,需要从磁盘读取198个Oracle数据块,一致性读取(query,consistent gets)数据块425块。因为磁盘物理读是非常耗时的,所以该查询执行起来不是特别快。

3.2 游标查询效率分析********************************************************************************
SELECT LAST_NAME
FROM
EMPL_TBL ORDER BY LAST_NAME


call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch   100001      0.71       0.62        198        425          4      100000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total   100002      0.71       0.62        198        425          4      100000

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 62     (recursive depth: 1)******************************************************************************** 该方法的主要耗时也是在数据读取阶段,虽然磁盘读取数据块和一致性读取数据块的数目同Distinct SQL查询相等,但是,因为该方法中的游标要循环100001次,所以上面的SQL的读取会发生100001次,总共读出了100000行数据,这就是为什么使用游标需要1.4秒的原因。下面我们看看使用Bulk Collect会发生什么?

3.3 Bulk Collect的查询效率分析********************************************************************************
SELECT LAST_NAME
FROM
EMPL_TBL


call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.08       0.07          0        425          0      100000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total        2      0.08       0.07          0        425          0      100000

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 62     (recursive depth: 1)
********************************************************************************
虽然这种方法也要读取100000行数据,但是读取操作只发生了1次,并且Bulk Collect语句将所需数据一次性读入内存,所以该方法没有从磁盘读取数据块,所以这种方法比上述两种方法都具有优势,所以执行效率最高。

4. 结论
通过上面的测试和分析,我们可以看到Bulk Collect批查询在某种程度上可以提高查询效率,它首先将所需数据读入内存,然后再统计分析,这样就可以提高查询效率。但是,如果Oracle数据库的内存较小,Shared Pool Size不足以保存Bulk Collect批查询结果,那么该方法需要将Bulk Collect的集合结果保存在磁盘上,在这种情况下,Bulk Collect方法的效率反而不如其他两种方法,有兴趣的读者可以进一步测试。

另外,除了Bulk Collect批查询外,我们还可以使用FORALL语句来实现批插入、删除和更新,这在大批量数据操作时可以显著提高执行效率。

这篇关于Bulk Collect(oracle批量查询)的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/651293

相关文章

Go语言开发实现查询IP信息的MCP服务器

《Go语言开发实现查询IP信息的MCP服务器》随着MCP的快速普及和广泛应用,MCP服务器也层出不穷,本文将详细介绍如何在Go语言中使用go-mcp库来开发一个查询IP信息的MCP... 目录前言mcp-ip-geo 服务器目录结构说明查询 IP 信息功能实现工具实现工具管理查询单个 IP 信息工具的实现服

MySQL索引的优化之LIKE模糊查询功能实现

《MySQL索引的优化之LIKE模糊查询功能实现》:本文主要介绍MySQL索引的优化之LIKE模糊查询功能实现,本文通过示例代码给大家介绍的非常详细,感兴趣的朋友一起看看吧... 目录一、前缀匹配优化二、后缀匹配优化三、中间匹配优化四、覆盖索引优化五、减少查询范围六、避免通配符开头七、使用外部搜索引擎八、分

SQL表间关联查询实例详解

《SQL表间关联查询实例详解》本文主要讲解SQL语句中常用的表间关联查询方式,包括:左连接(leftjoin)、右连接(rightjoin)、全连接(fulljoin)、内连接(innerjoin)、... 目录简介样例准备左外连接右外连接全外连接内连接交叉连接自然连接简介本文主要讲解SQL语句中常用的表

MySQL高级查询之JOIN、子查询、窗口函数实际案例

《MySQL高级查询之JOIN、子查询、窗口函数实际案例》:本文主要介绍MySQL高级查询之JOIN、子查询、窗口函数实际案例的相关资料,JOIN用于多表关联查询,子查询用于数据筛选和过滤,窗口函... 目录前言1. JOIN(连接查询)1.1 内连接(INNER JOIN)1.2 左连接(LEFT JOI

MySQL 中查询 VARCHAR 类型 JSON 数据的问题记录

《MySQL中查询VARCHAR类型JSON数据的问题记录》在数据库设计中,有时我们会将JSON数据存储在VARCHAR或TEXT类型字段中,本文将详细介绍如何在MySQL中有效查询存储为V... 目录一、问题背景二、mysql jsON 函数2.1 常用 JSON 函数三、查询示例3.1 基本查询3.2

MySQL中的交叉连接、自然连接和内连接查询详解

《MySQL中的交叉连接、自然连接和内连接查询详解》:本文主要介绍MySQL中的交叉连接、自然连接和内连接查询,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录一、引入二、交php叉连接(cross join)三、自然连接(naturalandroid join)四

mysql的基础语句和外键查询及其语句详解(推荐)

《mysql的基础语句和外键查询及其语句详解(推荐)》:本文主要介绍mysql的基础语句和外键查询及其语句详解(推荐),本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋... 目录一、mysql 基础语句1. 数据库操作 创建数据库2. 表操作 创建表3. CRUD 操作二、外键

Python实现AVIF图片与其他图片格式间的批量转换

《Python实现AVIF图片与其他图片格式间的批量转换》这篇文章主要为大家详细介绍了如何使用Pillow库实现AVIF与其他格式的相互转换,即将AVIF转换为常见的格式,比如JPG或PNG,需要的小... 目录环境配置1.将单个 AVIF 图片转换为 JPG 和 PNG2.批量转换目录下所有 AVIF 图

详解如何通过Python批量转换图片为PDF

《详解如何通过Python批量转换图片为PDF》:本文主要介绍如何基于Python+Tkinter开发的图片批量转PDF工具,可以支持批量添加图片,拖拽等操作,感兴趣的小伙伴可以参考一下... 目录1. 概述2. 功能亮点2.1 主要功能2.2 界面设计3. 使用指南3.1 运行环境3.2 使用步骤4. 核

Mybatis 传参与排序模糊查询功能实现

《Mybatis传参与排序模糊查询功能实现》:本文主要介绍Mybatis传参与排序模糊查询功能实现,本文通过实例代码给大家介绍的非常详细,感兴趣的朋友跟随小编一起看看吧... 目录一、#{ }和${ }传参的区别二、排序三、like查询四、数据库连接池五、mysql 开发企业规范一、#{ }和${ }传参的