Oracle 10g sequence order is different

never trust an "implicit" sort in any version
if you want your data sorted a certain way, then force it with order by

OR use a cluster or index organized table.

Those are still using "implicit" sorting and hence are unreliable.

I just ran the following query...

select /*+ PARALLEL(wordlist, 4) */ word from wordlist  (word list is an IOT)

here are the first 9 words returned...

Row#      WORD

1      COMPLAINANT
2      COMPLAINING
3      COMPLAINT
4      COMPLAISANCE
5      COMPLAISANT
6      COMPLEMENT
7      COMPLEMENTARITY
8      COMPLEMENTARY
9      COMPLEMENTATION

vs

select /*+ PARALLEL(wordlist, 4) */ word from wordlist order by word

Row#      WORD

1      A
2      AAA
3      AARDVARK
4      AARHUS
5      AARON
6      ABA
7      ABABA
8      ABACK
9      ABACUS

No question, it is implicit, but it will still order the data. A "normal" index or table scan will result in ordering by the index key.

I agree the only guaranteed way is to use explicit ORDER BY, but still consider the default on a normal query will be a old fashioned scan.

Since I'm tangenting, at least I can add some useful info, so let me say: I would consider an IOT or cluster where I had a predominant ORDER BY case. Meaning, if I typically ordered by col1, col2 90% of the time, using the IOT or cluster will speed up things because Oracle will not have to sort the results before returning.

Still, keep the ORDER BY, as Sean already said, or there are no guarantees.

I never consider the "default", I think that's a dangerous practice, like trusting default date formats.  :)

In my example, I picked an easy illustration

If my table is partitioned oracle may parallelize the query for me
and, even if I run the same query multiple times I'm not guaranteed to get the same results.

I just ran this again... select /*+ PARALLEL(wordlist, 4) */ word from wordlist
and got different results

Row#      WORD

1      C
2      CA
3      CAB
4      CABAL
5      CABANA
6      CABARET
7      CABBAGE
8      CABDRIVER
9      CABIN

so, it is obviously scanning in order through the index, but without ORDER BY to ensure the final results, the Parallel threads can return in effectively random order even though each will scan in "natural" or "default" order, the overall result isn't reliable without the explicit order by

as long as we're trying to make the most of default sorting.  An order by operation on already sorted data is almost free.

So, even if I was going to do

select * from my_index_organized_table

I would still add an order by.  

interestingly,  I just did an autotrace on select word from wordlist with and without order by

I was not expecting this...  NOT using an order by produced many, many more gets but did eliminate one sort.

That's really getting tangential though.  I'll investigate that more off line and start a separate discussion about what I find.

SQL> select word from wordlist ;
 
35369 rows selected.
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2594  consistent gets
          0  physical reads
          0  redo size
     553160  bytes sent via SQL*Net to client
      26300  bytes received via SQL*Net from client
       2359  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      35369  rows processed
 
SQL> select word from wordlist order by word;
 
35369 rows selected.
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        250  consistent gets
          0  physical reads
          0  redo size
     553160  bytes sent via SQL*Net to client
      26300  bytes received via SQL*Net from client
       2359  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      35369  rows processed
 
SQL>

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:

Once again my poor choice of words has made me appear to give bad advice.

The only guarantee is to use ORDER BY, and I never meant to imply that I recommended not to.

I should have said more than "OR use a cluster or IOT" because by saying what I said I was offering it as an alternative.

I meant to communicate that there are physical storage structures (indexes and clusters) that can pretty much guarantee the ordering of your data at the physical layer and the side effect is that the "implicit" behaviour will be more consistent. The other side effect is performance; you'll benefit from an index or cluster that coincides with the predominant ordering that you request, because in most cases, the rows will already be sorted, so Oracle will skip the sort step if the SQL "order by" clause coincides with the physical ordering.

Please read my suggestion as supplementary information, not as an alternative, as I shouldn't have worded it as such.

As an aside to this conversation, I think that, since rownum is a pseudocolumn of the result set of a query and not of the table, it is also unreliable, especially when where clauses and/or order by clauses are applied.

unfortunately, i cannot modify the query as this was a query i retrieved after debugging the application itself. so i cannot explicitly alter the order by portion.
therefore, i need to resovle at the database level.

i haven't tried yet, but if i were to delete the records and re-insert them in the 10g environment would it sort them like in the 8i environment. ?

I'm sorry but you can't modify anything in the db to make it guaranteed.

Your only guaranteed ordering is putting an order by on the query.

mrjoltcola is correct about creating physically ordered data.

BUT!!!
 as discussed above,  the physical ordering of the data is NOT indicative of the order the data will be returned to you from a query

I made an easy example above where I forced parallel query.  But even if I don't do that.  The physical order is still not guaranteed to be reflected in the output  of a query.

Using the same index organized table as before

select * from wordlist  

(note, I haven't done ANYTHING to alter the results or forced funny execution through hints)

Here's is an excerpt of the returned data....


Row#      WORD      PHONE_NUMBER

1      A      2
2      AAA      222
3      AARDVARK      22738275
4      AARHUS      227487
5      AARON      22766
6      ABA      222
7      ABABA      22222
8      ABACK      22225
9      ABACUS      222287
-----
-----   started out good but when I scrolled down further....
-----
975      FRIEDRICH      374337424
976      FRIEND      374363
977      FRIENDLESS      3743635377
978      D      3
979      D'ART      3278
980      D'ETAT      33828
981      D'ETRE      33873
982      D'OEUVRE      3638873
983      DAB      322

now some of  my D's came before my F's  

the plan even shows the query is executed via the index which is sorted.

Row#      PLAN_TABLE_OUTPUT

1      Plan hash value: 888072002
2       
3      ------------------------------------------------------------------------------------
4      | Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
5      ------------------------------------------------------------------------------------
6      |   0 | SELECT STATEMENT     |             | 35369 |   587K|    56   (0)| 00:00:01 |
7      |   1 |  INDEX FAST FULL SCAN| PK_WORDLIST | 35369 |   587K|    56   (0)| 00:00:01 |
8      ------------------------------------------------------------------------------------