Oracle SQL tuning

In most cases, Oracle uses index t to traverse tables more quickly, and the optimizer improves performance primarily based on defined indexes.

However, if the SQL code written in the WHERE clause of the SQL statement is not reasonable, it will cause the optimizer to delete the index and use a full table scan, which is generally referred to as the poor SQL statement.

When writing SQL statements, we should be aware of the principles by which the optimizer removes the index, which helps to write high-performance SQL statements

1. Is null and is not NULL

You cannot use NULL as an index, and any column that contains null values will not be included in the index.

Even if the index has more than one column, the column is excluded from the index as long as there is a column in the column that contains null.

This means that if a column has a null value, even indexing the column does not improve performance.

Any statement optimizer that uses is null or is not NULL in the WHERE clause is not allowed to use the indexed

If we have to use is null and need to provide query efficiency can be indexed by function

Examples are as follows

CREATE TABLE Test_date (name VARCHAR2 ( -, day date); insert into test_date (name, day) VALUES ('Lucy',NULLINSERT into Test_date (name, day) VALUES ('Jony',NULL) insert into test_date (name,day) VALUES ('James', sysdate);Select* fromtest_date;
--Create the Decode function index instead of the CREATE index finx_day on Test_date (The Decode (day,NULL,'N','Y'))

--use decode judgment instead of IS null to judgeSelect* fromTest_date AwhereDecode (Day,NULL,'N','Y') ='N'

2. Join columns

For a joined column, the optimizer does not use the index, even if the last join value is a static value.

Suppose there is a staff table (employee), for a worker's surname and name are divided into two columns (first_name and last_name),

Now it's time to inquire about a worker named Bill Clinton,Cliton.

Here is an SQL statement that takes a join query.

Select  from where first_name| | ' ' | | last_name ='beill cliton'

The above statement can be used to find out if there is a Bill Cliton this employee, but it is important to note that the system optimizer does not use an index created based on last_name.

When written in this SQL statement, theOracle system can take an index created based on last_name.

 from where  ='beill' and last_name ='cliton'

If a variable (name) holds the name of the employee of Bill Cliton, how can we avoid a full traversal of this?

You can use a function to separate the first and last names in the variable name, but it is important to note that this function cannot be used on an indexed column. ‘

Here is the SQL query script

Select* fromEmployeewherefirst_name= SUBSTR ('&&name',1, INSTR ('&&name',' ')-1) Andlast_name= SUBSTR ('&&name', INSTR ('&&name ',' ') +1)

3. A like statement with wildcard characters (%)

The following SQL explains:

Select  from where ' %cliton% '

This is because the wildcard character (%) appears at the beginning of the search term, so the Oracle system does not use the last_name index.

In many cases it may not be possible to avoid this, but be sure to be in the bottom of your mind, so using a wildcard will slow down the query.

However, when wildcards appear elsewhere in a string, the optimizer can take advantage of the index.

The indexes are used in the following query:

Select  from where ' c% '

Note to readers: in the real development of the project, if the regular fuzzy query, can adopt SOLR or Elasticsearch or direct Lucene can also

4. Order by statement

The order BY statement determines how Oracle will sort the returned query results.

The ORDER BY statement has no special restrictions on the columns to be sorted, or it can be added to a column (join or attach, etc.).

Any non-indexed item in the ORDER BY statement, or a computed expression, will slow down the query.

Double-check the order BY statement to find non-indexed items or expressions that degrade performance.

The solution to this problem is to rewrite the order BY statement to use the index, or you can establish another index for the column you are using, and you should absolutely avoid using an expression in the ORDER BY clause.

5. Ideal alternative to not

We often use some logical expressions in the WHERE clause when querying, such as greater than, less than, equal to, and not equal to, etc.

You can also use and (with), or (or), and not (non). Not can be used to negate any logical operation symbol.

The following is an example of a NOT clause:

where not (status ='VALID')

If you want to use not, you should precede the phrase with parentheses and precede the phrase with the NOT operator.

The NOT operator is included in another logical operator, which is the not equal to (<>;) operator.

In other words, the not is still in the operator, even if the not word is not explicitly added in the query where clause.

See the following example:

where status <>'INVALID'

Let's look at the following example:

Select  from where salary<>;

For this query, it can be rewritten to not use not:

Select  from where salary< or salary>;

Although the results of these two queries are the same, the second query scenario is faster than the first query scenario. The second query allows Oracle to use indexes on salary columns, while the first query cannot use indexes.

6. In and exists (there is a duplicate below)

Sometimes a column is compared to a series of values. The simplest approach is to use subqueries in the WHERE clause. You can use a two-format subquery in the WHERE clause.

The first format is the use of the in operator:

where inch (Selectfromwhere ...);

The second format is the use of the exist operator:

where exists (select'X' from ... where ...);

I believe most people will use the first format because it is easier to write, whereas the second format is much more efficient than the first.

In Oracle, you can overwrite almost all in-operator queries to subqueries that use exists.

In the second format, the subquery begins with ' select ' X '. Use the EXISTS clause no matter what data the subquery extracts from the table it only looks at the WHERE clause.

This way, the optimizer does not have to traverse the entire table and can do its work only by indexing (this assumes that the column used in the WHERE statement has an index).

As opposed to the in clause, exists uses a concatenated subquery, which is more difficult to construct than in subqueries.

By using the exist,oracle system, the primary query is checked first, and then the subquery is run until it finds the first match, which saves time.

When the Oracle system executes an in subquery, the subquery is executed first, and the resulting list is stored in an indexed temporary table.

Before executing a subquery, the system first suspends the primary query, and executes the subquery until it has been executed, and then performs the main query after the query is held in the temporary table. This is why using exists is faster than using in usually queries.

Instead of not, use not exists as much as possible, although both use not (which cannot be used to slow down the index), and the not exists is more efficient than the not-in query

7. Avoid using "*" in the SELECT clause:

When you want to list all columns in the SELECT clause, it is a convenient way to refer to ' * ' Using a dynamic SQL column.

Unfortunately, this is a very inefficient approach.

In fact, in the process of parsing, Oracle translates ' * ' into all column names, which is done by querying the data dictionary, which means more time is spent.

8, reduce the number of access to the database:

Oracle performs a lot of work internally when executing each SQL statement:

Parse SQL statements, estimate utilization of indexes, bind variables, read data blocks, and more.

Thus, reducing the number of accesses to the database can actually reduce the workload of Oracle.

Example:

Topic-I'm looking for information for students numbered 0001 and 0002.

(Low efficiency)

Select  from where ' 0001 ' ; Select  from where ' 0002 ';

Efficient

Select  from where ' 0001 ' ' 0002 ';

9. Use the Decode function to reduce processing time:

Use the Decode function to avoid duplicate scans of the same record or duplicate connections to the same table.

Example:

(Low efficiency)

Select  from where ' 0001 ' ' anger% ' ; Select  from where ' 0002 ' ' anger% ';

Efficient

SelectCount (Decode (dept_id,'0001','XYZ',NULL)) Count_01,count (Decode (dept_id,'0002','XYZ',NULL)) Count_02,sum (Decode (dept_id,'0001', dept_id,NULL)) Sum_01,sum (Decode (dept_id,'0002', dept_id,NULL)) sum_02 fromtable1whereName like'anger%';

10, integrated simple, no associated database access:

If you have a few simple database query statements, you can integrate them into a single query (even if they are not related)

Example:

(Low efficiency)

Select  from where ' 0001 ' ; Select  from where ' 0001 ' ; Select  from where ' 0001 ';

Efficient

    Select  t1.name, T2.name, T3.name    from  table1 T1, table2 t2, table3 T3     where ' 0001 ' ' 0001 ' ' 0001 '

Note: Although the above example is efficient, but the readability is poor, need the amount of love!

11. Delete Duplicate records:

The most efficient way to delete duplicate records (because ROWID is used)

Example:

 from table1 T1 where t1.rowid > (selectfromwhere

12, try not to use the HAVING clause, you can consider where to replace:

Having the result set is filtered only after all records have been retrieved. This processing requires sorting, totals, and so on.

If you can limit the number of records through the WHERE clause, you can reduce this overhead.

13, try to use the table alias:

When you concatenate multiple tables in an SQL statement, use the alias of the table and prefix the aliases on each column.

In this way, you can reduce the parsing time and reduce the syntax errors caused by the column ambiguity.

14. Replace distinct with exists:

When submitting a query that contains one-to-many table information, avoid using DISTINCT in the SELECT clause. You can generally consider replacing with exists

Example:

(Low efficiency)

Select  from where d.dept_no = e.dept_no;

Efficient

Select  from where exists (select1fromwhere d.dept_no = e.dept_no);

exists makes queries faster because the RDBMS core module returns results immediately after the conditions of the subquery are met.

15. Replace the exists with the table connection:

In general, table joins are more efficient than exists.

Example:

(Low efficiency)

Select  from where exists (select1fromwhere'W');

Efficient

Select  from where ' W '

Oracle SQL tuning