SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Select distinct performance

700833Sep 20 2011 — edited Sep 22 2011

Hi,
I have the following issue. A table with appox 650,000 rows and 200 columns is being queried for distinct values on a column(SN) that is of type VARCHAR2(200 Bytes).

The query returns 40 distinct values in 30 secs. I would like to bring that time down.

The original DBA had created a single index on two columns (SN, DN). Column DN is of type NUMBER(10,0).

When I run the query with autotrace on I see that the index is not being used and a full table scan is being done.

I tried creating an index on just SN, that is not being used either.

Any suggestions?

Thanks.

This post has been answered by John Spencer on Sep 20 2011

Jump to Answer

Locked Post

New comments cannot be posted to this locked post.

Locked on Oct 20 2011

Added on Sep 20 2011

14 comments

5,238 views