Re: Hints difference between AND_EQUAL, INDEX_JOIN and INDEX_COMBINE

"Jason" <jungerle_at_gmail.com> wrote in message news:1134760340.979628.221540_at_z14g2000cwz.googlegroups.com...
> Does anyone know the real difference between these hints? I've read
> the documentation in the "Oracle 9i Database Performance Tuning Guide
> and Reference" and the "Oracle 9i SQL Reference". Both seem to say the
> same thing just in different fashions, essentially, combining indexes
> for a better access path. INDEX_COMBINE appears to be only for
> bitmapped indexes so I assume the other 2 hints are for normal indexes
> (although the documentation doesn't state this explicitly).
>
> Thanks!
> Jason
>

AND_EQUAL:
    Can operate on 2 to 5 non-unique, single-column indexes     with an equality condition on the indexed values.     For colX = const each index produces a list of rowids     which are guaranteed to be in rowid order, hence     pre-sorted and ready for a merge join. rowids that survive     the join are used to access the table

Index_join

    Can be used to join any two indexes - no restrictions     on number of columns, uniqueness, or predicates.     Used to derive results without visiting the table at all.     Each index is used to supply a (sub)set of its columns     plus the relevant rowids. Oracle then performs a hash     join on the two sets of data - using the rowid as the     join column. Any data surviving the data is the answer     set.
    Having joined two indexes by hash join, it is possible     for Oracle to join a third, and so on until all the required     columns have been join into the final result set

Index combine

    Used for bitmap operators (although you can reference     b-tree indexes in the hint to indicate to Oracle that the     index is a candidate for 'rowid conversion to bitmap').     Oracle acquires bitmaps from each index, and uses the     AND, OR, NOT, or MERGE operators to produce     a result bit string that can then be converted into rowids     for visiting the table.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 29th Nov 2005