I have a big table (61mil rows) that has an indexed not-null column ID. I want to select the distinct values of that column. I expected the

select distinct id from bigtable ;

to run fast, making use of that index. Yet it takes more than 40sec to run on a fast server.

The EXPLAIN PLAN shows that a FAST FULL SCAN of that index takes place, whereas I'd expect a INDEX RANGE SCAN instead. The cost comes up as 93000.

Please advise. Do I perhaps need a different index type?

  • 2
    Unrelated, but: the parentheses around the column name are completely useless – a_horse_with_no_name Sep 20 '19 at 13:06
  • 1
    Please edit your question and add the complete execution plan as formatted text please – a_horse_with_no_name Sep 20 '19 at 13:07
  • 5
    An index range scan is used for conditions like id > 42. Your query has to look at all id values to evaluate the distinct ones. Reading the complete index using a FAST FULL SCAN seems the only sensible approach to me. – a_horse_with_no_name Sep 20 '19 at 13:08
  • If a fast full scan is all I can hope for, then I'd welcome suggestions on how to maintain that list of distinct values in other ways. I only run Standard Edition so MVs are not available. – GI D Sep 20 '19 at 13:47
  • Materialized views are available in the standard edition. You don't get query rewrite so you'd need to alter your query to hit the MV rather than the table. Is that an option? From a data model standpoint, it sounds like you're missing a table with all the id values that bigtable can then have a foreign key reference to. Then you'd just need to query that new dimension table. – Justin Cave Sep 20 '19 at 15:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.