Database SQL statement Performance optimization

Select the most efficient table name order

The Oracle parser processes the table names in the FROM clause in a right-to-left order, and the FROM clause is written in the final table (the underlying table, driving tables) will be processed first, and in the case where the FROM clause contains more than one table, you must select the table with the fewest number of record bars as the underlying table. If you have more than 3 tables connected to the query, you need to select the crosstab (intersection table) as the underlying table, which refers to the table that is referenced by the other table.

Connection order in the WHERE clause

Oracle uses a bottom-up sequential parsing where clause, according to which the connection between tables must be written before other where conditions, and those that can filter out the maximum number of records must be written at the end of the WHERE clause.

Avoid using ' * ' in the SELECT clause

When you want to list all columns in the SELECT clause, it is a convenient way to use dynamic SQL column references, which unfortunately is a very inefficient approach. In fact, in the process of parsing, Oracle translates ' to all column names in turn. This work is done by querying the data dictionary, which means more time is spent.

Character fields must be enclosed in single quotation marks

The character field must be enclosed in single quotes to avoid confusion after the where query condition does implicit conversion, select Ename,sal,deptno from emp where empno= ' 7782 ';

Reduce the number of accesses to the database

Oracle has done a lot of work internally: Parsing SQL statements, estimating index utilization, binding variables, reading chunks, and so on;

ArraySize parameter settings

The ArraySize parameter is reset in Sqlplus, Sqlforms, and pro*c to increase the amount of data retrieved per database access, with a recommended value of 200

Use the Decode function to avoid duplicate scans of the same record or duplicate connections to the same table

Select COUNT (*), sum (SAL) from empwhere deptno = ' A ' and ename like ' smith% '; Select COUNT (*), sum (SAL) from empwhere deptno = ' + ' and ename like ' smith% ';

You can use the Decode function to get the same results efficiently.

Select COUNT (Decode (deptno, ' + ', ' x ', null)) D20_count, Count (Decode (deptno, ' + ', ' x ', null)) D30_count, sum (Decode (Deptno, ' A ', Sal, null)) D20_sal, sum (decode (deptno, +, Sal, null)) D30_sal from EMP where ename like ' SM ith% ';

' X ' represents any one field. Similarly, the Decode function can also be used in the group BY and ORDER BY clauses.

Integrated simple, non-associative database access

If you have a few simple database query statements, you can integrate them into a single query (even if there is no relationship between them), the same statement is written, the same functionality has different effects on the same performance of SQL, such as:

Developer a writes: SELECT * FROM emp Developer B writes: SELECT * from scott.emp (owner prefix with table) Developer C writes: SELECT * from Scott. EMP (capital table name) Developer D wrote: SELECT * from SCOTT. EMP (more space in the middle)

The results of the four SQL analysis and the execution time are the same, but from the principle of Oracle shared memory, it can be seen that each SQL is parsed by Oracle, and the separate consumption of shared memory, if the SQL in the same format, Oracle only resolves once, and shared memory retains only one parse result, which not only reduces the time to parse SQL, but also reduces the duplication of information in shared memory.

Delete duplicate records

Delete from emp e where E.rowid > (select min (x.rowid) from emp x where x.emp_no = e.em_no);d elete from Tb_cablecheck_g Rid g where G.rowid > (select min (g1.rowid) from Tb_cablecheck_grid G1 where g.grid_no=g1.grid_no);

Replace Delete with truncate

When you delete a record in a table, in general, the rollback segment (rollback segments) is used to hold information that can be recovered. If you do not have a COMMIT transaction, Oracle restores the data to the state it was before it was deleted (exactly before the delete command was executed) and when the truncate is applied, the rollback segment no longer holds any recoverable information. When the command runs, The data cannot be restored. So very few resources are invoked and execution times are short. (Translator Press: truncate only in Delete full table applies, truncate is DDL is not DML)

Use commit as much as possible

Whenever possible, commit is used as much in the program as possible, so that the performance of the program is improved and the requirements are reduced by the resources freed by the commit. The resources freed by commit: a. The information that is used to recover data on the rollback segment. B. Locks obtained by program statements c. Space in redo log buffer d. Oracle manages internal spending on 3 of these resources

Replace a HAVING clause with a WHERE clause

Avoid having a HAVING clause that filters the result set only after all records have been retrieved. This processing requires sorting, totals, and so on. If you can limit the number of records through the WHERE clause, you can reduce this overhead. (Not in Oracle). On, where, have these three can be added conditional clauses, on is the first to execute, where second, having the last, because on is the non-conforming record filter before the statistics, it can reduce the intermediate operation to process the data, it should be said that the speed is the fastest, Where should also be faster than having to, because it filters the data before the sum, in two table joins only use on, so in a table, the left where and have compared. In the case of this single-table query statistic, if the conditions to be filtered are not related to the fields to be computed, then the results are the same, except where the Rushmore technique can be used, and the having is not, the latter is slower in speed.

Reduce the query on the table

