Optimize common oracle SQL statements and oraclesql statements

Optimize common oracle SQL statements and oraclesql statements

1. execution efficiency caused *

Minimize the use of select * For queries. When you use * For queries *,
The database parses and converts * to all columns.

Select count (si. student_id)
From Student_info si (student_id is the index)
And
Select count (*) from Student_info si
During execution, the preceding statement is faster than the statement without index statistics below.

 

2. Avoid using computation on index columns.

In the WHERE clause, if the index column is part of the function, the optimizer will use full table scan without using the index.

Example:

Inefficiency:

SELECT... From dept where sal * 12> 25000;

Efficient:

SELECT... From dept where sal> 25000/12;

3. Replace with> =>

Efficient:

SELECT * from emp where deptno> = 4

Inefficiency:

SELECT * from emp where deptno> 3

The difference between the two lies in that the former DBMS will jump directly to the first record whose DEPT is equal to 4, while the latter will first locate the record whose DEPTNO is = 3 and scan forward to the record whose first DEPT is greater than 3.

4. 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 index columns will scan the entire table. 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 did not select 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.

5. 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 );

6. Avoid using is null and is not null in the index column.

7. where execution sequence

Where execution runs from bottom to bottom.

Select *

From student_info si -- student information table

Where si. school_id = 10 -- school ID

And si. system_id = 100 -- System ID

When placing the where clause, place the conditions that can filter a large amount of data at the bottom.

8. Optimization in the from field:

Oracle installs table data from the right to the left, and puts the tables with the most excluded data to the back (basic table ).

For example, in associated queries, the curriculum is put behind and the curriculum table is put ahead, because the curriculum data is usually relatively small, and some score data can be quickly filtered out during Association.

9. Index failure

1. Index failure caused by Computation

2. Index failure caused by type conversion

3. Problems Caused by calculation on index Columns

4. Is not null (student_id Is an index)

5. Order by causes index failure (student_id is the index)

6. automatically select an index

7 ,! = Leading to index failure

8. Index failure caused by %