Oracle query optimization and oracle Optimization

Oracle query optimization and oracle Optimization

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, dname

FROM emp, dept

WHERE 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. name

FROM emp e, dept d

WHERE 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 emp

WHERE deptno IN (10, 20)

Group by deptno;

The efficiency is higher than the following

SELECT deptno, avg (sal) FROM emp

Group by deptno

HAVING deptno IN (10, 20 );

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

5. Use System Functions

After all, system functions are a "professional" solution provided by database vendors, which is relatively more reliable.

Oracle Query Optimization

Using exists () does not traverse the entire table
Select case when exists (select * from user where name = 'xxx') then 1 else 0 end as aa from dual

Oracle Query Optimization

Dizzy, you know that 20130131 was checked in February. You just need to directly = '000000'. If you use more substr, the query speed will be slow.
The vertical display is just union all. As the upstairs said, the horizontal display is like this.
Select sum (case when term_mon <3 then 1 else 0 end) is less than 3, sum (case when term_mon <3 then bal else 0 end) is less than 3, sum (case when term_mon> 3 and term_mon <6 then 1 else 0 end) greater than 3 less than 6 quantity, sum (case when term_mon> 3 and term_mon <6 then bal else 0 end) values greater than 3 and less than 6, sum (case when term_mon> 6 and term_mon <12 then 1 else 0 end) are greater than 6 and less than 12, sum (case when term_mon> 6 and term_mon <12 then bal else 0 end) is greater than 6 and smaller than 12, sum (case when term_mon> 12 then 1 else 0 end) is greater than 12, sum (case when term_mon> 12 then bal else 0 end) is greater than 12 values from rpt_xd where substr (beg_date, 201301) = '123'