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 ?
>

Hello André,

if you have few distinct values in colimn b, it might be worth trying a bitmap index on this column. Since it has a bit vector for every distict value, it has a chance to give you the distinct values really fast. If you have a large number of didtinct values this might not be very clever though. Also if the number of distinct values changes often, the bitmap index will not behave well (at least it did not on Oracle 7.3, maybe this changed in the meantime. ;-)

But beware! This is just a wild guess, I did not test that.

HTH,
Lothar

-- 
Lothar Armbrüster       | la_at_oktagramm.de
Hauptstr. 26            | la_at_heptagramm.de
D-65346 Eltville        | lothar.armbruester_at_t-online.de