[Untitled]‎ > ‎SQL Hint Docs‎ > ‎

Oracle: Popular Hints




INDEXES 
=============================================
    INDEX_ss 
           skip scan
           wolfgang
    INDEX_rs 10.2.0.3
           range scan
           http://jonathanlewis.wordpress.com/2007/06/17/hints-again/
           wolfgang
    FULL - the opposite of the above
           Connor most common
          for reports mostly to be sure that there won't be
          unnecessary surprises due to some specific parameters
           wolfgang
    INDEX - 
           Quite rarely used:
           to be sure that table won't be full scanned. Usually without specifying index name.
    INDEX(t index_name)           
           Niall
           -- Mark D Powell --
           where Oracle chooses the wrong index over the correct one
           Connor less common
           wolfgang
    INDEX_COMBINE 
           Ralph Graulich, OLTP System (9iR2 ) (40% of all hints)
    INDEX_JOIN 
           Ralph Graulich, OLTP System (9iR2 ) (10% of all hints)
    INDEX_FFS - 
           index fast full scan
           Ralph Graulich, OLTP System (9iR2 ) (10% of all hints)
           Quite rarely used:
           for reports instead of FULL if I'm sure there is index satisfying all need for info.
           wolfgang
    NO_INDEX
           Niall
           Connor less common

ORDERING
=============================================
    LEADING  
        there are some cases when I'm 99,99% sure that particular
        table x have to be the first one.
    ORDERED 
         1 Ralph Graulich, OLTP System (9iR2 ) (30% of all hints)
         2 Remigiusz Sokolowski  mainly for OLTP
           from time to time; usually with some additional hints
         3 Email: chris.dunscombe 
           The hints I've used most often tend to be ones affecting join order and join
           method e.g. 
         4 Connor most common
         5 -- Mark D Powell --
           usually with USE_NL  to get Oracle to not hash join
         6 wolfgang
           

Joins
=============================================
     USE_HASH 
           wolfgang
           usually accompanied by LEADING or ORDERED (for pre 10g)
           for reports mostly to be sure that there won't be
           unnecessary surprises due to some specific parameters
           Connor less common
           Dave Herring, DBA |   A c x i o m  M I C S / C S O
           gets use in various large parallel table joins where Oracle would
           instead pick a merge join and we've found hash is much better.  Probably
           with the proper level of stats Oracle would pick the best plan, but many
           tables we don't have the time to gather more than .5%.
     NO_USE_NL
     USE_NL - in general we accept a little bigger cost with smaller memory requirements
           Niall
          Email: chris.dunscombe
          The hints I've used most often tend to be ones affecting join order and join
          method e.g. 
           Connor less common
     NO_MERGE
            HTH.Milen


=============================================
     ALL_ROWS
           Connor most common
     FIRST_ROWS
           Niall
           Connor most common
           Dave Herring, DBA |   A c x i o m  M I C S / C S O
           As for FIRST_ROWS, I have that placed in a number of triggers (logon,
           logoff, on servererror) where quick lookups are done against V$ objects.
           These triggers are called thousands of times a day and I wanted to make
           sure it was clear to Oracle I only intend on 1 row being returned.
           Remigiusz Sokolowski <rems@wp-sa.pl>
           because we start developing our apps with optimizer set to all_rows, 
           so now there is plenty of code, which assumes just that index - sometimes 
           the optimizer chooses other indexes or paths than I plan
           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).

PUSHING and RE-WRITE
=============================================
    NO_PUSH_SUBQ
            HTH.Milen
    PUSH_SUBQ
            HTH.Milen
           Connor most common
           wolfgang
           used quite a bit in 8i together with use_hash, less these days
    NO_UNEST
           HTH.Milen
           Connor most common
    REWRITE - to force query rewrite irrespective of cost.
    DRIVING_SITE - a few cases using db links.

Statistics and Control
=============================================
    CARDINALITY 
           Connor most common
           Gints Plivna http://www.gplivna.eu
           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.
    GATHER_PLAN_STATISTICS 
           Dion Cho
            used as a *handy* sql trace 
    QB_NAME 
           Dion Cho
            to specifiy query block name when tuning complex queries
    DYNAMIC_SAMPLING (x)
           Dion Cho
            used as alternative to statistics for large-scale DW queries
          periodically
          once-in-a-while I run a query against Oracle metadata (9i) without stats
          on a database without optimizer_dynamic_sampling set, so I toss this in
          to get a better plan.
    OPT_PARAM 
           Dion Cho
            this magical hint is added in 10.2.0.3(IIRC)used to control optimizer behavior in query level



Comments