ORACLE SQL效率 準備工作

• 準備工作

　　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; --時間點2v_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;-- 建立 departmentcreate table DEPARTMENT (       DEPID INTEGER PRIMARY KEY,       DEPTNAME VARCHAR2(10) NOT NULL )TABLESPACE CICI;-- 添加欄位alter table PERSON add DEPID INTEGER ;-- 添加外鍵約束 person -->department--              deptid -- >deptidALTER TABLE PERSONADD CONSTRAINT fk_PERSON_DEPTFOREIGN 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;