Home » RDBMS Server » Performance Tuning » about index (Oracle 10.2.0.4.0, RedHatLinux-5)
about index [message #592534] Fri, 09 August 2013 08:18 Go to next message
8939513598$
Messages: 103
Registered: July 2013
Location: chennai
Senior Member
Hi Michael,How to force an index if the table not using the index?

Re: about index [message #592538 is a reply to message #592534] Fri, 09 August 2013 08:27 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Probably this question is only for "Michel" I guess!!!

Anyway, you need to use an index hint to force the optimizer to use a specific access path for a table. Syntax
/*+ INDEX(table_name index_name) */


Check index hint

[Updated on: Fri, 09 August 2013 08:30]

Report message to a moderator

Re: about index [message #592552 is a reply to message #592534] Fri, 09 August 2013 10:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But maybe Oracle is right to NOT use the index.
Read Performances Tuning sticky, last part of second post.

Regards
Michel
Re: about index [message #593196 is a reply to message #592552] Wed, 14 August 2013 20:49 Go to previous messageGo to next message
jbarnard
Messages: 2
Registered: August 2013
Location: New Zealand
Junior Member
Michael is correct, an index does not always result in better performance of a query. You need to determine how many rows in the query result set from the table you want to index. If it is retrieving columns that are not part of the index and the result set is half or more rows of the table, an index will be slower than a table scan because it won't be going back and forth between the index and table.

The most common cause I have seen for the optimizer to not use an index is because the first column of the index is not in the where clause.
Re: about index [message #593304 is a reply to message #592534] Thu, 15 August 2013 10:06 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.oracle.com/pls/db121/search?remark=quick_search&word=cost+based+optimizer

http://www.lmgtfy.com/?q=Oracle+cost+based+optimizer

[Updated on: Thu, 15 August 2013 10:07]

Report message to a moderator

Previous Topic: Please help me to improve the performance of this query.
Next Topic: dbms_stats.set_table_prefs and "LIST STALE"
Goto Forum:
  


Current Time: Thu Mar 28 17:03:58 CDT 2024