学步园

• 准备工作

　　1 1秒 = 1000毫秒

      2 测试时间差的工具 oracle自带函数dbms_utility.get_time   参考：http://blog.csdn.net/linminqin/article/details/6601897

      3 计算时间差

create or replace function F_TEST_TIME_efficiency
(IN_TESTSQL1 varchar)
RETURN  number IS 
t1 INTEGER;--时间点1 
t2 INTEGER; --时间点2
v_diff number;--时间差
  /*************************************************************** 
  *NAME : GET_PARENT_DEPTNO_BY_LEVEL
  *PURPOSE : --查看SQL过程执行时间差的function 
   *Author : --WangChao
  *CreateDate : -- 
  *UpdateDate : -- 
  ************************************************************/
--DBMS_UTILITY.get_time 时间单位1/100秒
begin
       t1:= DBMS_UTILITY.get_time; 
        execute immediate   IN_TESTSQL1  ; 
       t2:= DBMS_UTILITY.get_time;
       v_diff:=t2-t1;
       dbms_output.put_line('Time differences is  '||v_diff);
return v_diff;
end;

• 4 数据准备

4.1 表结构准备

-- 创建person表
create   table PERSON (
       PID INTEGER PRIMARY KEY,
       PNAME VARCHAR2(10) NOT NULL 
)TABLESPACE CICI;
-- 创建 department
create table DEPARTMENT (
       DEPID INTEGER PRIMARY KEY,
       DEPTNAME VARCHAR2(10) NOT NULL 
)TABLESPACE CICI;
-- 添加字段
alter table PERSON add DEPID INTEGER ;
-- 添加外键约束 person -->department
--              deptid -- >deptid
ALTER TABLE PERSON
ADD CONSTRAINT fk_PERSON_DEPT
FOREIGN KEY (DEPID) REFERENCES DEPARTMENT (DEPID);

4.2 批量插入数据的存储过程

 

create or replace procedure BATCH_INSERT_T_DEPT(insertNo in integer) is

/*    *NAME : BATCH_INSERT_TABLEA_DEPT
  *PURPOSE : --向表DEPARTMENT批量插入数据
  *IMPUT : -- 批量插入数据的数量
  *OUTPUT : -- N/A
  *Author : -- CICI
  *CreateDate : -- 2012、12、30
  *UpdateDate : --
  ************************************************************/

  V_DEPT_ID INTEGER;
  V_INSERT_NO INT;
  I INTEGER;
  BEGIN

          SELECT MAX(DEPID) into V_DEPT_ID
           FROM  DEPARTMENT;
         --如果表中没有数据 则先插入一条数据
          IF V_DEPT_ID IS NULL  THEN
              INSERT INTO  DEPARTMENT(DEPID,DEPTNAME)
                 VALUES(1,'DEPT_'||1);
               V_DEPT_ID:=1;
               V_INSERT_NO:=insertNo-1;
          ELSE
              V_INSERT_NO:=insertNo;
          END IF;

          --表中至少有一条数据的前提之下 插入数据
          FOR  I IN V_DEPT_ID+1 ..V_DEPT_ID+V_INSERT_NO loop

          INSERT INTO  DEPARTMENT(DEPID,DEPTNAME)
                 VALUES(I,'DEPT_'||I);
          end LOOP;
          COMMIT;
end  BATCH_INSERT_T_DEPT;

 

create or replace procedure BATCH_INSERT_T_PERSON(insertNo in integer,in_dept_no IN INTEGER) is

/*    *NAME : BATCH_INSERT_TABLEA_DEPT
  *PUCALL BATCH_INSERT_TABLEA_PERSON(50,1);RPOSE : --向表person批量插入数据
  *IMPUT : -- insertNo:批量插入数据的数量  in_dept_no:外键 用来引用department deptid字段
  *OUTPUT : -- N/A
  *Author : -- CICI
  *CreateDate : -- 2012、12、30
  *UpdateDate : --
  ************************************************************/

  V_DEPT_ID INTEGER;
  V_PID INTEGER;
   V_INSERT_NO INT;
  I INTEGER;
  BEGIN
          IF in_dept_no IS NOT NULL THEN
                    V_DEPT_ID:=in_dept_no;
          END IF;

          SELECT MAX(PID) into V_PID
           FROM  PERSON;

         --如果表中没有数据 则先插入一条数据
          IF V_PID IS NULL  THEN
              INSERT INTO PERSON(PID,PNAME,DEPID)
                 VALUES(1,'PNAME_'||1,V_DEPT_ID);
               V_PID:=1;
               V_INSERT_NO:=insertNo-1;
          ELSE
              V_INSERT_NO:=insertNo;
          END IF;

          --表中至少有一条数据的前提之下 插入数据
          FOR  I IN V_PID+1 ..V_PID+V_INSERT_NO loop

          INSERT INTO  PERSON(PID,PNAME,DEPID)
                 VALUES(I,'PNAME_'||I,V_DEPT_ID);
          end LOOP;
       --   COMMIT;
end  BATCH_INSERT_T_PERSON;

create or replace procedure BATCH_INSERT_T_PERSONDEPT(insertdeptNo   in integer,
                                                    insertPersonNo IN INTEGER) is

  /*    *NAME : BATCH_INSERT_PERSONDEPT
  *PUCALL BATCH_INSERT_PERSONDEPT(50,1);RPOSE : --向表person 和 表 department 批量插入数据
  *IMPUT : -- insertdeptNo:批量插入department表的数据的数量  insertPersonNo:批量插入person表的数据的数量

  *OUTPUT : -- N/A
  *Author : -- CICI
  *CreateDate : -- 2012、12、30
  *UpdateDate : --
  ************************************************************/

  V_PID       INTEGER;
  V_INSERT_NO INTEGER;
  I           INTEGER;

BEGIN

  V_INSERT_NO := insertdeptNo;
  --从department表 取得DEPTID
  select max(depid) into v_pid from  department;
  if v_pid is not null then
    BATCH_INSERT_T_DEPT(V_INSERT_NO); -- 向department表插入 insertdeptNo条数据
    --向 person表 插入数据
    --每个新增加的deptid 都对应插入insertPersonNo 条数据
    for I in v_pid + 1 .. v_pid + insertdeptNo loop
      BATCH_INSERT_T_PERSON(insertPersonNo, I);
    end loop;
    -- 如果dept表为空
  else
    v_pid := 1;
    BATCH_INSERT_T_DEPT(V_INSERT_NO); -- 向department表插入 insertdeptNo条数据
    --向 person表 插入数据
    --每个新增加的deptid 都对应插入insertPersonNo 条数据 部门编号从1开始
    for I in v_pid ..v_pid + insertdeptNo - 1 loop
      BATCH_INSERT_T_PERSON(insertPersonNo, I);
    end loop;
  end if;

end BATCH_INSERT_T_PERSONDEPT;

 

4.3 数据准备

 向父表插入1000条记录

子表插入1000*100 条记录（每个父表对应100条子表记录）

CALL BATCH_INSERT_T_PERSONDEPT(1000,100);

另外添加一些部门ID为NULL 的人员信息

call batch_insert_t_person(100,null);

 

• 5 清空数据

TRUNCATE TABLE T_PERSON;
ALTER TABLE  PERSON DISABLE CONSTRAINT FK_PERSON_DEPT ;
TRUNCATE TABLE T_DEPARTMENT;
ALTER TABLE  PERSON ENABLE CONSTRAINT FK_PERSON_DEPT ;

•  6 清空缓存

ALTER SYSTEM FLUSH BUFFER_CACHE; 
alter system flush shared_pool;