We are currently in the process of migrating some of our applications from Oracle 9i to Oracle 10g. One of the problems that we faced while migrating the application code is the order in which the result is returned for a particular set of queries.
We have some dropdown boxes (e.g Country Name) in our application which are populated dynamically by a query like this:
SELECT DISTINCT country_name FROM countries WHERE current_rec_ind = 'Y';
The DISTINCT and "GROUP BY" are two clauses that inherently force the sorting of data set in case of Oracle 9i. So, when you have a DISTINCT or "GROUP BY" in your SELECT query, you are most likely to get the data set sorted by the columns in the SELECT list. This happens because the SQL Optimizer (Cost based) in Oracle 9i is implemented in such a way that it gets the lowest cost for a query when it first sorts the data and then removes the duplicates.
So, for the dropdown, we always used to get the country names sorted alphabetically.
But this is not the case with Oracle 10g. The SQL optimizer in 10g does not sort the data in order to remove duplicates. So, we were not getting the country names in proper order. Here are the explain plans of the same query in the two Oracle versions to make it clearer:
The second step in the plan makes it all clear.
This is one of the potential risks that should be considered for your application code while migrating from 9i to 10g. So, its always better to have an "ORDER BY" in your queries if you need sorted data to avoid backward compatibility problems.
जब बात दिल से लगा ली तब ही बन पाए गुरु
16 hours ago
Posts
