Re: Which index to create ?

On Mon, 08 Dec 2003 09:21:24 +0100, André Hartmann wrote:
> 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 ?

The question is how selective B is. If there is a large number of values in B, it is probably the sort, that is time consuming.

Do you need first row performance? In this case a

SELECT /*+ FIRST_ROWS */ B
FROM X
WHERE B<> <impossible value> -- or B=B as mentioned GROUP BY B with an (analyzed) index on B might do what you want.

Hth,
Joachim

-- 
Warnung: \" kann Augenkrebs verursachen.