Menu
If I have a sql query that uses 'distinct' (in oracle), would it be faster than retrieving the non-distinct then getting the unique results via java programming?
I heard somewhere that oracle sql distinct is heavy, but is it heavier than manual 'distinction' via java programming?
Thanks, Franz
481
Summary. DISTINCT makes a query return unique rows only, and is often used injudiciously to suppress duplicate rows being returned by a bad query. Don't use DISTINCT to cover up errors.
Yes, the application needs to compare every record to the "distinct" records cache as it goes. You can improve performance by using an index, particularly on the numeric and date fields.
Most of the SELECT DISTINCT queries will perform exactly as fast as their simple SELECT counterparts, because the optimizer will do away with the step necessary for eliminating duplicates.
For MAX and MIN , you probably shouldn't ever use DISTINCT because the results will be the same as without DISTINCT , and the DISTINCT function will make your query substantially slower to return results.
The rule of the thumb is that the data is faster to process in the database than in your programming language.
The reason is that the data is already available in the database, and it saves the effort of going to your app:
As for Oracle DISTINCT being heavy, what it can mean is to never simply throw a DISTINCT at a query just because it seems like a good idea - profile it with a realistic data set since it can have serious performance implications.
183
Two main aspects:
I'd be very surprised to find that fetching all the data and then doing a distinct operation in Java was faster than doing it in the database.
21
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
