Basic query optimization and subquery Optimization in Oracle databases

Basic query optimization and subquery Optimization in Oracle databases

1. Reasonably sort query Conditions

Oracle uses the bottom-up sequence to parse WHERE data. From the perspective of performance optimization, we recommend that you write the conditions that can filter out a large number of record rows at the end of the WHERE clause and

The join conditions between them are placed before other WHERE clauses, that is, the conditions that are easy to troubleshoot are judged and processed first. In this way, the equivalent join is performed after filtering out as many records as possible, which 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 = 20;

The query efficiency is higher than that of the following statements:

SELETE empno, ename, job, sal, dept.deptno, dnameFROM emp, deptWHERE emp.deptno = 20 AND emp.deptno = dept.deptno;

2. Use the table alias in the connection

When performing a connection query, we recommend that you use the table alias in the SQL statement and prefix the alias with each field. This reduces the resolution time and avoids ambiguity of Field Names (multiple

Syntax error. For example, the following statement:

SELECT e.empno, e.ename, e.job, e.sal, e.deptno, d.nameFROM emp e, dept dWHERE e.deptno = d.deptno AND e.deptno = 20;

3. Replace DISTINCT with EXISTS

When performing a one-to-multiple table join query, if you want to remove duplicate rows in the results, you can consider replacing DISTINCT with EXISTS (combined with subquery.

For example:

SELECT deptno, dname FROM dept d WHERE EXIST (SELECT 'y' FROM emp e WHERE e.deptno = d.deptno);

4. Replace HAVING with WHERE

Since the execution sequence of SELECT statements is: first the WHERE clause, in the group by clause, then the SELECT query, then the HAVINT clause, and finally the order by clause, grouping

If the filter condition does not involve group computing, replace the WHERE statement with the filtering condition specified by HAVING.

For example:

SELECT deptno, avg(sal) FROM empWHERE deptno IN (10, 20)GROUP BY deptno;

The efficiency is higher than the following

SELECT deptno, avg(sal) FROM empWHERE deptno IN (10, 20)GROUP BY deptno;

Of course, if grouping query filtering conditions are designed for grouping calculation, they can only be specified in the HAVING clause.

5. subquery Optimization

1) when the data volume is large, the performance of subqueries will be greatly affected;

2) The to_char (add_months (to_date (tb. rptdate, 'yyyymm'), 12), 'yyyymm'). When querying large data volumes, executing a function also has a big impact on the system.

Here is an example:

Createor replace procedure Rpt_Dyhgl_year (rptyear in varchar2, -- yyyy-rpttype in varchar2, -- report type, 1 Annual Report, 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, count (jcdcount) jcdcount, sum (nvl (tongbiRuntime, 0) tongbiRuntime, sum (nvl (tongbiBhg, 0) tongbiBhg from (select rptdate, 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, ''yyymmd''), 12), ''yyymmd'') = r. rptdateand tb. objid = r. objid) tongbiRuntime, (select chaoshangxian + chaoxiaxian fromywtj_rpt_dyhgl_month tb whereto_char (add_months (to_date (tb. rptdate, ''yyymmd''), 12), ''yyymmd'') = 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 where objid = r. objid) jcdlbName from partition r where rptdate =: rptYear) group by rptdate, jcdlb, jcdlbName order by rptdate, jcdlb; -- Insert new data v_sqlRptInsert: = 'insert partition (RPTYEAR, RPTDATE, RPTTYPE) values (: RPTYEAR,: RPTDATE,: RPTTYPE) '; v_sqlRptUpdate: = ''; -- calculate the data of the month from 1 to 12 months Rptmonth in 1 .. 12 loop execute immediate limit rptyear, rptmonth, '1'; commit; open v_cur for v_ SQL using rptyear | LPAD (rptmonth, 2, '0'); loop fetch v_cur limit v_rptdate, v_jcdlb, v_jcdName, v_sumRuntime, v_sumbuhegeTime, v_jcdCount, v_tongbiRuntime, v_tongbiBhg; exit when v_cur % notfound; -- calculate the pass rate ...... -- Update the data of the current month ...... End loop; close v_cur; end loop; -- Update accumulated data ...... Commit; -- calculate the overall pass 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 runs normally and the query time is about 30-40 seconds.

