Oracle Performance Optimization Action Three: increase the scope limit for queries

Increase the scope of the query to avoid a full range of searches.

Example: The following query table record in time Actiontime less than March 1, 2001 of data:

SELECT * FROM record where Actiontime < To_date (' 20010301 ', ' yyyymm ');

The query plan indicates that the above query has a full table scan of the table, and if we know that the earliest data in the table is January 1, 2001, then we can add a minimum time,

Make the query within a full scope. Modify the following:

SELECT * FROM record where Actiontime < To_date (' 20010301 ', ' yyyymm ') and   actiontime > To_date (' 20010101 ', ' yyy Ymm ');

The latter SQL statement will take advantage of the index on the Actiontime field to improve query efficiency.

Replace ' 20010301 ' with a variable, which can be more than half the chance to improve efficiency, depending on the probability of the value being taken.

Similarly, for queries that are larger than a certain value, you can add "and column name < MAX (max)" in the WHERE clause if you know the current maximum possible value.

Oracle Performance Optimization Action Three: increase the scope limit for queries