asked on

Forcing indexes to be used in regular table and Global temporary tables

How can we make sure that a "SELECT" query or "INSERT" query is using indexes in a regular tables..
All I know is, we can use /*+ index ( table_name index_name) */ on regular tables. Are there any other options other than using Index Hints to force indexes.

Also, I see that sometimes that index hints are ignored and tables do not use Indexes.

Can this also be done on Global Temporary tables.

Please advise, quick reply is appreciated!

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

d27m11y d27m11y

ASKER

Can we do the same thing in global temporary tables?

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

d27m11y d27m11y

ASKER

I guess I am not clear here. As you suggested, if I set it to "Rule based optimizer" , does it go by and then use and "Index Hint", will it be surely using Index hint?

alter session set OPTIMIZER_MODE = RULE;

Please advise!

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

d27m11y d27m11y

ASKER

well, actually I did not get a chance to test it. This was the question asked by someone to me and so I was not sure what to answer. They asked me if we can force indexes on Global temporary tables and regular tables both

I will definitely test it :)

Thanks so much for your help!