Indexes for selecting distinct values

I have a table of customers with a large number of rows, and several status code columns. The various status codes will be small, relative to the number of customers. The customers are divided into regions. (Some numbers: right now I have a region with ~350k customers, and 410 distinct status codes.)

I want to figure out what indexes will speed up a query for the distinct status codes. A customer can have multiple status codes, and there are several columns for these to go in (not my design and not under my control):

CREATE TABLE CUST (
    CUST_ID NUMBER(10) PRIMARY KEY NOT NULL,

    REGION_ID NUMBER(10) NOT NULL,
    STATUS_1_CD VARCHAR2(2),
    STATUS_2_CD VARCHAR2(2),
    STATUS_3_CD VARCHAR2(2)

The query to get the distinct status codes is something like:

SELECT DISTINCT STATUS_1_CD FROM CUST WHERE REGION_ID = :reg AND STATUS_1_CD IS NOT NULL
UNION
SELECT DISTINCT STATUS_2_CD FROM CUST WHERE REGION_ID = :reg AND STATUS_2_CD IS NOT NULL
UNION
SELECT DISTINCT STATUS_3_CD FROM CUST WHERE REGION_ID = :reg AND STATUS_3_CD IS NOT NULL I think that concatenated indexes on REGION_ID + STATUS_1_CD, REGION_ID + STATUS_2_CD, and REGION_ID + STATUS_3_CD may help this, but I'm not sure. If so, would it be better to use bitmap indexes? I think I need to, since the columns in question are nullable?

Any feedback will be deeply appreciated. If you prefer to reply via email I can be reached at drconrad at gmail dot com. I will be glad to summarize the replies to the newsgroup.

Thanks in advance,
David Conrad Received on Mon Oct 17 2005 - 12:15:33 CDT