Re: Which index to create ?

André Hartmann wrote:

> Hi,
>
> I have a question regarding the tuning of one of my SQL statements.
> Suppose there is a table X in my Schema which has columns A, B, C (all
> INTEGER) where A is the primary key. Suppose further that the table is
> pretty large: 10 million rows or more. Quite frequently I want to do this
> statement:
>
> SELECT DISTINCT B FROM X
>
> The explain plan tells me there is a full table scan on X. So I thought it
> might help to create an index on the B column but I still get a full table
> scan. I believe that the full table scan is the main performance
> bottleneck... how can I get rid of the full table scan ?
>
> André

If column B is defined as NOT NULL and you use a hint pointing to the index the optimizer may use it. If NULLs are present chances are the FTS is what you are going to get.

BTW: Did you run DBMS_STATS after creating the index?

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)