Difference between Hints and Indexes

Question

Oracle Hints

Hints can extend the capability of cost-based optimization. This technique is especially helpful if your distributed query contains an aggregate, subquery, or complex SQL.

Indexes

Indexes can provide faster data access. Indexes are optional structures associated with tables and clusters that allow SQL queries to execute more quickly against a table.

Those are the definitions of Hints and Indexes provided by Oracle, having said that, we can see that both have the objective of improving the performance of the query. I'd like to know if there is any other difference between them.

Hints are more for performance issues, and Indexes are for response speed?

Answer 1

Indexes and Hints are fundamentally different.

An index is an additional data structure that you can instruct Oracle (or pretty much any database) to create automatically for you to make lookup faster (at the cost of making insertion and update a little slower because that data structure has to be inserted and/or updated too).

The optimizer will look at every statement you send and decide which indexes to use to make it as fast as possible.

But the optimizer cannot work magic, it's only as good as the data it gets and the programmers that built it. So sometimes, the optimizer picks an index that you know is not the best for that statement.

For example you may have multiple indexes on a table, one to find a person by name, another to find a person by date of birth and a third to find a person by phone number.

You know that for a certain statement using the person-by-name index would be best, but the optimizer decides to take the person-by-phone index. So your statement is slow.

That is the time for hints. A hint is the way to tell the optimizer: I know better than you. Please use a specific index that I tell you to use.

So to sum it up: indexes make lookups fast. A hint makes sure a specific index is used despite the automatism that says something else. So it's not one or the other. It's indexes first and hints as a way to point to an index you know is best. A hint is useless without an index that it can hint to, while an index on it's own should normally be enough if the optimizer works well.

Answer 2

Both are related to performance and response speed.

A hint gives the Cost-Based Optimiser (CBO) a "nudge" in a particular direction; an explicit instruction to try and get to the data in the way you tell it to. The use of hints requires a [very] good understanding of the CBO and how Oracle chooses to do things internally. Given that this might change with any given Release, it's probably the less effective of the two, generally speaking.

I can't recall having applied hints to any Oracle SQL I've ever written.

Indexes are generally there to speed up access.
If Oracle can use an index to get to data faster, then it [usually] will and the increase in speed can be huge! Put millions of rows into an un-indexed table and try to query them; Oracle has no choice but to scan through them all and it will be really, really slow. Put an index on the field you're filtering by and things can get [several] orders of magnitude faster. A simple binary search on a single value can get to any of, say, one million records in about twenty comparisons (instead of the million). Oracle's indexes are generally even better than that, holding more values per "bucket" and getting down to the detail even faster.

Of course, the flip side is that inserts and updates can get slower, because Oracle has to update the base data and then the index[es] as well. It's trade-off, like so much else in the Database World.