Oracle Performance Optimization Action one: Avoid column operations

Any action on a column can result in a full table scan, where the so-called operations include database functions, calculation expressions, and so on, to move the operation to the right of the equation whenever possible.
Even remove the function.

Example 1: The columns in the following SQL conditional statements are properly indexed, but the execution speed is very slow in the case of 300,000 rows of data:

SELECT * from record where  substrb (cardno,1,4) = ' 5378 ' (13 seconds)  select * from record where  amount/30< 1000 (11 seconds)  select * from record where  to_char (actiontime, ' yyyymmdd ') = ' 19991201 ' (10 seconds)  

Because any action on a column in the WHERE clause results in a row-wise calculation of the SQL runtime, it has to perform a table scan without using the index above the column;
If these results are available at query compile time, they can be optimized by the SQL optimizer, use the index, avoid table scans, and therefore rewrite the SQL as follows:

SELECT * from record where Cardno like  ' 5378% ' (< 1 seconds) select * from record where amount  < 1000*30 (< 1 seconds) Sele CT * from record where actiontime= to_date (' 19991201 ', ' YYYYMMDD ') (< 1 sec)

Oracle Performance Optimization Action one: Avoid column operations