CODEWITHSUNDEEP
sqloracle-databasedistinct
sharath
sharath

Reputation: 37

Make a query with DISTINCT clause run faster

I have the following query:

select distinct type, account 
  from balances_tbl 
 where month = 'DEC-12'

The table balances_tbl has over 3 million records per month. The distinct type and account rows are maybe around 20,000 records. The month column is indexed. The type and account columns are also indexed. The query takes a really long time (about 30 minutes) to execute.

Is there a way to make this query with the DISTINCT clause faster?

Upvotes: 2

Views: 13029

Answers (5)

gpeche
gpeche

Reputation: 22514

First of all, check with your DBA that you have fresh statistics for that table / indexes.

Then, 30 minutes is too long for that query, Without seeing the EXPLAIN PLAN, I guess Oracle is doing a full scan, so it is visiting every row in the table. Given that there are ~ 3 million rows per month, that might probably be a lot of rows. As you say that month is indexed, you can try forcing index access on month with:

select /*+ index(b <index_on_month)>*/ distinct type, account 
from balances_tbl b
where month = 'DEC-12'

where <index_on_month> is the name of the index on the month column. As it is a date-related field, chances are that the index has a good clustering factor.

Anyway, without seeing the execution plan it is very hard to determine why it is taking so long.

Upvotes: 1

Art
Art

Reputation: 17

Here's the simplest solution - no need for indexes, plans and all this ... :

SELECT DISTINCT d.deptno, dname FROM scott.dept D, scott.emp E WHERE D.deptno = E.deptno
/  
-- Same as Distinct --
SELECT deptno, dname FROM scott.dept D WHERE EXISTS ( SELECT 'X' FROM scott.emp E WHERE E.deptno = D.deptno)
/

Upvotes: -1

glh
glh

Reputation: 4972

Try to add an aggregate function and ensure there is an index on the month column. I shouldn't take this long unless the server is a poor one.

Try:

select type, 
       account,
       count(*) as dummy
from   balances_tbl 
where  month = 'DEC-12'
group by type,
       account

Upvotes: 0

Dileep
Dileep

Reputation: 664

you can create Month wise partion or use can use the oracle optimization Hints for faster retrieval of data

these are the hints which 'll useful for ur query

 1) /*+ ALL_ROWS */
 2) /*+ FIRST_ROWS(n) */ 
 3) /*+ parallel */

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270391

You need to add an index that has all three columns. I would suggest an index on (month, type, account). That way, Oracle can do its work just by scanning the index.

Otherwise, you have to do a full table scan and then the work for finding just the distinct values.

I might also suggest partitioning the table by month. However, that would require reorganizing the table and would be much more work.

Upvotes: 6

Related Questions