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:
SELECTThat 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.
COUNT( DISTINCT( col_1 ) ) col1_count,
COUNT( DISTINCT( col_2 ) ) col2_count,
COUNT( DISTINCT( col_3 ) ) col3_count,
COUNT(*) count_all
FROM my_table;
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:
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?
OK use of DISTINCT there.
Especially for our QA brethren who aren't the best at SQL. ;)
And that was a sad swipe on my part to my QA friends out there. My apologies.
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.
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.
@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.
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.
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.
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.
Post a Comment