Tutorial - How To Make Your SQL Lightning-Fast
- 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.
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
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
- 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)
next Oracle SQL tutorial explains why you shouldn't always use
index in your queries
to Oracle SQL
Tutorial part 1