Basic query optimization and sub-query optimization in Oracle database _oracle

1. Reasonable sorting of query conditions

Oracle uses a bottom-up sequence to parse where writes, and from an optimized performance perspective, it is recommended that the conditions that filter out a large number of record rows be written at the end of the WHERE clause, and the table

The connection conditions between are placed before other where clauses, that is, the easy to determine the conditions of the first judgment processing, so that after filtering out as many records as possible before the equivalent connection, can improve the retrieval efficiency.

For example:

SELECT empno, ename, Job, Sal, Dept.deptno, dname from 

EMP, dept 

WHERE Emp.deptno = dept.deptno and Emp.deptno = 2 0;

Queries are more efficient than the following statements:

Selete empno, ename, Job, Sal, Dept.deptno, dname from

EMP, dept

WHERE Emp.deptno = and Emp.deptno = DEPT.DEPTN O

2. Use table alias in connection

When making a connection query, it is recommended that you use the alias of the table in the SQL statement and prefix the alias with each field. This reduces the time to parse and avoids ambiguity in the field name (using multiple

A field with the same name as appears in the table, resulting in a syntax error. For example, the following statement:

SELECT E.empno, E.ename, E.job, E.sal, E.deptno, d.name from

emp E, dept d

WHERE e.deptno = D.deptno and E.deptno = 20;

3. Replace distinct with exists

If you want to remove duplicate rows from the results when you are connecting between tables in a one-to-many relationship, consider replacing distinct with exists (combined with subqueries).

For example:

Select Deptno, dname from Dept D where EXIST (select ' Y ' from emp e where e.deptno = D.deptno);

4. Where to replace having

Because the SELECT statement is executed in the following order: The WHERE clause, the GROUP BY clause, then the SELECT query, then the HAVINT clause, and finally the orders by clause, so that the group

Query, if the filter condition does not involve grouping calculations, you should replace the having-specified filter condition with the WHERE statement

For example:

SELECT deptno, avg (SAL) from EMP

WHERE Deptno in (a) GROUP by

Deptno;

Efficiency will be higher than the following

SELECT deptno, avg (SAL) from EMP

WHERE Deptno in (a) GROUP by

Deptno;

Of course, if the filter conditions of a grouped query design group calculations, they can only be specified in the HAVING clause.

5. Sub-query optimization

1 when the data is large, the performance of the subquery will be greatly affected;

2 in the subquery used the function To_char (add_months (To_date (tb.rptdate, ' yyyymm '), ' yyyymm '), the execution function also has the big influence to the system when querying the large amount of data.

Here's an example:

Createor Replace procedure rpt_dyhgl_year (rptyear in Varchar2,--year yyyy--rpttype in varchar2,--report type, 1-year newspaper, 
2 Quarterly Ref_cursor out Sys_refcursor--return cursor) as--variable declaration ... 
  Begin delete from ywtj_rpt_dyhgl_year where rptyear = rptyear and Rpttype = ' 1 '; 
 Commit --Query raw data v_sql: = ' Select Rptdate, jcdlb, Jcdlbname, sum (NVL (runtime,0)) Sumruntime,sum (NVL (buhegetime,0)) Buhegetime, C Ount (jcdcount) jcdcount, sum (NVL (tongbiruntime,0)) tongbiruntime, sum (NVL (tongbibhg,0)) TONGBIBHG from (Select Rpt  Date, Runtime,r.chaoshangxian+r.chaoxiaxian buhegetime, R.objid, 1 Jcdcount, (select Runtime Fromywtj_rpt_dyhgl_month TB Whereto_char (Add_months (To_date (tb.rptdate, ' yyyymm '), "' yyyymm ') = R.rptdateand Tb.objid = R.objid) Tongbiruntime, (select Chaoshangxian + Chaoxiaxian fromywtj_rpt_dyhgl_month TB Whereto_char (add_months (to_date Date, ' yyyymm '), ' (' yyyymm ') = R.rptdateand Tb.objid = R.objid) TONGBIBHG, (select Jcdlb from Jczl_dwJG d Whered.objid = R.objid) jcdlb, (select Jcdlbname from Dic_jcdlb wherejcdlbid = (select Jcdlb from JCZL_DWJG whe Re objid = R.objid)) Jcdlbname from Ywtj_rpt_dyhgl_month r where Rptdate =: rptyear) Group by Rptdate, JCDLB, JCD 
  Lbname ORDER by Rptdate, JCDLB '; --Insert new data V_sqlrptinsert: = ' Insert Intoywtj_rpt_dyhgl_year (Rptyear,rptdate,rpttype) VALUES (: Rptyear,:rptdate,: 
  Rpttype) '; 
  V_sqlrptupdate: = '; 
   --Calculates the month data for 1--12 months for Rptmonth in 1..12 Loop execute immediate v_sqlrptinsertusing rptyear, Rptmonth, ' 1 '; 
   Commit Open v_cur for v_sql using Rptyear | | 
   Lpad (Rptmonth, 2, ' 0 '); Loop Fetch V_cur INTOV_RPTDATE,V_JCDLB,V_JCDNAME,V_SUMRUNTIME,V_SUMBUHEGETIME,V_JCDCOUNT,V_TONGBIRUNTIME,V_ 
     TONGBIBHG; 
     Exit when V_cur%notfound; 
     --Calculate the qualified rate ... 
    --Update month data ... 
    End Loop; 
  Close v_cur; 
  End Loop; 
  --Update cumulative data ... 
  Commit --Calculate the comprehensive qualified rate open Ref_cursor for select * Fromywtj_rpt_dyhgl_year r wherE r.rptyear = rptyear and R.rpttype = 1 order byr.rptdate; 
 End Rpt_dyhgl_year;