After you deploy the Report to the running environment, you will find that the report does not come out within 2 or 3 minutes when verifying the function! After several attempts, I finally got the result: More than 940 seconds, more than 15 minutes! In this way, the user must be unacceptable! Use pl/SQL dev performance analysis to analyze the execution process of Rpt_Dyhgl_year.

fetch v_cur into v_rptDate,v_jcdlb,v_jcdName,v_sumRuntime,v_sumbuhegeTime,v_jcdCount,v_tongbiRuntime,v_tongbiBhg;

(During this period, the process is converted into program execution. During performance analysis, it is found that the main time is occupied by dr. Read (), which is consistent with the analysis of the process .)

In the query corresponding to fetch

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 rptdate, 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''),12),''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''),12),''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 where objid = r.objid)) jcdlbName      from ywtj_rpt_dyhgl_month r where rptdate = :rptYear)    group by rptdate, jcdlb,  jcdlbName     order by rptdate ,jcdlb ';      --① 

Take it out and execute it in about 5.7 seconds.
Then, the subquery:

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''),12),''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''),12),''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 where objid = r.objid)) jcdlbName      from ywtj_rpt_dyhgl_month r where rptdate =:rptYear;  --② 

It is fast to execute it separately, but the returned data volume is large. If we analyze it cyclically and estimate the performance, we should give up this attempt first.

The two fields tongbiRuntime and tongbiBhg in query ② are removed to achieve faster execution. Similarly, the two fields tongbiRuntime and tongbiBhg in query ① are removed from the subquery, which greatly improves the execution speed, the time is about 0.157 seconds.

In this way, the speed is improved a lot, but with less data from the year-on-year comparison last year, it is divided into two queries! Based on this idea, the final process is as follows (only the main part is retained ):

Createor replace procedure Rpt_Dyhgl_year (-- Voltage pass rate-Annual v_rptyear in varchar2, -- yyyy-rpttype in varchar2, -- report type, 1 Annual Report, 2 quarterly ref_cursor out sys_refcursor -- Return cursor) as -- variable declaration ....... begin delete from YWTJ_RPT_DYHGL_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, count (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 partition r where rptdate =: rptYear) group by rptdate, jcdlb, jcdlbName order by rptdate, jcdlb; -- Insert new data v_sqlRptInsert: = 'insert partition (RPTYEAR, RPTDATE, RPTTYPE) values (: RPTYEAR,: RPTDATE,: RPTTYPE) '; v_sqlRptUpdate: = ''; -- calculate the monthly data for rptmonth in 1-12 months .. 12 loop -- open the cursor and start to calculate the pass rate. open v_cur for v_ SQL using v_rptyear | LPAD (rptmonth, 2, '0'); loop fetch v_cur 1_v_rptdate, v_jcdlb, v_jcdLbName, v_jcdCount; exit when v_cur % notfound; -- calculate the pass rate if v_sumRuntime <> 0 then v_hgl: = (v_sumRuntime-v_sumbuhegeTime)/v_sumRuntime * 100; end if; -- Update the data of the current month ........ end loop; close v_cur; -- open the cursor to start calculating the yoy 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, hour, v_jcdCount; exit when v_cur % notfound; -- pass rate if v_sumRuntime <> 0 then v_hgl: = (v_sumRuntime-v_sumbuhegeTime) /v_sumRuntime * 100; end if; -- Update the year-on-year data ......... end loop; close v_cur; end loop; -- Update the accumulated data ......... -- calculate the overall 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 has been verified that the execution speed is more than one second, that is to say, the performance has increased from more than 900 seconds to 1 second, which is about three orders of magnitude.

Articles you may be interested in:

• Comprehensive query optimization suggestions for some Oracle databases
• Explain the usage examples of oracle cross-database query dblink
• Order by sorting and query IN Oracle Database are output IN the ORDER of IN conditions
• Script sharing for viewing slow query progress in Oracle
• Introduction to Distributed Query of replicated table data in Oracle cross-Database Query
• SQL syntax summary for querying by page in Oracle
• ORACLE Study Notes-Query
• Sort out the key points of Data Query Optimization in Oracle databases