SQL & PL/SQL

Hi Guyz,

I am facing a performance issue in a query which contains DISTINCT function. Following is my query:

SELECT /*+ ORDERED USE_NL_WITH_INDEX(c DIMENSION_KEY_PK) */
DISTINCT f.*,c.client_ids
FROM FACT_TAB f, DIM_TAB c
WHERE f.client = c.dimension_key

FACT_TAB = Fact table with a bitmap index on client column (10,000,000 records).
DIM_TAB = Dimension table with dimension_key as primary key (100,000 records).

when i select only fact table columns in the above query, the query executes within a second. But when i execute the above query it takes more than 15 minutes to execute.

How can i improve the above query. Any suggestions or tips would be helpful.

Thanks in advance.