OraFAQ Forum: Performance Tuning » Index is not working properly in my query ?

Home » RDBMS Server » Performance Tuning » Index is not working properly in my query ?

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

Index is not working properly in my query ? [message #400053]

Sat, 25 April 2009 09:13

tondapi
Messages: 99
Registered: August 2007
Location: usa

Member

Hi,

I am using 9i.In my query i am using index but query does not considering the index.It run full table scaninig.I dont why my query doesn't using index's.

Can any one explain me what can i do regarding this.

thanks

Report message to a moderator

Re: Index is not working properly in my query ? [message #400054 is a reply to message #400053]

Sat, 25 April 2009 09:19

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Please, please, please Read & Follow Posting Guidelines above.
Go to the section labeled "Practice" & do as directed.

Working mysteries without ANY clues.

You have table & DDL. We don't
You have data & DML. We don't.
You have code. We don't.

You have ALL the information & can't debug the problem.
Why do you expect others, who have NO details, to solve your mystery?

Report message to a moderator

Re: Index is not working properly in my query ? [message #400067 is a reply to message #400053]

Sat, 25 April 2009 11:50

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You can first read:
- OraFAQ Forum Guide, Performance Tuning section
- Performances Tuning sticky

Regards
Michel

Report message to a moderator

Re: Index is not working properly in my query ? [message #400072 is a reply to message #400053]

Sat, 25 April 2009 13:40

gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen

Senior Member

q) what is your optimizer_mode ?

Was it using index before ? One chance is that too much data change happend and you have not analyzed your table after that.

Report message to a moderator

Re: Index is not working properly in my query ? [message #403453 is a reply to message #400053]

Fri, 15 May 2009 13:24

g.manivannan
Messages: 2
Registered: May 2009
Location: Mumbai

Junior Member

Try to use Hint and check the explain plan and cost.
Also adjust the init.ora parameter optimizer_index_cost_adj.

Report message to a moderator

Re: Index is not working properly in my query ? [message #403458 is a reply to message #403453]

Fri, 15 May 2009 13:35

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

g.manivannan wrote on Fri, 15 May 2009 20:24

Try to use Hint and check the explain plan and cost.
Also adjust the init.ora parameter optimizer_index_cost_adj.

DON'T do that they are the worst advices you could get.

Regards
Michel

Report message to a moderator

Re: Index is not working properly in my query ? [message #403621 is a reply to message #400053]

Sun, 17 May 2009 11:00

bangalibor
Messages: 15
Registered: September 2008
Location: dhaka

Junior Member

please check the reason , like why index is getting surpressed.

may be the cpu cost is high
may be you are using a condition on the query which eliminates the use of index

Post your sql code here, with the execution plan . then we may try

Report message to a moderator

Previous Topic:	Inner workings: how often is data written to disk
Next Topic:	Queries on the fly using DBMS_ADVANCED_REWRITE

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jun 30 00:41:23 CDT 2024