Simple Analysis of Oracle Having clauses

Today, we will introduce the Oracle Having clause. Many people may not understand the Where clause. It does not matter. After reading this article, you will certainly learn the SQL clause. I hope this article will teach you more. Replace the Oracle Having clause with the Where clause:

Avoid using the Oracle Having clause. Having filters the result set only after any records are retrieved. This process requires sorting, total, and other operations. If the WHERE clause can be used to limit the number of records, this overhead can be reduced. (In non-Oracle) where on, where, and Having conditions can be added, on is the first statement to be executed, where is the second clause, and Having is the last clause, because on filters out records that do not meet the conditions before making statistics, it can reduce the data to be processed by intermediate operations. It is reasonable to say that the speed is the fastest, where should also be faster than Having, because it performs sum only after filtering data, and on is used only when two tables are joined, so in a table, then we can compare the where clause with the Oracle Having clause.

In the case of single-Table query statistics, if the filter condition does not involve fields to be calculated, the results will be the same, but where can use the rushmore technology, having cannot. In terms of speed, the latter must be slow. If a calculated field is involved, the value of this field is uncertain before calculation, according to the workflow written in the previous article, the where function time is completed before computing, and Having works only after computing. In this case, the results are different. In multi-table join queries, on takes effect earlier than where.

The system first combines multiple tables into a temporary table based on the join conditions between tables, then filters them by where, then computes them, and then filters them by Having after calculation. It can be seen that to filter a condition to play a correct role, you must first understand when the condition should take effect and then decide to put it there.

Reduce queries to a table:

In SQL statements containing subqueries, pay special attention to reducing the number of queries to the table. Example:

SELECT TAB_NAME from tables where (TAB_NAME, DB_VER) = (SELECTTAB_NAME, DB_VER FROM TAB_COLUMNS where version = 604)

Improve SQL efficiency through internal functions:

Complex SQL statements often sacrifice execution efficiency. Being able to take control of the above methods to solve problems using functions makes sense in actual work.

Use the table Alias (Alias ):

When connecting multiple tables in an SQL statement, use the table alias and prefix the alias on each Column. In this way, the parsing time can be reduced and the syntax errors caused by Column ambiguity can be reduced.

Replace IN with EXISTS and not exists instead of not in:

In many basic table-based queries, to meet one condition, you often need to join another table. In this case, using EXISTS (or not exists) usually improves the query efficiency. IN a subquery, the not in Clause executes an internal sorting and merging. IN either case, not in is the most inefficient (because it executes a full table traversal for the table IN the subquery ). To avoid the use of not in, we can rewrite it into an Outer join (Outer Joins) or not exists. Example:

 
 

  
  
SELECT * from emp (basic table) WHERE EMPNO>0 AND EXISTS
(SELECT 'x' FROM DEPT WHEREDEPT. DEPTNO=EMP. DEPTNO ANDLOC= 'Melb ')
(Inefficient) SELECT * from emp (basic table) WHERE EMPNO>0 AND DEPTNO IN
(SELECT DEPTNO FROM DEPT WHERELOC= 'Melb ')
 
 

1. Oracle LogMiner
2. Unveil the Oracle LogMiner mystery
3. Brief description of Oracle Clob type
4. Overview Oracle 10g Server
5. Introduction to Oracle xmltype