| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Force index?
First issue...if you are looking at the EXPLAIN PLAN output and Oracle
is not using the index that you think it should be, ensure that 1)
you are using your desired optimization mode (rule or cost), and 2) if
you are using cost-based optimization, collect statistics for the
tables accessed by your query by using the ANALYZE command. These
statistics are used by the optimizer when generating the query plan.
Now, you would "force" Oracle to use a specified index using hints... which are "suggestions that you give the optimizer for optimizing a SQL statement."
In your exact case (assuming that index ind_a is on table a), this would be:
SELECT /*+ INDEX(a ind_a) */ x, y, z
FROM a, b, c
WHERE a.x = b.y AND b.z = c.m;
Check out the "Optimization Modes and Hints" chapter of the Oracle Server Tuning manual (chapter 8 of the Oracle8 version of this manual).
On Wed, 13 May 1998 10:13:17 -0400, Alex Vilner <avilner_at_usa.net> wrote:
>Hello,
>
>Is there a way to FORCE the use of index in the SELECT query?
>We have several indexes defined on tables, used in a query.
>One index would benefit one type of query, while other benefits
>the other. It seems during EXPLAIN PLAN, that it is not using the
>index it should.
>
>Is there something like:
>SELECT x, y, z
>FROM a, b, c
>WHERE a.x = b.y
> AND b.z = c.m
>FORCE INDEX ind_a
>?
>
>Thank you in advance.
>
>Alex Vilner
>
Thanks!
Joel
Joel R. Kallman Oracle Government, Education, & Health
Columbus, OH http://govt.us.oracle.com jkallman@us.oracle.com http://www.oracle.com
 |
 |