6.4 Query Optimization in Oracle

Query Optimization in Oracle

Some of the query optimization measures used in Oracle are the following:

–Indexes unnecessary for small tables. i.e., if the size of the actual data record is not much larger than the index record, the search time in the index table and the data table will be comparable. Hence indexes will not make much difference in the performance of queries.

–Indexes/clusters when retrieving less than 25% of rows. The overhead of searching in the index file will be more when retrieving more rows.

–Multiple column WHERE clauses

–evaluations causing largest number of eliminations performed first

–JOIN-columns should be indexed. JOIN columns or Foreign Key columns may be indexed since queries based on these columns can be expected to be very frequent.

–Index not used in queries containing NULL / NOT NULL. Index tables will not have NULL / NOT NULL entries. Hence need not search for these in the index table.