Difference between Hints and Indexes

by Juliano Costa   Last Updated October 11, 2018 13:05 PM - source

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 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?

Answers 1

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.

Phill  W.
Phill W.
October 11, 2018 12:42 PM

Related Questions

Oracle RMAN login with no recovery catalog

Updated July 05, 2015 13:02 PM

Multibyte character set in pl/sql

Updated August 13, 2015 17:02 PM

Multiple local Oracle XE instances

Updated June 14, 2016 08:02 AM

how to compare two large sets of data using PL/SQL?

Updated February 18, 2018 18:05 PM