Oracle SQL Performance Optimization series (11)

36.Use UNIONReplace OR (Applies to index columns)

In general, replacing OR in the WHERE clause with UNION will produce better results. using OR for index columns will scan the entire table. note that the preceding rules are only valid for multiple index columns. if a column is not indexed, the query efficiency may be reduced because you did not select OR.

In the following example, both LOC_ID and REGION have indexes.

Efficient:

SELECT LOC_ID, LOC_DESC, REGION

FROM LOCATION

WHERE LOC_ID = 10

UNION

SELECT LOC_ID, LOC_DESC, REGION

FROM LOCATION

Where region = "MELBOURNE"

 

Inefficiency:

SELECT LOC_ID, LOC_DESC, REGION

FROM LOCATION

WHERE LOC_ID = 10 or region = "MELBOURNE"

 

If you insist on using OR, you need to write the index columns with the least records at the beginning.

 

Note:

 

WHERE KEY1 = 10 (minimum returned Records)

OR KEY2 = 20 (the maximum number of records returned)

 

ORACLE converts the above

WHERE KEY1 = 10 AND

(NOT KEY1 = 10) AND KEY2 = 20)

 

Press:

 

The following test data is for reference only: (a = 1003)Returns a record, B = 1Returns 1003Records)

SQL> select * from unionvsor/* 1st test */

2 where a = 1003 or B = 1;

1003 rows selected.

Execution Plan

----------------------------------------------------------

0 select statement Optimizer = CHOOSE

1 0 CONCATENATION

2 1 table access (by index rowid) OF 'unionvsor'

3 2 INDEX (range scan) OF 'ub '(NON-UNIQUE)

4 1 table access (by index rowid) OF 'unionvsor'

5 4 INDEX (range scan) OF 'ua '(NON-UNIQUE)

Statistics

----------------------------------------------------------

0 recursive cballs

0 db block gets

144 consistent gets

0 physical reads

0 redo size

63749 bytes sent via SQL * Net to client

7751 bytes encoded ed via SQL * Net from client

68 SQL * Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1003 rows processed

SQL> select * from unionvsor/* 2nd test */

2 where B = 1 or a = 1003;

1003 rows selected.

Execution Plan

----------------------------------------------------------

0 select statement Optimizer = CHOOSE

1 0 CONCATENATION

2 1 table access (by index rowid) OF 'unionvsor'

3 2 INDEX (range scan) OF 'ua '(NON-UNIQUE)

4 1 table access (by index rowid) OF 'unionvsor'

5 4 INDEX (range scan) OF 'ub '(NON-UNIQUE)

Statistics

----------------------------------------------------------

0 recursive cballs

0 db block gets

143 consistent gets

0 physical reads

0 redo size

63749 bytes sent via SQL * Net to client

7751 bytes encoded ed via SQL * Net from client

68 SQL * Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1003 rows processed

 

SQL> select * from unionvsor/* 3rd test */

2 where a = 1003

3 union

4 select * from unionvsor

5 where B = 1;

1003 rows selected.

Execution Plan

----------------------------------------------------------

0 select statement Optimizer = CHOOSE

1 0 SORT (UNIQUE)

2 1 UNION-ALL

3 2 table access (by index rowid) OF 'unionvsor'

4 3 INDEX (range scan) OF 'ua '(NON-UNIQUE)

5 2 table access (by index rowid) OF 'unionvsor'

6 5 INDEX (range scan) OF 'ub '(NON-UNIQUE)

Statistics

----------------------------------------------------------

0 recursive cballs

0 db block gets

10 consistent gets

0 physical reads

0 redo size

63735 bytes sent via SQL * Net to client

7751 bytes encoded ed via SQL * Net from client

68 SQL * Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

1003 rows processed

Use UNIONFrom the consistent getsAnd SQL * NETAs shown in the following figure:

 

37.Use INTo replace OR

 

The following query can be replaced by more efficient statements:

 

Inefficiency:

 

SELECT ....

FROM LOCATION

WHERE LOC_ID = 10

OR LOC_ID = 20

OR LOC_ID = 30

 

Efficient

SELECT...

FROM LOCATION

WHERE LOC_IN IN (10, 20, 30 );

Press:

This is a simple and easy-to-remember rule, but the actual execution effect must be verified.The execution paths of the two seem to be the same.

 

 

38.Avoid using is null in the index ColumnAnd IS NOT NULL

To avoid using any columns that can be empty in the index, ORACLE will not be able to use this index. this record does not exist in the index if the column contains a null value. for a composite index, if each column is empty, this record does not exist in the index. if at least one column is not empty, the record is stored in the index.

Example:

If the unique index is created on column A and column B of the table, and the and B values of A record are (123, null), ORACLE will not accept the next one with the same, records (INSERTED) with a value of B (123, null ). however, if

All index columns are empty, and ORACLE considers the entire key value to be emptyNull is not equal to null. Therefore, you can insert 1000

Records with the same key value are empty!

 

Because the null value does not exist in the index column, the Null Value Comparison of the index column in The WHERE clause will disable ORACLE.

Example:

 

Inefficiency: (index failure)

SELECT...

FROM DEPARTMENT

WHERE DEPT_CODEIs not null;

 

Efficient: (index valid)

SELECT...

FROM DEPARTMENT

WHERE DEPT_CODE> = 0;