Thursday, January 29, 2009

Is DISTINCT a Bug?

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?

9 comments:

Chen Shapira said...

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?

oraclenerd said...

OK use of DISTINCT there.

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

oraclenerd said...

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

Clever Idea Widgetry said...

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.

SydOracle said...

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.

oraclenerd said...

@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.

Noons said...

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.

Anonymous said...

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.

Mike Kemp said...

Absolutely a tool for sanity checks or for situations where there is a problem with the data model - or you suspect there might be one and you need some insurance against the possibility. I'd consider it a backup plan to a good data model myself.