Oracle Database Optimization

oracle| Data | database | optimization
Optimization is the choice of the most effective way to execute SQL statements. The Oracle Optimizer chooses what it deems most effective

method to execute the SQL statement.



1). Is null and is not NULL

If a column has a null value, even indexing the column does not improve performance.

2). Write different blocks of SQL statements for different jobs

It's not a good way to write a big chunk of SQL for a different job. It often leads to an suboptimal result for each task

Of. To do different work with SQL, it is generally better to write a different statement block than to write one.

3). In and exists

Select name from employee where name is not in (select name from student);

Select name from employee where NOT exists (select name from student);

The first sentence of the SQL statement is less efficient than the second sentence.

By using Exists,oracle, you first check the main query, and then run the subquery until it finds the first match

Items, which saves time. When Oracle executes an in subquery, it executes the subquery first, and the results obtained

The list is stored in an indexed temporary table. Before the subquery is executed, the system suspends the main query and stays

The subquery executes, is stored in a temporary table, and then executes the main query. This is also the use of exists than in

The reason for the query speed is usually fast.

4). Not operator

Select * FROM Employee where salary<>1000;

Select * FROM employee where salary<1000 or salary>1000;

The first sentence of the SQL statement is less efficient than the second sentence because the second SQL statement can use the index.

5). ORDER BY statement

The order BY statement is inefficient to perform because it is sorted. You should avoid using an expression in an ORDER BY clause.

6). The connection of the column

SELECT * FROM Employee where name| | Department= ' Zyzbioinfo ';

SELECT * FROM employee where name= ' Zyz ' and department= ' bioinfo ';

These two queries, the second sentence is faster than the first sentence, because for the join operator ' | | ' Query, the Oracle optimizer is not

That will use the index.

7). Wildcard '% ' when the wildcard appears at the beginning of the search term, the Oracle optimizer does not use an index

Select * FROM employee where name like '%z% ';

Select * FROM employee where name like ' z% ';

The second sentence will perform more efficiently than the first sentence, but the query result set may be different.

8). You should try to avoid mixed-type expressions

Suppose field Studentno is VARCHAR2 type

There is a statement select * from student where studentno>123;

Oracle will have an implied type conversion. Implicit type conversions may cause the Oracle optimizer to ignore indexes.

You should then use an explicit type conversion select * from student where Studentno=to_char (123).

9). DISTINCT
Distinct always create a sort, so the query is slow.