In SQL statements that contain subqueries, pay particular attention to reducing queries on tables. Example: Select Tab_name from tables where (tab_name,db_ver) = (select Tab_name,db_ver from Tab_ Columns where version=604)

Improve SQL efficiency with intrinsic functions

Complex SQL often sacrifices execution efficiency. The ability to master the above application function to solve the problem is very meaningful in practical work.

Use Alias for table

When you concatenate multiple tables in an SQL statement, use the alias of the table and prefix the alias to each column. This reduces the time to parse and reduces the syntax errors caused by column ambiguity.

Replacing exists with table joins

In general, table joins are more efficient than exists. (inefficient) Select ename from emp e where exists (select ' x ' from dept where dept_no = e.dept_no and Dept_cat = ' a ');(efficient) Select EN Ame from Dept D, emp e where e.dept_no = d.dept_no and Dept_cat = ' a '; Replace in with exists, replace not with not exists

In many base-table-based queries, it is often necessary to join another table in order to satisfy one condition. In this case, using EXISTS (or not EXISTS) usually increases the efficiency of the query. In a subquery, the NOT IN clause performs an internal sort and merge, in order to avoid using not In, we can change it to an outer join (Outer Joins) or not EXISTS. There is also a saying: in suitable for the appearance of large, small inner table query, EXISTS suitable for small appearance, inner table large query, the experiment is indeed so. (efficient) SELECT * from emp where empno > 0 and exist (SELECT ' X ' from dept where dept.deptno = emp.deptno and loc = ' Melb '); (inefficient) SELECT * from emp where empno > 0 and Deptno in (select Deptno from dept where loc = ' Melb ');

Replace distinct with exists

Avoid using DISTINCT in the SELECT clause when submitting a query that contains one-to-many table information, such as a departmental table and an employee table. You can generally consider replacing with exist, EXISTS makes the query faster because the RDBMS core module will be satisfied once the conditions of the subquery are met. Return the result immediately. Example: (inefficient) SELECT DISTINCT dept_no,dept_name from Dept D, emp e where d.dept_no = E.dept_no; (efficient) Select Dept_no,dept_name from Dept d where exists (select ' x ' from emp e where e.dept_no = D.dept_no); EXISTS makes queries faster because the RDBMS core module returns results immediately after the conditions of the subquery are met.

SQL statements are capitalized

Because Oracle always parses SQL statements first, the lowercase letters are converted to uppercase and then executed.

Avoid using not on indexed columns

We want to avoid using not on indexed columns, and not to have the same effect as using functions on indexed columns. When Oracle "encounters" not, he stops using the index instead of performing a full-table scan.

Avoid using calculations on indexed columns

Where clause, if the index column is part of a function. The optimizer will use a full table scan without using an index. Example: Inefficiency: SELECT ... From DEPT WHERE SAL * > 25000; Efficient: SELECT ... From DEPT WHERE SAL > 25000/12;

Replace > > with >=

Efficient: Select from emp where DEPTNO >=4 inefficient: Select from emp where DEPTNO >3

The difference between the two is that the former DBMS will jump directly to the first record that dept equals 4 and the latter will first navigate to the Deptno=3 record and scan forward to the first record with a dept greater than 3.

Replace or with union (for indexed columns)

In general, replacing or in a WHERE clause with Union will have a good effect. Using or on an indexed column causes a full table scan. Note that the above rules are valid only for multiple indexed columns. If a column is not indexed, the query efficiency may be reduced because you did not select or. In the following example, indexes are built on both loc_id and region.  Efficient: Select loc_id, Loc_desc, region from location WHERE loc_id = Ten UNION SELECT loc_id, Loc_desc, region from location where region = "MELBOURNE" inefficient: Select loc_id, Loc_desc, region from location WHERE loc_id = ten OR region = "MELBOURNE" as If you insist on using or, you need to return the least logged index column to the front.

Replace or with in.

This is a simple and easy-to-remember rule, but the actual execution effect has to be tested, and under Oracle8i, the execution path seems to be the same. Inefficient: SELECT .... From location WHERE loc_id = ten or loc_id = or loc_id = 30 Efficient: SELECT ... From location WHERE loc_in in (10,20,30);

Avoid using is null and is not NULL on indexed columns

To avoid using any nullable columns in the index, Oracle will not be able to use the index. For single-column indexes, this record will not exist in the index if the column contains null values.　For composite indexes, if each column is empty, the same record does not exist in the index. If at least one column is not empty, the record exists in the index. For example, if a uniqueness index is established on column A and column B of a table, and the table has a value of a, a and a record of (123,null), Oracle will not accept the next record (insert) with the same A, B value (123,null). However, if all the index columns are empty, Oracle will assume that the entire key value is empty and null is not equal to NULL. So you can insert 1000 records with the same key value, of course they are empty! Because null values do not exist in the index column, a null comparison of indexed columns in the WHERE clause causes Oracle to deactivate the index. Inefficient: (index invalidation) SELECT ... From DEPARTMENT WHERE dept_code are not NULL; Efficient: (index valid) SELECT ... From DEPARTMENT WHERE Dept_code >=0; always use the first column of an index (composite index)