The function is working properly and the query time is about 30-40 seconds.

Then, after the report is deployed to the running environment, verify the function, found that 2, 3 minutes report did not come out! Many attempts, then finally came out the result: more than 940 seconds, 15 minutes more! So the user is certainly not acceptable! Using the performance analysis of Pl/sql Dev to analyze the execution process of rpt_dyhgl_year, it is found that almost all the time is in the

Fetch v_cur into V_RPTDATE,V_JCDLB,V_JCDNAME,V_SUMRUNTIME,V_SUMBUHEGETIME,V_JCDCOUNT,V_TONGBIRUNTIME,V_TONGBIBHG;

(during the process of converting to program execution, performance analysis found that the main time was by Dr.) Read () occupies, and is consistent with the analysis of the process. ）

The question should be in the fetch corresponding query, the

V_sql: = ' Select Rptdate, JCDLB, Jcdlbname,sum (NVL (runtime,0)) sumruntime, sum (NVL (buhegetime,0)) Buhegetime,count ( Jcdcount) Jcdcount, sum (NVL (tongbiruntime,0)) tongbiruntime, sum (NVL (tongbibhg,0)) TONGBIBHG from (select Rptd Ate, Runtime,r.chaoshangxian+r.chaoxiaxian buhegetime, R.objid, 1 Jcdcount, (select Runtime Fromywtj_rpt_dyhgl_mont H TB Whereto_char (Add_months (To_date (tb.rptdate, ' yyyymm '), "' yyyymm ') = R.rptdateand Tb.objid = R.objid) Tongbiruntime, (select Chaoshangxian + Chaoxiaxian fromywtj_rpt_dyhgl_month TB Whereto_char (add_months (to_date Ptdate, ' yyyymm '), ' (' yyyymm ') = R.rptdateand Tb.objid = R.objid) TONGBIBHG, (select Jcdlb from Jczl_dwjg d whe Red.objid = R.objid) jcdlb, (select Jcdlbname from Dic_jcdlb wherejcdlbid = (select Jcdlb from JCZL_DWJG where obj id = r.objid)) Jcdlbname from Ywtj_rpt_dyhgl_month r where Rptdate =: rptyear) Group by Rptdate, JCDLB, JCDLB     Name ORDER by Rptdate, JCDLB '; --① 
 

Take out alone to execute, about 5.7 seconds more.
And then put the subquery in it:

Selectrptdate, runtime, R.chaoshangxian+r.chaoxiaxian buhegetime, R.objid, 1jcdcount, 
 
    (select Runtime fromywtj_ Rpt_dyhgl_month TB Whereto_char (add_months to_date (tb.rptdate, ' yyyymm '), ' yyyymm ') = R.rptdateand Tb.objid = R.objid) Tongbiruntime, 
 
    (select Chaoshangxian + Chaoxiaxian fromywtj_rpt_dyhgl_month TB Whereto_char (Add_months ( To_date (tb.rptdate, ' yyyymm '), ' ' yyyymm ') = R.rptdateand Tb.objid = R.objid) TONGBIBHG, 
 
    (select Jcdlb from JCZL_DWJG d Whered.objid = R.objid) jcdlb, 
 
    (select Jcdlbname from Dic_jcdlb wherejcdlbid = (select Jcdlb from Jczl_dw JG where ObjID = R.objid)) Jcdlbname from 
 
    ywtj_rpt_dyhgl_month R where Rptdate =:rptyear;  --② 

Take out alone to execute, fast, but the amount of data returned is very large. If you recycle the analysis, estimate the performance is not good, give up this attempt.

The query ② in the Tongbiruntime and TONGBIBHG two word of the query to remove, faster execution, the same ① tongbiruntime and TONGBIBHG Two word of the query removed, the implementation speed of a lot of time, about 0.157 seconds.

This speed is increased a lot, but less than last year's data, that is divided into two queries! Follow this line of thought and the resulting process is as follows (only the main part is retained):

Createor Replace procedure rpt_dyhgl_year (--voltage qualified rate--year v_rptyear in Varchar2,--year yyyy--rpttype in Varchar2, --Report type, 1 year, 2 quarterly ref_cursor out Sys_refcursor--return cursor) as--variable declaration ... begin delete from YWTJ_RPT_DYHG 
  L_year where rptyear = v_rptyear and Rpttype = ' 1 '; 
 Commit --Query raw data v_sql: = ' Select Rptdate, jcdlb, Jcdlbname, sum (NVL (runtime,0)) Sumruntime,sum (NVL (buhegetime,0)) Buhegetime, C  
    Ount (Jcdcount) Jcdcount from (select Rptdate, Runtime,r.chaoshangxian+r.chaoxiaxian buhegetime, R.objid, 1 Jcdcount, (select Jcdlb from jczl_dwjg d whered.objid = R.objid) jcdlb, (select Jcdlbname from dic_jcdlb wherejcdlbid = (select Jcdlb from jczl_dwjg where ObjID = R.objid)) Jcdlbname from Ywtj_rpt_dyhgl_month r where Rptdate =: rptyear) Group by Rptdate, JCDLB, jcdlbname order by RP 
  Tdate, Jcdlb '; --Insert new data V_sqlrptinsert: = ' Insert Intoywtj_rpt_dyhgl_year (Rptyear,rptdate,rpttype) VALUES (: Rptyear,:rptdate,: 
  Rpttype) '; v_sQlrptupdate: = '; --The month data for 1--12 month for Rptmonth in 1..12 Loop--open cursor start compute pass rate open v_cur for v_sql using v_rptyear| | 
   Lpad (Rptmonth, 2, ' 0 '); 
     loop fetch v_cur Intov_rptdate,v_jcdlb,v_jcdlbname,v_sumruntime,v_sumbuhegetime,v_jcdcount; 
     Exit when V_cur%notfound; 
     --Calculate the qualified rate if V_sumruntime <> 0 then V_hgl: = (v_sumruntime-v_sumbuhegetime)/v_sumruntime *100; 
     End If; 
    --Update month data ... end loop; 
   Close v_cur; --Open the cursor to start computing the annual pass rate open V_cur for v_sql using (v_rptyear-1) | | 
   Lpad (Rptmonth, 2, ' 0 '); 
     Loop fetch v_cur into V_rptdate,v_jcdlb,v_jcdlbname,v_sumruntime,v_sumbuhegetime,v_jcdcount; 
     Exit when V_cur%notfound; 
     --Calculate the qualified rate if V_sumruntime <> 0 then V_hgl: = (v_sumruntime-v_sumbuhegetime)/v_sumruntime*100; 
     End If; 
    --Update year-on-year data ... end loop; 
  Close v_cur; 
 
  End Loop; --Update the cumulative data ...--Calculate the comprehensive pass rate ... open ref_cursor foR SELECT * Fromywtj_rpt_dyhgl_year r where R.rptyear = v_rptyear and R.rpttype = 1 order byr.rptdate; End Rpt_dyhgl_year;

It is validated that the execution speed is approximately one second, which means that the performance increases from more than 900 seconds to 1 seconds, and increases by almost three orders of magnitude.