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

From: Dion Cho <>
Date: Wed, 5 Nov 2008 18:24:47 -0800 (PST)
Message-ID: <>

On 11월6일, 오전6시54분, wrote:
> 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:
> 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;

Two methods(search them in

  1. index join: index_join hint
  2. (b*tree index combination): index_combine hint

Both operations are available in 9i (and maybe in 8i). And Oracle chooses them without hints when it considers them efficient than single index access.

But using multiple indexes is not always beneficiary. It totally depends on how much rows would be pre-filerted by multiple indexes.

Dion Cho Received on Wed Nov 05 2008 - 20:24:47 CST

Original text of this message