Oracle optimization-related

1,Oracle's parser according to right-to-left order the table name in the FROM clause is processed, so that the last table written in the FROM clause (the underlying table, driving tables) will be processed first . In the case that the FROM clause contains more than one table, you must select the table with the lowest number of record bars as the underlying table.

2. Oracle uses a bottom -up sequential parsing where clause, according to which the connection between tables must be written before other where conditions, and those that can filter out the maximum number of records must be written at the end of the WHERE clause.

3. Avoid using ' * ' in the SELECT clause (Oracle will convert ' * ' to all column names in the parsing process, which is done by querying the data dictionary, which means more time is spent).

4, replacement efficiency: In--"exists-" table connection

The constant list of in is optimized (for example: aab019 in (' 20 ', ' 30 ')), without exists substitution; in list equals or

5. Replace distinct with exists

Avoid using DISTINCT in the SELECT clause when submitting a query that contains one-to-many table information, such as a personal basic information table and a personal reference information table. You can generally consider replacing with exists

For example:

Low efficiency:

SELECT DISTINCT ac01.aac001

From AC02,AC01

where ac02.aac001 = ac01.aac001

and ac02.aae140= ' 31 '

and ac01.aab001= ' 100100 ';

Efficient:

Select ac01.aac001

From AC01

where exists (select 1 from ac02 where aac001 = ac01.aac001

and aae140= ' 31 ')

and ac01.aab001= ' 100100 ';

6. Try to replace Union with UNION all

The Union will discard the duplicate records and will have a sort of action that will waste time. Therefore, if there are no duplicate records, or can be allowed to have duplicate records, try to use union all to associate.

7, use 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.

For example:

(Low efficiency)

Select COUNT (1) from ac01 where aab001= ' 100001 ' and aac008= ' 1 ';

Select COUNT (1) from ac01 where aab001= ' 100001 ' and aac008= ' 2 ';

(Low efficiency)

Select count (1), aac008

From AC01

Where aab001= ' 100001 '

and aac008 in (' 1 ', ' 2 ')

Group BY aac008;

Efficient

Select COUNT (Decode (aac008, ' 1 ', ' 1 ', null)) ZZ,

Count (Decode (aac008, ' 2 ', ' 1 ', null)) TX

From AC01

where aab001= ' 100001 ';

Special Note:

Group BY and order by both affect performance, avoid unnecessary grouping and sorting when programming, or replace it with other effective programming methods, such as the above approach.

8 . When using left Jion, the difference between on and where conditions is as follows:

1. On condition is the condition used when generating a temporary table, which returns records from the left table regardless of whether the condition on is true.

2. Where condition is the condition that the temporary table is filtered after the temporal table has been generated. At this point there is no left join meaning (must return the record of the table on the right), the condition is not true all filter out.

So the condition of the left table should be placed after the where.

Oracle optimization-related