| Use of Index [message #345330] |
Wed, 03 September 2008 02:03  |
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 #345389 is a reply to message #345332] |
Wed, 03 September 2008 05:32  |
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 |
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 |
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 #345705 is a reply to message #345430] |
Thu, 04 September 2008 10:07 |
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 |
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 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
