Re: Hints used most often?

OK I'll take flag :)
Quite often used by me:

CARDINALITY - for object types. Default cardinality for them is this magic number 8168 (BTW why exactly this one?) which quite often is too big and leads to full scans and hash joins of joined tables.

ALL_ROWS, FIRST_ROWS (n) in mixed environments where there are both reports and data entries. Then the most important one is set db wide and second one used for reports (usually all_rows)/data entry (usually first_rows) statements.
Sometimes also based on some parameter analysis creating 2 separate queries one with ALL_ROWS and FULL and USE_HASH and one with FIRST_ROWS(1). FULL, USE_HASH - for reports mostly to be sure that there won't be unnecessary surprises due to some specific parameters

LEADING - there are some cases when I'm 99,99% sure that particular table x have to be the first one.

Quite rarely used:

INDEX - to be sure that table won't be full scanned. Usually without specifying index name.

INDEX_FFS - for reports instead of FULL if I'm sure there is index satisfying all need for info.

REWRITE - to force query rewrite irrespective of cost.

APPEND - for big ad_hoc INSERTS mostly.

DRIVING_SITE - a few cases using db links.

I'm probably a bit different than most of this list memebers because I'm working from development side, not as DBA, so I've almost never used, for example, PARALLEL, because it is definitely harmful for most OLTP apps :) Except probably some data migration code.

Gints Plivna
http://www.gplivna.eu

2008/4/1, kyle Hailey <kylelf_at_gmail.com>:
> I'm curious what hints people use the most often tuning SQL statements and why.
>

--
http://www.freelists.org/webpage/oracle-l