下面来写个通过job实现多线程插入的例子,但并为与其他比对效率,只供参考
--创建临时表
CREATE TABLE TT1 AS SELECT * FROM DBA_OBJECTS WHERE 1=0; CREATE TABLE TT2 AS SELECT * FROM DBA_OBJECTS; --数据分批插入参数表 DROP TABLE JOB_PARMS; CREATE TABLE JOB_PARMS ( JOB NUMBER PRIMARY KEY, LO_RID INT, HI_RID INT );
--创建插入的存储过程
CREATE OR REPLACE PROCEDURE proc_test(p_job IN NUMBER) IS l_rec job_parms%ROWTYPE; BEGIN SELECT * INTO l_rec FROM job_parms WHERE job = p_job; INSERT INTO tt4 SELECT a.owner, a.object_name, a.subobject_name, a.object_id, a.data_object_id, a.object_type, a.created, a.last_ddl_time, a.timestamp, a.status, a.temporary, a.generated, a.secondary FROM (SELECT rownum rn, tt3.* FROM tt3 WHERE rownum <= l_rec.hi_rid) a WHERE a.rn >= l_rec.lo_rid; DELETE FROM job_parms WHERE job = p_job; COMMIT; END;
--DIY 并行调度程序块 DECLARE l_job NUMBER; c_index NUMBER; --插入的数量总数 s_index INT := 0; --插入的开始index e_index INT := 0; --插入的结束index cq_index INT := 20; --循环的次数 num_increase INT := 0; --增量累加 v_i INT := 0; --计数器 BEGIN SELECT COUNT(*) INTO c_index FROM tt3; num_increase := ceil(c_index / cq_index); WHILE cq_index > v_i LOOP v_i := v_i + 1; s_index := 1 + num_increase * (v_i - 1); IF (v_i = 20) THEN --当等于循环次数则修改结束的index e_index := c_index; ELSE e_index := num_increase * v_i; END IF; dbms_job.submit(l_job, 'PROC_TEST(JOB);'); INSERT INTO job_parms (job, lo_rid, hi_rid) VALUES (l_job, s_index, e_index); END LOOP; END; / --输入COMMIT开始执行 COMMIT; --查看job是否执行完成 SELECT * FROM DBA_JOBS_RUNNING;
本例来自下面网址,只进行了粗略测试,能够实现所需的功能,对于是否能提高效率不能确定, 今天时间有点赶就先写到这里,下次抽空对多种方法进行对比。今日毕!
感谢:http://yangeoo.iteye.com/blog/1867074