- 浏览: 224652 次
- 性别:
- 来自: 上海
文章分类
最新评论
-
kandari:
很全,收藏
oracle相关知识 -
若见三生石:
,辛苦楼主!感谢为大伙敏捷开发做出贡献!
Oracle中的二进制、八进制、十进制、十六进制相互转换函数 -
若见三生石:
你好,要定义的类和类型怎么写呢?
Oracle中的二进制、八进制、十进制、十六进制相互转换函数 -
greatwqs:
...
PLSQL操作文件 -
sun17921:
var areaCode ={11:"北京" ...
身份证验证JS
原文: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语句来实现批插入、删除和更新,这在大批量数据操作时可以显著提高执行效率。
发表评论
-
ORACLE 中ROWNUM
2012-02-03 11:16 827对于 Oracle 的 rownum 问题,很多资料都说不 ... -
timesten内存数据库
2011-11-10 14:18 3286为什么现在内存型数据 ... -
PL/SQL集合方法、集合赋值、比较集合
2011-08-12 17:53 1237http://www.cnblogs.com/lanzi/ar ... -
oracle(集合类型)
2011-07-08 18:47 1078http://blog.sina.com.cn/s/blog_ ... -
PL/SQL Developer 设置快捷键
2011-06-20 11:47 10381、登录后默认自动选中My Objects 默认 ... -
PD常用设置和快捷键
2011-06-15 16:37 2214最近用powerDesinger ... -
PL/SQL Developer 使用技巧
2011-02-17 12:32 989http://hi.baidu.com/chssheng200 ... -
ASCII码对应表
2010-10-19 20:15 1551chr(9) tab空格 chr(10) 换行 chr(13) ... -
oracle的to_char
2010-08-20 15:02 991The following are number exam ... -
Oracle系列:Cursor
2010-07-29 17:28 9441,什么是游标? ... -
oracle job 和它的时间设置
2010-07-29 17:27 1249描述 INTERVAL参 ... -
oracle字符替换
2010-06-24 20:16 3283select REGEXP_REPLACE( 'AB ... -
update机制
2010-05-22 04:56 972就 oracle 而言 update 的时候,如果存在索引字 ... -
oracle commit
2010-05-21 09:40 1841http://blog.csdn.net/knowhow/ ... -
Oracle中的二进制、八进制、十进制、十六进制相互转换函数
2010-05-13 12:41 4502CREATE OR REPLACE PACKAGE pkg_n ... -
oracle相关知识
2010-04-28 00:16 10251.曾经不小心把开发库的数据库表全部删除,当时吓的要死。结果找 ... -
index rebuild online
2010-04-23 16:30 1304SQL> select segment_name ,se ... -
Oracle优化器Optimizer
2010-04-23 14:13 842Oracle在执行一个SQL之前, ... -
改变表空间的大小
2010-04-20 15:13 1132方法 : 如果你想把 aa.dbf 这个数据文件给缩小。 ... -
oracle的锁详解
2010-04-19 10:36 1639Oracle 多粒度锁机制介绍 根据保护对象的不同, ...
相关推荐
Oracle 中使用 fetch bulk collect into 批量效率的读取游标数据 Oracle 中的游标数据读取是数据库开发中非常常见的操作。通常,我们使用 fetch some_cursor into var1, var2 形式来获取游标数据,当游标中的记录数...
### Oracle批量处理(Bulk Collect) #### 一、概述 在Oracle数据库中,`BULK COLLECT` 是一种高效的数据检索机制,它允许一次性从数据库中提取多行数据,并将其存储在一个PL/SQL集合中。与传统的游标逐行处理相比,...
BULK COLLECT(成批聚合类型)和数组集合type类型is table of 表%rowtype index by binary_integer用法笔记。 例1: 批量查询项目资金账户号为 “320001054663”的房屋账户信息并把它们打印出来 . DECLARE TYPE acct_...
详细介绍了使用 BULK COLLECT 进行批量操作 提高sql的执行效率 使用MERGE INTO USING 一条sql搞定 新增和修改 使用connect by 进行递归树查询
这段代码示例虽然较为杂乱,但可以提炼出的关键点主要包括:如何使用`BULK COLLECT`来提高查询效率、如何声明和使用表类型(如`TABLE OF`)以及如何在PL/SQL过程中实现数据的批量处理。 ### Oracle BULK COLLECT...
Bulk Collect 允许一次性地将查询结果集批量加载到一个集合(collections)中,而不是传统的逐行处理,从而减少数据库 I/O 和循环开销,提高性能。 在 Oracle8i 中引入的 Bulk Collect 特性,适用于 select into、...
在Oracle数据库中,BULK COLLECT 还可以与其他操作结合使用,如BULK INSERT用于批量插入数据,或者与FORALL一起使用,进行批量更新或删除。这些技术通常在处理大数据量时,能显著提升应用程序的性能,并减少资源消耗...
在Oracle数据库中,Bulk Collect是一个非常实用的特性,它允许我们一次从游标(Cursor)中批量地获取多行数据,极大地提高了处理大量数据时的性能。本文将深入讲解如何在PL/SQL中使用Bulk Collect实现Cursor的批量...
#### 一、批量处理技术:`BULK COLLECT` 批量处理是提高数据处理效率的重要手段之一。通过一次性获取多条记录,可以显著减少数据库的I/O操作次数,从而提升整体性能。在提供的代码示例中,`BULK COLLECT`指令被用来...
`BULK COLLECT INTO` 是Oracle PL/SQL中用于高效地从查询结果集中一次性获取多行数据到集合(通常是数组)中的一个关键字。这一特性极大地提高了数据处理效率,尤其是在需要处理大量数据时尤为明显。相比传统的逐条...
本篇文章将详细探讨“Oracle批量”操作,特别是批量删除的实现方法。 一、Oracle批量删除 1. **SQL DELETE语句**:最基础的批量删除方式是使用SQL的DELETE语句配合WHERE子句。例如,如果你想要删除特定表(如:TBL...
BULK COLLECT 是一个用于加速查询的特性,它允许一次性地将查询结果集加载到集合(collections)中,而不是逐行处理。这样避免了频繁的上下文切换,减少了PL/SQL和SQL引擎之间的交互次数,从而提升了整体性能。以下...
在PL/SQL环境中,可以通过循环结构配合BULK COLLECT和INTO关键字实现批量插入操作。这种方法适用于已经存在于内存中的数据,例如从其他数据源获取的数据。批量处理可以显著提高性能,减少与数据库的交互次数,从而...
例如,我们可以从CSV文件中读取数据,然后使用BULK COLLECT INTO将这些数据批量插入到表中。 3. **FORALL**:在收集了大量数据后,可以使用FORALL语句一次性将它们插入到数据库中,避免了循环中的单次插入,提高了...
Oracle 12c引入了DBMS_BULK_INSERT包,提供了更高效的批量插入方式。使用该包前,确保已启用“direct path insert”权限,然后调用如下: ```plsql BEGIN DBMS_BULK_INSERT(target_table => 'YOUR_TABLE', file_...
例如,使用BULK COLLECT语句进行大规模数据读取,或者使用流式处理减少内存占用。 综上所述,"照片批量导入导出"是一个涉及数据库操作、批处理、图像处理和编程技术的综合问题。理解并熟练运用这些知识点,可以有效...
BULK COLLECT是一种高效的批量数据检索方法,可以显著提高查询性能。以下是一个具体的示例: ```sql -- 使用BULK COLLECT查询部门ID为50的所有员工 DECLARE TYPE emp_table_type IS TABLE OF employees%ROWTYPE; ...