ORACLENERD: Is DISTINCT a Bug?

Is DISTINCT a Bug?

Thursday, January 29, 2009

In your application or data model that is.

On a well designed system, I've rarely seen the need to use DISTINCT.

If anything, I use it to do analysis on table data or maybe a summary report. Something like this:

SELECT
  COUNT( DISTINCT( col_1 ) ) col1_count,
  COUNT( DISTINCT( col_2 ) ) col2_count,
  COUNT( DISTINCT( col_3 ) ) col3_count,
  COUNT(*) count_all
FROM my_table;

That will tell me the basic distribution of certain data elements in a specific table. Good for possibly determining whether a column needs a Bitmap Index.

In an OLTP system however, the liberal use of DISTINCT signals (to me) a problem in the underlying model. Obviously not all instances are without merit, but I'd be willing to bet that the majority are an indicator.

How about you? What are you thoughts on DISTINCT? Good? Bad? Indifferent?

Labels: database, design, rant, sql

¶ 1/29/2009 09:35:00 PM

Comments:

QA folks are fond of "count distinct" when planning their tests.

How many different users login every day? How many different pages do we have?

# posted by

Chen Shapira : January 29, 2009 at 10:36 PM

OK use of DISTINCT there.

Especially for our QA brethren who aren't the best at SQL. ;)

# posted by

oraclenerd : January 29, 2009 at 10:44 PM

And that was a sad swipe on my part to my QA friends out there. My apologies.

# posted by

oraclenerd : January 29, 2009 at 10:55 PM

DISTINCT is just a tool, nothing inherently wrong with it. If you are using it in the context of a data access layer for your application code, then there's likely a problem.

When I see it in a view's source, that never passes the smell test.

However, all too frequently there are needs to query for sets of things that are not necessarily key-oriented and DISTINCT fills the bill nicely.

# posted by

Crisatunity : January 29, 2009 at 11:39 PM

It puts my spider-sense on tingle. The idea is that if it isn't there, you'll get more records. However DISTINCT doesn't prune out specific records, so it isn't really a filter. It is a set operator (like UNION, MINUS and INTERSECT). That's more 'mathematical' than I'd expect in a business context.

# posted by

Gary Myers : January 30, 2009 at 12:12 AM

@gary @chrisatunity

I'm glad I'm not alone there, "smell-test" and "spidey-senses tingling" is about how I feel.

Thanks Gary for getting into Math and stuff...taking it right over my head. ;)

Crisatunity, a tool indeed. Definitely has it's place. Just don't want to see it everywhere (unnecessarily).

I guess I've seen it used mostly by people who don't understand SQL or sets. That was probably the source of my jab above.

# posted by

oraclenerd : January 30, 2009 at 12:19 AM

Have yet to see one well designed system or application or report that needs it.

Mostly, it is used as a crutch by those who don't want to bother with analyzing the data model and just want a quick "correct" result at any cost.

Everytime I see one, I see a chance for tuning.

# posted by

Noons : January 30, 2009 at 1:40 AM

You can get the number of distinct values in a table from the table statistics. Donno how current those are, but I'd imagine if you tell Oracle to analyze your table before looking up said statistics you should be ok.

# posted by

Anonymous : January 30, 2009 at 1:12 PM