Optimization of SQL statements in ORACLE notes, oraclesql statements

Optimization of SQL statements in ORACLE notes, oraclesql statements

SQL statement optimization is summarized as follows:

 

To optimize SQL statements, follow these six steps:

1. Rational use of Indexes
2. Avoid or simplify sorting
3. Eliminate scanning of large tables
4. Avoid complex wildcard matching
5. Adjust the performance of subqueries
6. EXISTS and IN Operators

The following is a summary of the above six steps:

• 1 ------------------------------------- reasonably use the index

First, we need to clarify the fundamental purpose of index creation: To improve query efficiency;

The usage principles are as follows:

1.1 create an index for columns with different values frequently used in conditional expressions

1.2. Do not create an index or a bitmap index on a column that is not worth the most.

For example, in the Gender column of the employee table, there are only two different values: "male" and "female". If an index is created, the performance will not be improved.

1.3 create an index on a column that is frequently connected but not specified as a foreign key

According to my experience, in actual development, the database design will not create indexes, and they are all associated by establishing indexes on the foreign key, so the performance is better;

. Create an index on columns that are frequently sorted or grouped

. If there are multiple columns to be sorted, you can create a composite index.

For example, order by D and E. Note: when creating an index, note that the order of column D and column E is the same as that of column E. Otherwise, no such database will use this index;

. If a function is often applied to a column in a condition expression and a query condition is specified, a function index should be created.

Note: For how to create an index, how many indexes are there, and the application scenarios in Oracle, I will add them in the following post.

 

• Notes for using Indexes

 The following SQL statements do not use column indexes. Avoid using them as much as possible:

1. There is an implicit conversion of data types, for example

 

SQL code

1. Select * from emp where empno = '201312 ';

 

Note that the above empno column is of the number type, so this index will not be used for conversion.

 

2. When a column has a mathematical operation, for example

 

SQL code

1. Select * from emp where sal * 2 <1000;

 

This is because there is only sal value in the index, and there is no sal * 2 value. It should be changed

 

SQL code

1. Select * from emp where sal <1000/2;

 

 

3. When not equal to (<>) is used, for example

 

 

SQL code

1. Select * from emp where deptno <> 10;

 

4. When using the substr string function, for example

 

 

SQL code

1. Select * from emp where substr (ename, 1, 3) = 'smi ';

5. When the '%' wildcard is the first character, for example

 

 

SQL code

1. Select * from emp where ename like '% th ';

 

6. When the string is connected (|), for example

 

 

SQL code

1. <Span style = "font-size: 16px;"> Select * from emp where ename | 'abc' = 'ithabc'; </span>

 

 

 

 

• 2 -------------------------------------Avoid or simplify sorting

2.1 duplicate sorting of large tables should be simplified or avoided. oracle sorts large tables by default.

• SQL contains the Group By clause
• SQL contains the Order By clause
• SQL contains the Distinct clause
• SQL contains the Minus or Union clause
• SQL subquery in THE in Clause

2.2 The index cannot be effectively used in the following cases

1. Not all columns to be sorted are indexed, for example

 

SQL code

1. Order by D, E, and only create an index on column D in the table
2. The column Order in the Group by or Order by clause is different from that in the index column.

2. Columns sorted during connection query come from different tables (the index cannot be cross-table)

 

 To avoid unnecessary sorting, you must add indexes correctly and merge database tables reasonably. If sorting is unavoidable, try to simplify it, such as narrowing the column range of sorting.

 

 

• 3 ------------------------------------- eliminate scanning for large tables

3.1. In connection queries, sequential access to tables may have a fatal impact on query efficiency. The primary way to avoid this is to index the connected columns. For example, there are two tables, student table (student ID, name, age ...) And Course Selection form (student ID, course number, score ). If you want to connect two tables, you need to create an index on the connection field "student ID.

 

 

3.2 use Union to avoid sequential access. Although all check columns are indexed, some forms of where clauses force the database to use sequential access. For example

 

SQL code

1. Select * from abc where a> 10 or B <10;

 

Although we have created indexes on column B and column C, in the preceding statement, the optimizer still uses sequential access to scan the entire table. Because this statement is used to retrieve the set of separated rows, you can change it to the following statement:

 

 

SQL code

1. Select * from abc where a> 10
2. Union
3. Select * from abc where B <10

 

 

 

