Re: Can Oracle use Multiple indexes at once on a single table?

I just was looking at Tom Kyte's book: Effective Oracle by Design page 462

Yes, Oracle can.
The term is called: index joins.

You can also find more info here:

http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/optimops.htm#56069

http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/hintsref.htm#5232

Here, a hint forces the query use both indexes:

SELECT /*+index_join(employees emp_emp_id_pk emp_department_ix)*/

    employee_id, department_id
    FROM employees
    WHERE department_id > 50; Received on Wed Nov 05 2008 - 15:54:01 CST