Feng zuhong to Oracle SQL optimization Summary

There were no things left idle in the past two days, and the project was soon launched. I read all the SQL statements I wrote in the project.

With reference to the experience of optimizing SQL statements in previous years and the summary of SQL statement optimization methods on the Internet, and the powerful SQL analysis tool such as Oracle, We optimized the SQL statements that were not well written.

 

Summary:

I. Fixed SQL writing habits. Keep the same query as much as possible

2. High storage process efficiency.
3. Write statements in the same format, including uppercase and lowercase letters, punctuation marks, and line breaks.

SQL statements are written in uppercase. Because Oracle always parses SQL statements first, converts lowercase letters to uppercase and then executes them.
4. When writing a query statement, try not to use '*' to query the required columns.

1. Use the in operator as few as possible. Basically, all in operators can be replaced by exists, and not in can be replaced by not exists.

Special note: In is suitable for the case where the external table is large but the internal table is small; exists is suitable for the case where the external table is small but the internal table is large.

It does not mean that all in or not in can be replaced by exists and not exists.

Replace or with in
This is a simple and easy-to-remember rule, but the actual execution results must be tested. in Oracle8i, the execution paths of the two seem to be the same.
Inefficiency:
Select .... From location where loc_id = 10 or loc_id = 20 or loc_id = 30
Efficient
Select... From location where loc_in in (10, 20, 30 );

Replace or with Union (applicable to index columns)

In general, replacing or in the WHERE clause with union will produce better results. Using or for the index column will cause a full table scan.

Note that the preceding rules are only valid for multiple index columns. If a column is not indexed, the query efficiency may be reduced because you have not selected or.

 

In the following example, both loc_id and region have indexes.
Efficient:
Select loc_id, loc_desc, region from location where loc_id = 10
Union
Select loc_id, loc_desc, region from location where region = "Melbourne"
Inefficiency:
Select loc_id, loc_desc, region from location where loc_id = 10 or region = "Melbourne"

If you insist on using or, you need to write the index columns with the least records at the beginning.

2. No "<>" or "! = "Operator. Processing Non-equals operators will cause full table scanning, which can be replaced by "<" or ">.
For example:
A <> 0 to a> 0 or a <0
A <> ''To A>''

3. If the WHERE clause is null or is not null, Oracle will stop using the index and perform a full table scan.

When designing a table, you can set the index column to not null. In this way, other operations can be used to replace null operations.

Use other operations with the same function, such:
1. Change A is not null to a> 0 or a>.
2. fields that are not allowed to be empty are replaced by a default value. For example, status fields in the application for expansion cannot be empty. The default value is application.
3. Create a bitmap index (a partitioned table cannot be created, and the bitmap index is difficult to control. If there are too many fields, the index will degrade the performance, and the data block lock will be added during multi-person update operations)
If a column has a null value, the performance will not be improved even if the column is indexed. Any statement optimizer that uses is null or is not null in the WHERE clause cannot use indexes.

4. If the wildcard "%" or "_" is the first character of the query string, the index will not be used.
Example: Select U. name from user u where u. name like '% Huang %'
Should be changed to: Select U. name from user u where u. name like 'huang %'

5. For the connected column "|", the index of the last connected column is invalid. Avoid connections whenever possible. Separate connections or use functions that do not work on columns.

6. If the index is not function-based, the index will no longer function when you use the function for the index column in The WHERE clause.
For example, creadate has an index.
Query: select a. paicumnum from ps_number A where to_char (A. creadate, 'yyyy-mm-dd')> '2017-10-12 'No index is used
Select a. paicumnum from ps_number A where a. creadate> to_date ('2017-10-12 ', 'yyyy-mm-dd') use index

7. Avoid using computation on the index column in The WHERE clause. Otherwise, the entire table is scanned because the index fails.
Example: Select S. numer from student s where S. Age-20 = 50;
Should be changed to: Select S. numer from student s where S. Age = 30 or (50-20 );

8. If you compare columns of different data types, the index will become invalid.
For example, if age is of the number type and score is of the varcher type
Select S. * from student s where S. age> the score index on age and score will be invalid.

Avoid changing the index column type .:
Oracle automatically converts columns to different types of data.
Assume that empno is a numeric index column.
Select... From EMP where empno = '20140901'
In fact, after Oracle type conversion, the statement is converted:
Select... From EMP where empno = to_number ('123 ')
Fortunately, the type conversion does not occur on the index column, and the purpose of the index is not changed.

Assume that emp_type is a character-type index column.
Select... From EMP where emp_type = 123
This statement is converted:
Select... From EMP where to_number (emp_type) = 123
This index will not be used because of internal type conversion! To avoid implicit type conversion for your SQL statements, it is best to explicitly display the type conversion.

Note: When comparing characters and values, Oracle converts the value type to the character type first.

9. Replace union with the Union all operator, because the Union all operation simply merges the two results and returns them. Union: extracts the results of two tables at run time, sorts and deletes duplicate records using the sorting space, and finally returns the result set. If the table has a large amount of data, it may lead to disk sorting.

10. Always use the first column of the index:
If an index is created on multiple columns, the optimizer selects this index only when its first column (Leading column) is referenced by the WHERE clause. this is also a simple and important rule. When only the second column of the index is referenced, the optimizer uses the full table scan and ignores the index.

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

Example:
Select emp_no from EMP where (group, name) = (select
Column1, column2 from test where test_id = 604)

The above optimization suggestions for SQL are summarized through my personal experience and bit by bit. If any, pleaseAdvice.