If the index is built on more than one column, the optimizer chooses to use the index only if its first column (leading column) is referenced by a WHERE clause. This is also a simple and important rule that when referencing only the second column of an index, the optimizer uses a full table scan and ignores the index. In the case of composite indexes, it is best to use the Where condition order in the order of the indexes, which is the most efficient, such as creating a composite index:

CREATE INDEX idx1 on table1 (col1,col2,col3) query statement: Efficient: SELECT * FROM table1 where col1=a and col2=b and Col3=d inefficient: SELECT * Fro M table1 where col2=b and col1=a or where col2=b will not use the index

Replace union with Union-all (if possible)

When the SQL statement requires a union of two query result sets, the two result sets are merged in a union-all manner and then sorted before the final result is output. If you use UNION ALL instead of union, this sort is not necessary. Efficiency will therefore be improved. It is important to note that the UNION all will output the same record in the two result set repeatedly. So you still have to analyze the feasibility of using union all from the business requirements. The UNION will sort the result set, which will use the memory of the sort_area_size. The optimization of this memory is also very important. The following SQL can be used to query the ordering of consumption inefficiencies: Select Acct_num, Balance_amt from debit_transactions WHERE tran_date = ' 31-dec-95 ' UNION SELECT AC Ct_num, Balance_amt from debit_transactions WHERE tran_date = ' 31-dec-95 ' efficient: SELECT acct_num, Balance_amt from Debit_trans ACTIONS where tran_date = ' 31-dec-95 ' UNION all SELECT acct_num, Balance_amt from debit_transactions WHERE tran_date = ' 31 -dec-95 '

Where to replace order by

The ORDER BY clause uses the index only under two strict conditions: 1, all columns in an order by must be included in the same index and remain in the order of the index. 2. All columns in ORDER by must be defined as non-null. The index used in the WHERE clause and the index used in the ORDER BY clause cannot be tied.  For example: Table DEPT contains the following: Dept_code PK not NULL DEPT_DESC NOT NULL Dept_type NULL inefficient: (index not used) SELECT Dept_code from DEPT ORDER by Dept_type Efficient: (using index) SELECT dept_code from DEPT WHERE dept_type > 0

Avoid changing the type of indexed columns

Oracle automatically makes simple type conversions to columns when comparing data of different data types.  Suppose Empno is an indexed column of a numeric type. SELECT ...  From EMP WHERE EMPNO = ' 123 ' Actually, after the Oracle type conversion, the statement is converted to: SELECT ... From EMP where EMPNO = To_number (' 123 ') Fortunately, the type conversion did not occur on the index column, and the use of the index was not changed.  Now, suppose Emp_type is an indexed column of a character type. SELECT ...  From EMP WHERE Emp_type = 123 This statement is converted by Oracle to: SELECT ... From EMP where To_number (emp_type) =123 This index will not be used because of the type conversions that occur internally! To avoid the implicit type conversion of your SQL by Oracle, it is best to explicitly express the type conversions. Note When comparing characters to numbers, Oracle takes precedence over numeric types to character types

A WHERE clause that needs to be careful

The WHERE clause in some SELECT statements does not use an index. Here are some examples. In the following example, (1) '! = ' will not use the index. Remember, the index can only tell you what exists in the table, not what does not exist in the table. (2) ' | | ' is a character join function. As with other functions, the index is deactivated. (3) ' + ' is a mathematical function. As with other mathematical functions, the index is deactivated. (4) The same index columns cannot be compared to each other, which will enable full table scanning.

Index considerations

A. If the number of records in a table that has more than 30% data is retrieved. Using indexes will have no significant efficiency gains. B. In certain situations, using an index may be slower than a full table scan, but this is the same order of magnitude difference. In general, the use of indexes than the full table scan to block several times or even thousands of times!

Avoid using resource-intensive operations.

SQL statements with Distinct,union,minus,intersect,order by will start the SQL engine. Performs a resource-intensive sorting (sort) function. Distinct requires a sort operation, while the others need to perform at least two sorting. Typically, SQL statements with union, minus, and intersect can be overridden in other ways. If your database is well-sort_area_size, using union, minus, intersect can also be considered, after all, they are very readable

Optimize GROUP BY

Increase the efficiency of the group BY statement by filtering out unwanted records before group by. The following two queries return the same result but the second one is significantly faster. Inefficient: The Select job, avg (SAL) from the EMP GROUP by job has a job = ' president ' OR job = ' MANAGER ' efficient: Select Job, AVG (SAL) from EMP WHERE job = ' president ' OR job = ' MANAGER ' GROUP by job

Fuzzy query

Replace a like fuzzy query with the InStr () function: InStr (Eqp.equipment_code,#{eqpno}) >0

Database SQL statement Performance optimization