ORDER BY behavior in 9i and 10g databases

[garfield1]

Hi,
I am very confused to know the behavior of clauses action in different versions of oracle database.This is one of the example,I am going to show you which I had made and results are different on 9i and 10g-

SELECT 1 element, '1' value FROM dual
UNION ALL
SELECT 1 element, '2' value FROM dual
UNION ALL
SELECT 1 element, '3' value FROM dual
ORDER BY element;

Results are as follows:
[On 9i ]
1 1
1 2
1 3
[On 10g ]
1 1
1 3
1 2
Why and what the procedure both versions follows .

[Praetor]

Hello dear,

The results of both queries are consistent within the ORDER BY clause that only orders by the starting first column. If you needed the result which should be arranged and sorted by both columns then you have to say like that. The database is not obliged to return the result in a consistent way unless you externally defined and command through the ORDER BY clause - and indeed it will not.

You are some bit "lucky" in 9i database that the access path,the oracle 9i is returning the data in the way as you need and think.

[Modifier]

Using Order By clause :

The Order By clause is used to get the data from database object which is used with the query.It takes the representation of data in two ways-
Ascending and descending order are two ways in which the rows values are being represents,the Ascending order is default and Descending is manually defined.

For example,if you have inserted some values in the table and which is not in order and you select the column as a normal query procedure.Then using ORDER BY clause,you can order those columns in a way.

This concept is also applied on the large tables also which contain more than one thousands of records

[MindSpace]

ORDER BY clause databases

As Modifiers discussed so much about the ORDER BY clause but I will show you an example which can be very helpful for you to understand the functionality of ORDER BY clause.

select
  author_last_name
from
  author
order by author_last_name;

This example will show you the following result which is fetched from table named AUTHOR and result is represented with the help of ORDER BY clause.

[opaper]

Now you are working on version 10 and when you perform the queries like distinct the 10g version uses a hash values concept and doesn't follow any such type of order to the returned values.

In the earlier versions, it fetched the values and perform sorting and then it removes the duplication.

The reason is that you have jumped form the different version that's why this type of confusions are arising.Don't worry there are some little new concepts are evolved in the version 10.