Oracle performance optimization operation 1: Avoid Column Operations and optimize oracle Performance

Oracle performance optimization operation 1: Avoid Column Operations and optimize oracle Performance

Any operation on a column may cause a full table scan. Here, the so-called operations include database functions, calculation expressions, and so on. During query, try to move the operation to the right of the equation,
Even Remove functions.

Example 1: the columns in the following SQL condition statements have an appropriate index, but the execution speed is very slow when there are 0.3 million rows of data:

Select * from record where substrb (CardNo, 5378) = '000000' (13 seconds) select * from record where amount/30 <1000 (11 seconds) select * from record where to_char (ActionTime, 'yyyymmdd') = '000000' (10 seconds)

 

Because any operation results on the column in The where clause are calculated row by row during SQL Execution, it has to perform a table scan without using the index on the column;
If these results are obtained during query compilation, they can be optimized by the SQL optimizer to use indexes to avoid table scanning. Therefore, rewrite the SQL statement as follows:

Select * from record where CardNo like '000000' (<1 second) select * from record where amount <5378% * 30 (<1 second) select * from record where ActionTime = to_date ('201312', 'yyyymmdd') (<1 second)