OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » about index (Oracle 10.2.0.4.0, RedHatLinux-5)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

about index [message #592534]

Fri, 09 August 2013 08:18

8939513598$
Messages: 103
Registered: July 2013
Location: chennai

Senior Member

Hi Michael,How to force an index if the table not using the index?

Report message to a moderator

Re: about index [message #592538 is a reply to message #592534]

Fri, 09 August 2013 08:27

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

Michel Cadot
Messages: 68264
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

Report message to a moderator

Re: about index [message #593196 is a reply to message #592552]

Wed, 14 August 2013 20:49

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.

Report message to a moderator

Re: about index [message #593304 is a reply to message #592534]

Thu, 15 August 2013 10:06

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Aug 17 14:51:32 CDT 2022