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

On 11¿ù6ÀÏ, ¿ÀÀü6½Ã54ºÐ, dba_..._at_yahoo.com 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:
>
> http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/optimo...
>
> http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/hintsr...
>
> 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 tahiti.oracle.com)

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