Home » RDBMS Server » Performance Tuning » Index (Oracle 10g)
Index [message #302488] Mon, 25 February 2008 21:57 Go to next message
subbu_tce
Messages: 98
Registered: July 2007
Location: pune
Member
Am having a table which contains index on location_code column.
When am writing a query where in the selection criteria is based on location code.so,system has to use that index , but its not using why?
Even when i give the index hint still not using why?
create table insure_pol_ssumm 
(
  policy_no       varchar2(50),
  location_code   number(10),
  issue_dt        date
);

create index idx_location_code on insure_pol_ssumm(location_code);

create index idx_issue_dt on insure_pol_ssumm(issue_dt);

query1 ) select policy_no /*+ index (  idx_location_code) */ from insure_pol_ssumm where  location_code=1901

query2 ) select policy_no from insure_pol_ssumm where  location_code=1901 						   
query1 and query2 going to full table scan why?

Optimizer mode is all_rows.Kindly explain...


[mod-edit: Frank added code tags]

[Updated on: Mon, 25 February 2008 23:56] by Moderator

Report message to a moderator

Re: Index [message #302491 is a reply to message #302488] Mon, 25 February 2008 22:55 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Have you gathered statistics with DBMS_STATS.GATHER_TABLE_STATS()?

How many rows in the table?
How many returned by your SQL?

Ross Leishman
Re: Index [message #302508 is a reply to message #302488] Tue, 26 February 2008 00:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe (Oracle estimates) it is faster to not use it.

Regards
Michel
Re: Index [message #302524 is a reply to message #302488] Tue, 26 February 2008 00:53 Go to previous message
subbu_tce
Messages: 98
Registered: July 2007
Location: pune
Member
Thank u.
After gathering statistics system using the index.
Previous Topic: Whats the difference between these two queries ? - tunning purspective
Next Topic: Execution time differences between Devl and Test
Goto Forum:
  


Current Time: Sun Jun 30 14:33:42 CDT 2024