1、一个存储过程:向表里插入100000条数据
create or replace procedure proc1
as
begin
for i in 1..100000
loop
execute immediate
'insert into t values('||i||')';
commit;
end loop;
end;
as
begin
for i in 1..100000
loop
execute immediate
'insert into t values('||i||')';
commit;
end loop;
end;
drop table t purge;
create table t (x int);
--将共享池清空
alter system flush shared_pool;
create table t (x int);
--将共享池清空
alter system flush shared_pool;
很慢:
查询解析多少次:select t.sql_text,t.sql_id,t.parse_calls,t.executions from v$sql t where sql_text like '%insert into t values%'
发现解析很多次,
2、绑定变量:只解析一次
create or replace procedure proc2
as
begin
for i in 1..100000
loop
execute immediate
'insert into t values(:x)' using i;
commit;
end loop;
end;
as
begin
for i in 1..100000
loop
execute immediate
'insert into t values(:x)' using i;
commit;
end loop;
end;
drop table t purge;
create table t (x int);
--将共享池清空
alter system flush shared_pool;
create table t (x int);
--将共享池清空
alter system flush shared_pool;
select t.sql_text,t.sql_id,t.parse_calls,t.executions from v$sql t where sql_text like '%insert into t values%'
只解析一次:
3、静态改写:
create or replace procedure proc3
as
begin
for i in 1..100000
loop
execute immediate
insert into t values(i);
commit;
end loop;
end;
drop table t purge;
create table t (x int);
--将共享池清空
alter system flush shared_pool;
create table t (x int);
--将共享池清空
alter system flush shared_pool;
4、批量提交:
create or replace procedure proc4
as
begin
for i in 1..100000
loop
insert into t values(i);
end loop;
commit;
end;
drop table t purge;
create table t (x int);
--将共享池清空
alter system flush shared_pool;
create table t (x int);
--将共享池清空
alter system flush shared_pool;
5、集合写法:
insert into t select rownum from dual connect by level<=100000;
drop table t purge;
create table t (x int);
--将共享池清空
alter system flush shared_pool;
create table t (x int);
--将共享池清空
alter system flush shared_pool;
6、直接路径:
7、并行设置(自己测试电脑没有多个cpu,不能测试)
create table t nologging parallel 64 as select rownum x from dual connect by level<=100000;
总结:该sql对深入了解oracle的体系结构有一定的帮助。