In the subquery, The NOTIN clause executes an internal sorting and merging. In either case, NOTIN is the most inefficient (because it executes a full
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
How to improve query efficiency using Oracle statements
1:... where column in (select * from... where ...);
2:... where exists (select 'x' from... where ...);
The second format is far more efficient than the first one. IN Oracle, almost all IN operator subqueries can be rewritten to subqueries using EXISTS.
With EXISTS, the Oracle system first checks the primary query, and then runs the subquery until it finds the first match, which saves time.
When executing an IN subquery, the Oracle system first executes the subquery and stores the obtained result list IN a temporary table with an index.
Avoid having words
Avoid using the HAVING clause. HAVING filters the result set only after all 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.
The complete execution sequence of the SQL Select statement:
1. The from clause assembles data from different data sources;
2. The where clause filters record rows based on specified conditions;
3. The group by clause divides data into multiple groups;
4. Use Aggregate functions for computation;
5. Use the having clause to filter groups;
6. Calculate all expressions;
7. Use order by to sort the result set.
Example: update tablename set columnName = colunName + 1, columnNameBa group
In the select statement, you can use the group by clause to divide rows into smaller groups. Then, use the group function to return the summary information of each group. In addition, you can use the having clause to limit the returned result set. The group by clause can group query results and return the summary information of rows. Oracle groups query results by the value of the expression specified in the group by clause. In a query statement with a group by clause, the column specified in the select list is either a column specified in the group by clause or contains the select max (sal) aggregate function ), job emp group by job; (Note max (sal), job does not necessarily appear, but makes sense) select and group by of the query statement, having clause is the only place where clustering functions appear. In where clause, clustering functions cannot be used. Select deptno, sum (sal) from emp where sal> 1200 group by deptno having sum (sal)> 8500 order by deptno;
When having clause is used in the gropu by clause, only groups meeting having conditions are returned in the query results. A SQL statement can contain the where clause and having clause. Having is similar to the where clause and is used to set conditions.
The where clause is used to remove rows that do not meet the where condition before grouping query results. That is, data is filtered before grouping. The condition cannot contain clustering functions, use the where condition to display specific rows.
The having clause is used to filter groups that meet the conditions. That is, data is filtered after the group. conditions often contain clustering functions and the having condition is used to display specific groups, you can also use multiple grouping standards for grouping.
Queries the number of employees for each position in each department
Select deptno, job, count (*) from emp group by deptno, job;
Oracle SQL Performance Optimization skills
1. select an appropriate ORACLE optimizer
There are three optimizer types in ORACLE
A. RULE (RULE-based) B. COST (COST-based) c. CHOOSE (selectivity)
Set the default optimizer. You can declare the OPTIMIZER_MODE parameter in the init. ora file, such as RULE, COST, CHOOSE, ALL_ROWS, and FIRST_ROWS. Of course, you also overwrite SQL statements or sessions.
To use the Cost-Based Optimizer (CBO, Cost-Based Optimizer), you must run the analyze command frequently to increase the accuracy of object statistics in the database.
If the optimizer mode of the database is set to CHOOSE, the actual optimizer mode is related to whether the analyze command has been run. If the table has been analyze, the optimizer mode will automatically become CBO. Otherwise, the database will adopt the RULE form optimizer.
By default, ORACLE uses the CHOOSE optimizer. To avoid unnecessary full table scan, you must avoid using the CHOOSE optimizer, instead, we directly use a rule-based or cost-based optimizer.
2. Access Table
ORACLE uses two methods to access table records:
A. Full table Scan
Full table scan accesses each record in the table sequentially. ORACLE optimizes full table scanning by reading multiple data blocks at a time.
B. Access a table through ROWID
You can use ROWID-based access to improve the efficiency of accessing tables. ROWID contains the physical location information recorded in the table. ORACLE uses indexes to connect data with the physical location where data is stored (ROWID. Generally, indexes provide a quick way to access ROWID. Therefore, queries based on index columns can improve the performance.
3. Share SQL statements
To avoid repeated parsing of the same SQL statement, ORACLE stores the SQL statement in memory after the first parsing. The memory in the shared buffer pool of the SGA (system global area) can be shared by all database users. Therefore, when you execute an SQL statement (sometimes called a cursor), if it is exactly the same as the previously executed statement, ORACLE can quickly obtain parsed statements and the best execution path. This function greatly improves SQL Execution performance and saves memory usage.
Unfortunately, ORACLE only provides cache buffering for simple tables. This function is not applicable to multi-table join queries.
The database administrator must set the appropriate parameters for this region in init. ora. When the memory area is larger, more statements can be reserved. Of course, the possibility of sharing is higher.
When you submit an SQL statement to ORACLE, ORACLE will first find the same statement in the memory. It should be noted that ORACLE adopts a strict match between the two. To achieve sharing, the SQL statements must be identical (including spaces and line breaks ).
The database administrator must set the appropriate parameters for this region in init. ora. When the memory area is larger, more statements can be reserved. Of course, the possibility of sharing is higher.
The shared statement must meet three conditions:
A. Character-level comparison: the statements currently executed must be identical to those in the sharing pool.
B. The objects referred to by the two statements must be identical:
C. bind variables must be used in two SQL statements ).
4. Select the most efficient table name sequence (only valid in the rule-based Optimizer)
The ORACLE parser processes the table names in the FROM clause in the order FROM right to left. Therefore, the table written in the FROM clause (basic table driving table) is first processed. When the FROM clause contains multiple tables, You must select the table with the least number of records as the base table. When ORACLE processes multiple tables, it uses sorting and merging to connect them. First, scan the first table (the last table in the FROM clause) and sort the records, and then scan the second table (the last second table in the FROM clause ), finally, all records retrieved from the second table are merged with the appropriate records in the first table.
If more than three tables are connected for query, You need to select an intersection table as the base table, which is the table referenced by other tables.