Full table scan query

Afternoon all, here is today's daft question !

Oracle 8.0.5. (I know, but it is customer driven so I can't upgrade yet) :o)
The optimiser is CHOOSE
The statistics are up to date because I re-computed them. DB_FILE_MULTIBLOCK_READ_COUNT is 25
HP-UX 11:00 32 bit.

I have a table with 2,500,000 rows in it. There is an index on two columns in it and there are 175 unique values in the first column and 350,000 in the second column of the index.

A developer is running the statement :

        select * from the_table where first_index_column = 'some_value';

This will return 9 rows from the table.

The explain plan gives a full table scan for the select above. When hinted to use the index, it refuses and again gives a FTS. When I created histograms on the indexed columns, I still get a FTS.

However, if I create an index on the first column only, the optimizer uses that index whether or not I have histograms. But, because this new index has the same leading column as the original one, this is a redundant index (I thought) and will waste time inserting rows into the table.

How on earth do I get the statement to use the first index? Should I try to use it ?
And can someone explain why thie above is happening please? I suspect the reason is that there are too few values in the index, but, why then does it use the new index which only has 175 unique values in 2,500,000 entries?

I suspect that the existing index on the table has not been rebuilt for some time, but would that make the slightest difference - I don't think so.

Thanks in advance.

Regrads, Norman.

Norman Dunbar			EMail:	Norman.Dunbar_at_LFS.co.uk
Database/Unix administrator	Phone:	0113 289 6265
				Fax:	0113 289 3146
Lynx Financial Systems Ltd.	URL:	http://www.Lynx-FS.com


------------------------------------------------------------------------