　　　　　　　　　　　　　　　　　By the way, I collected some good things and shared them out.
　　　　
1. The most efficient method for deleting duplicate records (because rowid is used)

Example:
Delete from EMP e where E. rowid> (select Min (X. rowid)
From emp x where X. emp_no = E. emp_no );

2. Good parameter usage

<! -- Query the serial number of an existing approval entity production Architecture -->
<Select id = "searchappentitydeptid" parameterclass = "Java. util. Map"
Resultclass = "appentitydeptdto">
<! [CDATA [
Select T. entity_id apptempletid
From oms_entity_dept t
]>
<Isnotempty prepend = "where" property = "userlist">
<Iterate property = "userlist" conjunction = "or">
(T. dept_no = # userlist []. deptno # and T. entity_no = # userlist []. entityno #)
</Iterate>
</Isnotempty>
</SELECT>

3. ibatis calls proc
<! -- Call the stored procedure and write the personnel production information table -->
<Parametermap id = "swapparameters" class = "map">
<Parameter property = "paicuniquedeptid" jdbctype = "varchar" javatype = "Java. Lang. String" mode = "in"/>
<Parameter property = "paicumnum" jdbctype = "varchar" javatype = "Java. Lang. String" mode = "in"/>
</Parametermap>
<Procedure id = "swapemailaddresses" parametermap = "swapparameters">
{Call oms_sync_ps_pkg.proc_oms_prd_emp_io (?, ?)}
</Procedure>

Additional index concepts (reprinted)

I. Index concept
Index is the method to accelerate the retrieval of table data. A database index is similar to a book index. In books, indexes allow users to quickly find the desired information without having to read the complete book. In the database, the index also allows the database program to quickly find the data in the table without scanning the entire database.

Ii. Index features
1. indexing can speed up Database Retrieval
2. Indexes reduce the speed of maintenance tasks such as database insertion, modification, and deletion.
3. indexes are created on tables and cannot be created on views.
4. indexes can be created directly or indirectly.
5. You can use indexes in optimization hiding.
6. Use the query processor to execute SQL statements. Only one index can be used at a time on a table.

Iii. Advantages of Indexes
1. Create a unique index to ensure the uniqueness of each row of data in the database table
2. The data retrieval speed is greatly accelerated, which is also the main reason for index creation.
3. Accelerate the connection between tables, especially for Data Reference integrity.
4. When you use grouping and sorting clauses to retrieve data, you can also significantly reduce the time for grouping and sorting in queries.
5. By using indexes, you can use the optimizer during the query process to improve system performance.

Iv. Index disadvantages
1. It takes time to create and maintain indexes. This time increases with the increase of data volume.
2. Indexes occupy physical space. In addition to data tables, each index occupies a certain amount of physical space. To create a clustered index, the required space will be larger.
3. When adding, deleting, and modifying table data, the index must be maintained dynamically, reducing the Data Maintenance speed.

V. Index classification
1. directly create indexes and indirectly create indexes
Directly create an index: Create index mycolumn_index on mytable (myclumn)
Indirect index creation: defines the primary key constraint or unique key constraint. indexes can be created indirectly.
2. Common and unique Indexes
Normal index: Create index mycolumn_index on mytable (myclumn)
Unique index: ensure that all data in the index column is unique and can be used for clustered indexes and non-clustered indexes.
Create unique coustered index myclumn_cindex on mytable (mycolumn)
3. single index and Composite Index
Single index: Non-composite index
Composite Index: Also called composite index. The index creation statement contains multiple field names, up to 16 fields.
Create index name_index on username (firstname, lastname)
4. Clustered index and non-clustered index (clustered index and clustered index)
Clustered index: physical index. The physical order is the same as that of the base table. The data values are always sorted in order.
Create clustered index mycolumn_cindex on mytable (mycolumn)
Allow_dup_row (clustering indexes with repeated records are allowed)
Non-clustered index: Create unclustered index mycolumn_cindex on mytable (mycolumn)

Vi. Use of Indexes
You should create an index on these columns, for example:
1. You can speed up the search on columns that frequently need to be searched;
2. In a column that acts as a primary key, the uniqueness of the column and the data arrangement structure in the organization table are enforced;
3. These columns are frequently used in connection columns. These columns are mainly foreign keys, which can speed up the connection;
4. Create an index on a column that often needs to be searched by range. The specified range is continuous because the index has been sorted;
5. Create an index on a column that often requires sorting. Because the index has been sorted, you can use the index sorting to speed up the sorting query time;
6. Create an index on the columns in the WHERE clause frequently to speed up condition judgment.

These columns that should not be indexed have the following features:
First, indexes should not be created for columns that are rarely used or referenced in queries. This is because, since these columns are rarely used, there is an index or no index, and the query speed cannot be improved. On the contrary, the addition of indexes reduces the system maintenance speed and space requirements.

Second, indexes should not be added to columns with only few data values. This is because these columns have very few values, such as gender columns in the personnel table. In the query results, the data rows in the result set account for a large proportion of the data rows in the table, that is, the proportion of data rows to be searched in the table is large. Adding indexes does not significantly accelerate the search speed.

Third, indexes should not be added for columns defined as text, image, and BIT data types. This is because the data volume of these columns is either large or small.

Fourth, when the modification performance is far greater than the retrieval performance, you should not create an index. This is because the modification performance and retrieval performance are inconsistent. When an index is added, the search performance is improved, but the modification performance is reduced. When the index is reduced, the modification performance is improved and the retrieval performance is reduced. Therefore, when the modification performance is much higher than the retrieval performance, you should not create an index.