DISTINCT not using an index I have table (2139868 rows):

COLUMN1 NUMBER(20,0)
COLUMN2 XMLTYPE
COLUMN3 XMLTYPE
COLUMN4 VARCHAR2(50 BYTE)
COLUMN5 VARCHAR2(50 BYTE)
COLUMN6 VARCHAR2(50 BYTE)
COLUMN7 VARCHAR2(50 BYTE)
COLUMN8 VARCHAR2(50 BYTE)
COLUMN9 VARCHAR2(500 BYTE)
COLUMN10 VARCHAR2(100 BYTE)
COLUMN11 VARCHAR2(50 BYTE)
COLUMN12 VARCHAR2(20 BYTE)
COLUMN13 VARCHAR2(20 BYTE)
COLUMN14 TIMESTAMP(6)
COLUMN15 VARCHAR2(50 BYTE)
COLUMN16 PROPERTIES_ARRAY_TYPE
COLUMN17 TIMESTAMP(6)
COLUMN18 TIMESTAMP(6)


In a database

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production


I'm trying to select all the distinct values of COLUMN4; unfortunately this takes roughly 18 seconds despite the COLUMN4 being indexed.  There are only 5 unique items.

How can I speed up my query?

(Work doesn't let me upload images so I'll type out the execution plan, use your imagination)

Operation             OBJECT_NAME CARDINALITY   COST
---------------------- ----------- ----------- ------
SELECT STATEMENT       NIL                    4 65463
+ HASH(UNIQUE)         NIL                    4 65463
++ TABLE ACCESS (FULL) TABLE1           2010857 65334
If I saw a data model where a table had 2.1 million rows, one of the columns had only 5 distinct values, and I knew that people wanted to get a listing of those distinct values on a somewhat regular basis, I would strongly suspect that the data model was missing a lookup/ dimension table.  Rather than trying to tune your current query, I would bet that the better answer would be to create a separate table that has just those 5 distinct values, create a foreign key relationship between your current table and the new lookup table, and then modify your query to hit the new lookup table instead.