Home » RDBMS Server » Performance Tuning » Use of Index (Oracle 9i)
Use of Index [message #345330] Wed, 03 September 2008 02:03 Go to next message
me_arindam
Messages: 26
Registered: March 2008
Location: India
Junior Member
Hi,

I have a table named ADDRESS.

One column is STATE.

I have an index on that column IDX_STATE.

When I am issuing like :

select * from address----- 3800000 rows
The result is full table scan.
But

When I am issuing :
select * from address where state = 'MA'---- 5 rows
Still it is going for full table scan.

I have used the monitoring feature to see whether index is being used or not.
I have also seen the explain plan. But could not get why the index is not being used.

When stat condition is checked in where clause the % of rows is 1.71% of total rows.

The optimizer_mode is CHOOSE


If I use hints to use index,then the index is used by the query.
Please let me know how I can get to know when index will be used or not.

Thanks.
Arindam
Re: Use of Index [message #345332 is a reply to message #345330] Wed, 03 September 2008 02:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I have used the monitoring feature to see whether index is being used or not.
I have also seen the explain plan. But could not get why the index is not being used.

And if you don't post what you did we don't see anything either.

Quote:
Please let me know how I can get to know when index will be used or not.

Activate tracing.

Regards
Michel
Re: Use of Index [message #345389 is a reply to message #345332] Wed, 03 September 2008 05:32 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Look at the plan from V$sql_plan - that's the plan that was actually used.

I suspect that the statistics are out of date on your table.
Re: Use of Index [message #345421 is a reply to message #345330] Wed, 03 September 2008 09:33 Go to previous messageGo to next message
macdba
Messages: 27
Registered: May 2005
Location: US
Junior Member
You may need to analyze the table.

analyze table address compute statistics;

After this check the explain plan.

If it does not work,
describe the table address and post the explain plan

regards
--mak
Re: Use of Index [message #345422 is a reply to message #345332] Wed, 03 September 2008 09:38 Go to previous messageGo to next message
me_arindam
Messages: 26
Registered: March 2008
Location: India
Junior Member
What I have used so far:

explain plan set statement_id = '0022'
for select * from address where state = 'MA'

Then

select * from plan_table
where statement_id = '0022'

Output is :

STATEMENT_ID OPERATION OPTIONS OBJECT_NAME OPTIMIZER

0022 SELECT STATEMENT CHOOSE
0022 TABLE ACCESS FULL ADDRESS ANALYZED

The index has not been used.


I have also seen like:


alter index IDX_STATE monitoring usage

Then

Query like:

select * from address where state = 'MA'

And then:


select * from v$object_usage
where table_name = 'ADDRESS'

The output is:

INDEX_NAME TABLE_NAME MONITORING USED

IDX_STATE ADDRESS YES NO


That means the index has not been used.

But the other indexes present on that table is being used when querying.

Please explain.
Re: Use of Index [message #345424 is a reply to message #345330] Wed, 03 September 2008 09:44 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above

Re: Use of Index [message #345430 is a reply to message #345422] Wed, 03 September 2008 10:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From one of your previous topics:
Michel Cadot wrote on Mon, 19 May 2008 12:17
...
But before please read OraFAQ Forum Guide, "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

Regards
Michel


Re: Use of Index [message #345705 is a reply to message #345430] Thu, 04 September 2008 10:07 Go to previous messageGo to next message
macdba
Messages: 27
Registered: May 2005
Location: US
Junior Member
can you describe the table and send the output?
Also is state column has any check constraint on it and enabled with NOVALIDATE option? NOLVALIDATE option may ignore the index and do a full table scan.

Also check if the index is getting used if you specify it with hint and instead of * use 1 column in select
Re: Use of Index [message #345900 is a reply to message #345705] Fri, 05 September 2008 04:10 Go to previous messageGo to next message
me_arindam
Messages: 26
Registered: March 2008
Location: India
Junior Member
Hi All

Actually I have just written elaborately.......
I haven'y used huge lines of code.

There is no such check constraint and no such no validate option.

Re: Use of Index [message #346519 is a reply to message #345330] Mon, 08 September 2008 14:58 Go to previous message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
May want to use dbms_stats package to gather statistics
over analyze.

Most interesting situation. I would like to see the explain
plan for the exact SQL you are executing
(since you reference'd you were executing sql 'like' your
example, is their other criteria besides state = 'MA'?)

I have a situation where two initializations parms are set
for an OLTP system such that in my DEV environment I reset
at the session level optimizer_index_caching and optimizer_index_cost_adj to defaults of 0 and 100 because Oracle
would always use an index when it wasnt merited.

Take a look at how the cost adjustment affects a plan:

(here's a big table with STATE on it and an index on STATE)

SELECT STATE FROM EFPREF_PROVIDER WHERE STATE  = 'MA';


SELECT STATEMENT Optimizer Mode=CHOOSE		       	             	 
  INDEX FAST FULL SCAN	MPIEFP_DEV.EFREF$ST	 


alter session set optimizer_index_cost_adj = 100;


SELECT STATE FROM EFPREF_PROVIDER WHERE STATE  = 'MA';

SELECT STATEMENT Optimizer Mode=CHOOSE		 	      	             	 
  INDEX RANGE SCAN	MPIEFP_DEV.EFREF$ST	  	



In the first case I weighted the index at 300% and it choose
a fast full scan.


Regards,
Harry
Previous Topic: Why showing BITMAP in Plan while no bitmap exists for the table
Next Topic: Tuning of delete stmt
Goto Forum:
  


Current Time: Tue Dec 06 06:08:37 CST 2016

Total time taken to generate the page: 0.11394 seconds