How to Force Oracle to use the Index without /*+ rule */

[PCStorm]

I created a unique index to get a query to run faster. The explain plan is fine in most databases after the index is created. Query goes from two minutes to 1 second.

However, the database I really need it to work in will not use the index without the /*+ rule */ hint in the Select. We are looking for a DB solution so we do not have to change the C code.

I computed statistics. Still Oracle will not use the index by default. I have actually never seen this happen before. Anyone have any ideas? Please.

 

[PCStorm]

Additional Information: Optimizer_mode is CHOOSE in all databases. I know we could change to rule but do not go that route if possible.

 

[karluk]

Well, I would try rebuilding the index and then recomputing statistics. It's possible it has become sufficiently disorganized that Oracle is disregarding it.

 

[PCStorm]

Thanks it is a good idea. However, we did do that and it did not work. Any other ideas?

 

[vivekm]

Are you gathering statistics using DBMS_STATS package? Use this rather than the ANALYZE statement. If so, have you checked that these statistics are updated after you have run them?

Regards,
Vivek

 

[ThomVF]

are you saying you do not want to use the /*+ index(table) */ hint ?
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com

 

[PCStorm]

To answer Thomas V. Flaherty's Question. That is correct, we do not want to use the index table hint because the SQL statement is being executed from C code. We have used both the index and rule hint in testing the SQL and both produce EXPLAIN PLANS using the index correctly.

We are doing this analysis bypassing the C code, using the same SQL statement the C code is generating. However, changing the code will require a new build. Making a DB change and then being sure the database acts upon the unmodified SQL statement correctly will not.

 

[ThomVF]

You can "ALTER SESSION OPTIMIZER_GOAL RULE" but I am guessing that won't work since you probably establish the session inside the C code - right ?


Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com

 

[PCStorm]

Response to Thomas:

Correct. Also the impact to the other SQL statements the program is executing. I would make a bet that on an import, and this is, you do not want rule based optimization.

I just do not understand what could be wrong in the database that it will not naturally choose the unique index where in larger and smaller database instances it does not have a problem doing that with the same SQL statement.

 

[ThomVF]

Have you checked to see if the index status is "VALID" ?

What else could be different between this database and where the index is used?
Data volume in the table?
Index invalid?
Statistics?

Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com

 

[sem]

What command did you use to gather statistics? It's not a joke. As Vivek mentioned and I may confirm, in some situations using dbms_stats provides completely different results from ANALYZE command.

 

[PCStorm]

Response: I used DBMS_STATS