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'