Oracle SQL efficiency preparation

• Preparations

1 second = 1000 milliseconds

2. Oracle built-in FunctionDbms_utility.get_timeReference: http://blog.csdn.net/linminqin/article/details/6601897

3 calculation time difference

 Create   Or   Replace   Function  F_test_time_efficiency (in_testsql1  Varchar  )  Return    Number   Is  T1 Integer ; --  Time Point 1 T2 Integer ; --  Time Point 2 V_diff Number ; --  Time Difference    /*  **************************************** * *********************** Name: get_parent_deptno_by_level * purpose: -- view the function * Author: -- wangchao * createdate: -- * updatedate: --*************************************** ********************  */ --  Dbms_utility.get_time, in 1/100 seconds  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. Data Preparation

4.1 prepare the table structure

 --  Create a person table  Create     Table  Person (PID  Integer   Primary   Key , Pname  Varchar2 ( 10 ) Not   Null  ) Tablespace Cici;  --  Create Department  Create   Table  Department (depid  Integer   Primary   Key  , Deptname Varchar2 ( 10 ) Not   Null  ) Tablespace Cici;  --  Add Field  Alter   Table Person Add Depid Integer  ;  --  Add a foreign key constraint person --> Department  -- Deptid --> deptid  Alter   Table  Person  Add   Constraint  Fk_person_dept  Foreign   Key (Depid) References Department (depid );

4.2 Stored Procedure for batch data insertion

 

 Create   Or   Replace   Procedure Batch_insert_t_dept (insertno In   Integer ) Is  /*  * Name: batch_insert_tablea_dept * purpose: -- batch insert data to table Department * imput: -- number of batch insert data * 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 there is no data in the table, insert a data entry first.            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  ;  --  Insert data with at least one data entry in the table            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 (); rpose: -- batch insert data to table person * imput: -- insertno: Number of batch insert data in_dept_no: the foreign key is used to reference the Department deptid field * 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 there is no data in the table, insert a data entry first.            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  ;  --  Insert data with at least one data entry in the table            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 (); rpose: -- batch insert data to the table person and table Department * imput: -- insertdeptno: the number of data inserted to the Department table in batches: number of data inserted to the person table in batches * output: -- N/A * Author: -- Cici * createdate: -- 2012, 12, 30 * updatedate: --*************************************** ********************  */  V_pid  Integer ; V_insert_no  Integer  ; I  Integer  ;  Begin  V_insert_no:  =  Insertdeptno;  --  Get deptid from department table    Select   Max (Depid) Into V_pid From  Department; If V_pid Is   Not   Null   Then  Batch_insert_t_dept (v_insert_no );  --  Insert insertdeptno data to the Department table      --  Insert data to the person table      --  Each newly added deptid inserts insertpersonno data records.      For I In V_pid +  1 ... V_pid +  Insertdeptno loop batch_insert_t_person (insertpersonno, I );  End  Loop;  --  If the dept table is empty    Else  V_pid:  =   1  ; Batch_insert_t_dept (v_insert_no );  --  Insert insertdeptno data to the Department table     --  Insert data to the person table      --  Each newly added deptid is inserted with insertpersonno. The data department number starts from 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 Data Preparation

 Insert 1000 records to the parent table

Insert 1000*100 records into the sub-table (each parent table corresponds to 100 sub-Table Records)

 
Call batch_insert_t_persondept (1000,100);
 

Add personnel whose team ID is null.

 
Call batch_insert_t_person (100,Null);
 

 

• 5. Clear Data

Truncate TableT_person;Alter TablePerson disableConstraintFk_person_dept;Truncate TableT_department;Alter TablePerson enableConstraintFk_person_dept;
 

• 6. Clear Cache

 
AlterSystem flush buffer_cache;AlterSystem flush shared_pool;