Oracle Query Performance Optimization practices

?

1. Principles of Index use

?

Do not use full blur on the index, but like ' asdf% ' is possible, that is, do not contains, available Startwith

Do not perform functions on the index, expression operations, or use is null to determine, otherwise the full table scan will be used

Does not equal also causes a full table scan, using column < ' A ' or column > ' a ' instead

If one column has an index and the other has no index, then C1 or C2 will cause a full table scan;

A composite index query, if it contains a leading index, will take effect, otherwise the full table will be scanned;

?

2. Order

?

Where statement: Try to put a large range of filters in the back;

Table connection: The table with large-scale data filtering is placed behind;

?

3. Implementation plan

?

Learn to view execution plans in Sqldeveloper

General principle: Avoid full-scale scan and use index.

Eg:

General Query

?

Plus index optimization:

The index is used for data within 10 days. Where order_id is an index column of the OI table

?

and (Oi. order_id like ' 150913% ' OR oi. order_id like ' 150914% ' OR oi. order_id like ' 150915% ' OR oi. order_id like ' 150916% ' OR oi. order_id like ' 150917% ' OR oi. order_id like ' 150918% ' OR oi. order_id like ' 150919% ' OR oi. order_id like ' 150920% ' OR oi. order_id like ' 150921% ' OR oi. order_id like ' 150922% ' OR oi. order_id like ' 150,923% ')

?

The speed is increased several times, of course, this should be from the design of space, avoid large table scan.

?

Oracle Query Performance Optimization practices