Sql – Make a query with DISTINCT clause run faster

distinctoraclesql

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?

Best Answer

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.

Related Solutions

Mysql – Subqueries vs joins

A "correlated subquery" (i.e., one in which the where condition depends on values obtained from the rows of the containing query) will execute once for each row. A non-correlated subquery (one in which the where condition is independent of the containing query) will execute once at the beginning. The SQL engine makes this distinction automatically.

But, yeah, explain-plan will give you the dirty details.

Mysql – How to SELECT rows with MAX(Column value), PARTITION by another column in MYSQL

You are so close! All you need to do is select BOTH the home and its max date time, then join back to the topten table on BOTH fields:

SELECT tt.*
FROM topten tt
INNER JOIN
    (SELECT home, MAX(datetime) AS MaxDateTime
    FROM topten
    GROUP BY home) groupedtt 
ON tt.home = groupedtt.home 
AND tt.datetime = groupedtt.MaxDateTime

Best Answer

Related Solutions

Mysql – Subqueries vs joins

Mysql – How to SELECT rows with MAX(Column value), PARTITION by another column in MYSQL

Related Topic