Hints

Why using hints?
Oracle comes with an optimizer that promises to optimize a query's execution plan.
When this optimizer is really doing a good job, no hints should be required at all.

Sometimes, however, the characteristics of the data in the database are changing rapidly, so that the optimizer (or more accuratly, its statistics) are out of date. In this case, a hint could help.

It must also be noted, that Oracle allows to lock the statistics when they look ideal which should make the hints meaningless again.

All hints except /*+ rule */

Example:

SELECT /*+ index(t1 t1_abc) index(t2 t2_abc) */ COUNT(*)
FROM t1, t2
WHERE t1.col1 = t2.col1;


FIRST_ROWS(n):
The FIRST_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best response time (minimum resource usage to return first row).

set autotrace trace exp

SELECT table_name FROM dba_tables
WHERE owner = 'SYS' AND table_name LIKE '%$' ORDER BY 1;

SELECT /*+ FIRST_ROWS(10) */ table_name
FROM dba_tables WHERE owner = 'SYS'
AND table_name LIKE '%$' ORDER BY 1;

Access Method INDEX:

CREATE INDEX ix_customers_gender
ON customers(gender);

set autotrace traceonly explain

SELECT * FROM customers WHERE gender = 'M';

SELECT /*+ INDEX(customers ix_customers_gender) */ *
FROM customers WHERE gender = 'M';

SELECT /*+ INDEX_ASC(customers ix_customers_gender) */ *
FROM customers WHERE gender = 'M';

SELECT /*+ INDEX_DESC(customers ix_customers_gender) */ *
FROM customers WHERE gender = 'M';

We can use First row and Index Together:

/*+FIRST_ROWS(20) INDEX(table_alias index_name) */

Links:

http://psoug.org/reference/hints.html
http://www.adp-gmbh.ch/ora/sql/hints/index.html