2014-09-25 Created By BaoXinjian
一、摘要
集合是Oracle开发中经常遇到的情况,Oracle集合分为三种情况:索引表集合(index by table)、嵌套表集合(nested table)、可变集合(varry table)。
PL/SQL中没有数组的概念,他的集合数据类型和数组是相似的。
- 其中varray table的元素是有数量限制的,index_by table和nexted table是没有这个限制的。
- index-by table是稀疏的,也就是说下标可以不连续,varray table则是紧密的,他的下标没有间隔。
- index_by table不能存储在数据库中,但是nexted table和varray table可以被存储在数据库中。
集合在使用时必须先使用type进行定义方可使用
二、集合的定义方式
1. 索引表集合 index by table
TYPE test_type IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
遍历方式:这种数组不需要事先指定上限,下标可以不连续,可以是0或负数。
数据库存放:不可以存放在数据库中
2. 嵌套表集合 nested table
TYPE test_type IS TABLE OF VARCHAR2(10);
遍历方式:下标从1开始,不能超过数组所有元素的总和,当下标超出允许范围时
数据库存放:可以存放在数据库中
3. 可变集合 varry table
TYPE test_type IS ARRARY(5) OF VARCHAR2(10);
遍历方式:由于类型定义时的元素个数限制,所以TYPE3的变量在使用时最大的元素个数不能超过5个。
数据库存放:可以存放在数据库中
三、数组的存放在数据库方式
案例: 创建一个存在嵌套表集合类型栏位的表
Step1. 创建记录类型Record Type
CREATE OR REPLACE TYPE bxj_record
AS
OBJECT (bxj_record_id NUMBER,
bxj_record_date DATE,
bxj_record_description VARCHAR2 (10));
Step2. 创建嵌套表集合类型nested table type
CREATE OR REPLACE TYPE bxj_nested_table AS TABLE OF bxj_record;
Step3. 创建测试表,用以存放集合
CREATE TABLE bxj_test_table
(
invoice_id NUMBER,
invoice_num VARCHAR2 (10),
invoice_date DATE,
invoice_nested bxj_nested_table
)
NESTED TABLE invoice_nested STORE AS bxj_record_table
Step4. 查看所建立的Object
Step5. 插入测试资料
DECLARE
v_record1 bxj_record := bxj_record (1, SYSDATE, '2014/09/29');
v_record2 bxj_record := bxj_record (2, SYSDATE, '2014/09/30');
v_invoice_id NUMBER;
v_invoice_num VARCHAR2 (10);
v_invoice_date DATE;
v_invoice_nested bxj_nested_table := bxj_nested_table ();
BEGIN
v_invoice_id := 1;
v_invoice_num := 'INVOICE_01';
v_invoice_date := SYSDATE;
v_invoice_nested.EXTEND;
v_invoice_nested (1) := v_record1;
v_invoice_nested.EXTEND;
v_invoice_nested (2) := v_record2;
INSERT INTO bxj_test_table
VALUES (v_invoice_id,
v_invoice_num,
v_invoice_date,
v_invoice_nested);
COMMIT;
END;
Step6. 查看表数据
四、数组优化方式 (arraysize / bulk collect)
客户端Arraysize的设置实际上可以理解为设置数组的大小来保存sql调用的返回值。设置适当的arraysize有几个好处:
1.减少读data block的次数,也就是consistent gets的次数。
假设一个block有n行,如果每次取一行,则对该block的逻辑读次数就是n,尽管逻辑读是内存读,速度很快,但并发量很大时也会使数据库的压力很大。
如果每次取>n行,则该block只需读一次。因此合理设置arraysize能大大减轻数据库压力。
2.在PLSQL中减少plsql引擎和sql引擎的切换次数,提高效率
3.通过数组存放结果,减少客户端和数据库服务器的调用次数,减少往返时间
五、PLSQL Bulk Collect设置数组访问的方法
案例:测试Bulk Collect访问数组的限制对consistent gets和script run time的影响
测试一:不使用bulk collect,一次fetch一条记录
Step2. 运行测试脚本
DECLARE
CURSOR c1
IS
SELECT object_name
FROM all_objects
WHERE ROWNUM < 100000;
v_id VARCHAR2 (30);
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO v_id;
EXIT WHEN c1%NOTFOUND;
NULL;
END LOOP;
CLOSE c1;
END;
/
Step3. 查看运行后consisten gets变化 = 235622 -109441 = 126811
测试二:设置bulk collect,一次取100条
Step1. 运行测试脚本
DECLARE
TYPE VarcharTab
IS
TABLE OF VARCHAR2 (30)
INDEX BY BINARY_INTEGER;
CURSOR c1
IS
SELECT object_name
FROM all_objects
WHERE ROWNUM < 100000;
v_id VarcharTab;
BEGIN
OPEN c1;
LOOP
FETCH c1 BULK COLLECT INTO v_id LIMIT 100;
EXIT WHEN c1%NOTFOUND;
NULL;
END LOOP;
CLOSE c1;
END;
/
Step2. 查看运行后consisten gets变化 = 284623 -235622= 49001
测试三:设置bulk collect,一次取5000条
Step1. 运行测试脚本
DECLARE
TYPE VarcharTab
IS
TABLE OF VARCHAR2 (30)
INDEX BY BINARY_INTEGER;
CURSOR c1
IS
SELECT object_name
FROM all_objects
WHERE ROWNUM < 100000;
v_id VarcharTab;
BEGIN
OPEN c1;
LOOP
FETCH c1 BULK COLLECT INTO v_id LIMIT 5000;
EXIT WHEN c1%NOTFOUND;
NULL;
END LOOP;
CLOSE c1;
END;
/
Step2. 查看运行后consisten gets变化 = 332559 -284623= 47936
测试结果
- 测试一:consisten gets变化 = 235622 -109441= 126811
- 测试二:consisten gets变化 = 284623 -235622= 49001
- 测试三:consisten gets变化 = 332559 -284623= 47936
consistent_gets是从回滚段中读到的前映(或叫读取一致性影象), 看见的数据是查询开始的时间点的,所以若存在block在查询开始后发生了变化的情况,则必须产生 before image 然后读数据,这就是一致读的含义
查询就是表示 consistent gets (query mode),因为查询要保证所获取的数据的时间点的一致性,所以叫一致读,
即使是从当前 buffer 获得的数据,也叫 consistent gets ,这仅仅表达一种模式一种期望,并不表示真实的是从 当前buffer 获得 还是从回滚段获取数据产生的 bufore image 。
网络上的一个测试结果
六、SQLPLUS设置数组访问的方法
Sqlplus的arraysize参数表示数据库一次返回给客户端的行数,缺省是15。
如果返回数据量大,可以调大此参数:Set arraysize nnnn, 该参数最大为5000。
测试一:缺省arraysize
select * from all_objects where rownum <100000;
查看执行时间
测试二:SQL> set arraysize 1000
select * from all_objects where rownum <100000;
查看执行时间
Thanks and Regards
参考:http://www.jb51.net/article/35424.htm