Oracle SQL Tutorial - How To Make Your SQL Lightning-Fast

Tip #4 - Use Selective Indexes

Tip #4 in this Oracle sql tutorial explains why you should use selective indexes in your SQL queries as this can make a big difference to the the performance of your queries in Oracle.

You might think the more indexes the better, however there is a trade-off between the performance of ypur queries and the performance of your insert, update and delete statements. This is because each of these operations will require extra writes to keep the index in step with the data itself (this is true for updates only if an indexed column is being updated). Also the more indexes you have, the slower these operations will be.

Where the balance is depends on whether you're Oracle database is mainly read-only (such as a data warehouse) or a read/write database (such as typical OLTP system). For a datawarehouse , you don't need to worry about the performance of update/insert/delete statements but for an OLTP database too many indexes will degrade overall performance.

If your database is a hybrid (i.e. used for both oltp and data warehouse applications) then add extra indexes to reporting-only tables and for the rest add only those indexes required for constraint validation - primary key, unique, foreign key and any others required for acceptable query performance.

To make the best use of indexes in Oracle, ensure they are selective (i.e. each value applies to just a few rows in the table). The best indexes to use are primary-key and unique-key indexes where each value in the index uniquely identifies one row in the table. The difference between a primary key and a unique key, is that a primary key column cannot be NULL whereas a unique key column can be.

If you do have indexes on your tables, make sure that you're not inadvertently disabling the use of an index in your SQL by doing any of the following in your queries:-

• using an operator on the indexed column (eg. <indexed_col> + 1) - unless you're using a function-based index;
• using hints hat disable the use of indexes (if you're running Oracle and using the cost based optimiser)
• using NULL and not equal checks. (eg. <indexed_col> <> 12345 ; or <indexed_col> IS NULL)

The next Oracle SQL tutorial explains why you shouldn't always use an index in your queries

Return to Oracle SQL Tutorial part 1