Index [message #302488] |
Mon, 25 February 2008 21:57 |
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 |
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
|
|
|
|
|