• 4 ------------------------------------- avoid difficult wildcard matching

Complex wildcard configuration may cause time-consuming queries. For example

 

SQL code

1. Select * from customer where zipcode like '98 ___';

 

Even if an index is created on the zipcode field, sequential scanning is used in this case. Should be changed

 

 

SQL code

1. Select * from customer
2. Where zipcode> = '000000' and zipcode <'000000'

 

 

 

 

• 5 ------------------------------------- adjust the performance of subqueries

There are two types of subqueries: associated subqueries and non-associated subqueries. The two types are analyzed as follows:

5.1 non-associated subqueries

When a non-correlated subquery is executed, the subquery is executed only once and the result set is sorted and saved in a temporary Oracle segment, each record is referenced by the parent Query when returned. When a subquery returns a large number of records, sorting these result sets and sorting temporary data segments will increase the system overhead. For example

 

SQL code

1. Select emp_name from emp_number where emp_id in (select emp_id from emp_func );

 

 

 

5.2 associate a subquery

To explain, what is an associated subquery is that the conditions in the subquery Use columns in the parent query, for example:

SQL code

1. Select emp_name from emp_number where emp_id in (select emp_id from emp_func where emp_number.emp_id = emp_func.emp_id );

 

For records returned to the parent query, the subquery runs once per row. Therefore, make sure that the index is used as much as possible in the subquery. Associated subqueries have higher system overhead.

My application principle for subqueries is: the more layers of nested queries, the lower the efficiency, so we should try to avoid subqueries. If the subquery is unavoidable, filter as many rows as possible in the subquery.

 

• 6---EXISTS and IN Operator

6.1 join subqueries with IN are redundant because the IN clause provides the same functions as related operations IN subqueries. For example

SQL code

1. Select emp_name from emp_member where emp_id in (select emp_id from emp_func where emp_member.emp_id = emp_func.emp_id );

 

 

6.2 It is inappropriate to specify the EXISTS clause for a non-correlated subquery, because this produces Cartesian Product ). For example

SQL code

1. Select emp_name from emp_member
2. Where exists (select emp_id from emp_func );

 

 

6.3 try NOT to use the not in clause. Although the MINUS clause requires two queries, the MINUS clause is still faster than the not in clause. IN this case, you should write

SQL code

1. Select emp_name from emp_member where emp_id
2. In (select emp_id from emp_member
3. Minus
4. Select emp_id from emp_func where func_id like '201312 ');

 

 

 

 

Finally, I tested the index performance. The following example is displayed on the Internet. I just picked it up. I think it's very classic.

1. Create a dumpy table and add 10 million rows of records to the dumpy table. The ID column is an ordered integer, the Name column is a random string, and the Rand column is a random value.

In the Oracle test environment, the syntax for creating a table is as follows:

SQL code

1. Create table dumpy
2. (
3. Id number (10 ),
4. Name varchar2 (10 ),
5. Rand number (10, 2)
6. );

 

The syntax for adding data is as follows:

SQL code

1. Declare
2. Recordcount integer: = 10000000; -- 10 million records
3. Begin
4. For I in 1 .. recordcount loop
5. Insert into dumpy (id, name, rand)
6. Values (I, dbms_random.string ('x', 8 ),
7. Abs (dbms_random.random)/100.0 );
8. If mod (I, 1000) = 0 then
9. Commit; -- submit 1000 entries per insert
10. End if;
11. End loop;
12. End;

After the data is successfully added, the row whose Rand value is between 1000 and 2000 is queried. Add indexes to compare the query speed changes before and after optimization.

 Tip: run the set timing on command to display the execution time of each statement.

Oracle SQL statement Optimization

We recommend that you partition the table and create a partition index if the data volume of the table is large and you need to perform such queries frequently.
Your SQL statement is simplified, and the amount of data is large. It will be very slow no matter how you tune it.
This is usually because the application requirements are not taken into account when designing tables.

How to optimize SQL statements in oracle databases

Oracle comes with an SQL analyzer, which can be used to analyze simple and independent single-segment SQL statements. Then, it can provide you with an analysis result. You can add indexes based on the results. One of the most effective ways to optimize this SQL statement. Select the statement F5 you want to optimize to quickly start the analysis function.

In addition, there are many optimization methods, such as reducing views and reducing the use of functions in the Where condition.