SQL & PL/SQL

Oracle 10.2.0.3.0
optimizer_index_cost_adj = 100

Have a simple query on a table with 15mill records and 13 columns.

Select distinct columnA
from tablea

I have a normal index with only the columnA in it, that I rebuilt and ran statisitcs on and ran a shrink on the table and update statistics. I ran a partial statistics on both, same results and then a full on both.

I know with the distinct it has to do a full scan, but why is the CBO choosing a table over index.

The only thing I could think is that the index and tables are in the same tablespace and that a larger block size for my indexes might look better to the CBO on IO, do to record size. Just can't get how a full table scan of 15mill records and 22 columns wins out of a index only the one column and the health and statistics are current.



Thanks for you help.