Oracle indexes avoid full table Scan

The following describes the issues that need to be paid attention to when writing the where clause of some SQL statements. In these where clauses, even if some columns have indexes

The following describes the issues that need to be paid attention to when writing the where clause of some SQL statements. In these where clauses, even if some columns have indexes

Improving Oracle performance-how to index

I. Question proposal
At the early stage of application system development, due to the relatively small amount of data in the development database, the performance of SQL statement writing is not good for querying SQL statements and writing complex views, however, after the application system is submitted to the actual application, as the data in the database increases, the system response speed becomes one of the most important problems to be solved by the system. An important aspect of system optimization is the optimization of SQL statements. For massive data, the speed difference between inferior SQL statements and high-quality SQL statements can reach hundreds of times. It can be seen that a system can not simply implement its functions, instead, we need to write high-quality SQL statements to improve system availability.

In most cases, Oracle uses indexes to traverse tables faster. The optimizer improves performance based on the defined indexes. However, if the SQL code written in the where clause of the SQL statement is unreasonable, the optimizer will delete the index and use full table scan, in general, such SQL statements are so-called inferior SQL statements. When writing SQL statements, we should be clear about the optimizer's principles for deleting indexes, which helps to write high-performance SQL statements.

Ii. Notes for writing SQL statements

The following describes the issues that need to be paid attention to when writing the where clause of some SQL statements. In these where clauses, even if some columns have indexes, the system cannot use these indexes when running the SQL statement because of poor SQL writing. The full table scan is also used, this greatly reduces the response speed.

1. is null and IS NOT NULL

Null cannot be used as an index. Any column containing null values will not be included in the index. Even if there are multiple columns in the index, as long as one of these columns contains null, this column will be excluded from the index. That is to say, if a column has a null value, even if the column is indexed, the performance will not be improved.

Any statement optimizer that uses is null or is not null in the where clause cannot use indexes.

2. Join Columns

For joined columns, the optimizer does not use indexes even if the last joined value is a static value. Let's take a look at an example. Suppose there is a employee table (employee). For a employee's surname and name are divided into two columns for storage (FIRST_NAME and LAST_NAME), we want to query a table named Bill. bill Cliton employees.

The following is an SQL statement that uses join queries,

Select * from employss

Where

First_name | '| last_name = 'beill cliton'

The preceding statement can be used to check whether the employee Bill Cliton exists. However, the system optimizer does not use the index created based on last_name.

When the following SQL statement is used, the Oracle system can use an index created based on last_name.

Select * from employee

Where

First_name = 'beill' and last_name = 'cliton'

What should we do in the following situations? If a variable (name) stores the name of the employee Bill Cliton, how can we avoid full traversal and use indexes in this case? You can use a function to separate the surname and name in the variable name. However, note that this function cannot be used in the index column. The following is an SQL query script:

Select * from employee

Where

First_name = SUBSTR ('& name', 1, INSTR (' & name', '')-1)

And

Last_name = SUBSTR ('& name', INSTR (' & name', '') + 1)