Thanks, Cary.
Should have mentioned that the CBO has not always known _not_to_sort_ when
an index-access path is available - Oracle7/8.0 in particular.
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Thursday, June 26, 2003 4:29 PM
This is not so
When NOT Giving any Order by Clause , How is the Output of the SELECT Clause ordered
by Default ?
Assuming There exists a Unique index on the Table
Is some Rule followed ?
NOTE Records may have been INSERTED into the Table in some manner differing
from the Order of the data of the Unique
It depends on the access path and any other implicit sorting.
Access path - on a full table scan, the data is returned in the physical order it is
stored in the object. An index scan will return the data sorted according to the rule
of the index.
Implicit sorting - distinct will cause an
Vivek
If you want the data returned in an order, you can create an index with
the order you want, and in your query provide a hint for Oracle to use that
index. If your query is such that Oracle actually uses that index, the data
will be returned in that order. I work with a large application
Curious--if you can specify hints, why not just specify an ORDER BY clause?
Wouldn't that be more readable/maintainable/portable?
I don't know where I got it, but I had the impression that row order was
explicitly undefined (in one of the SQL standards?) when you don't do an
explicit ORDER BY. A
Vivek,
Oracle has always said that you cannot predict the order that records are
retrieved in.
If you create a new table and insert records, you can be pretty sure that
when you query them, they will come out in the same order.
*BUT* - once you delete a record and insert more new records, the
.
-Original Message-
From: VIVEK_SHARMA [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 25, 2003 9:33 AM
To: Multiple recipients of list ORACLE-L
Subject: SELECT Output Default Ordering ?
When NOT Giving any Order by Clause , How is the Output of
the SELECT Clause ordered
Vivek
If you want the data returned in an order, you can create an index with
the order you want, and in your query provide a hint for Oracle to use
that
index. If your query is such that Oracle actually uses that index, the
data
will be returned in that order. I work with a large
If you create a new table and insert records, you can be pretty sure that
when you query them, they will come out in the same order.
No, you can't. For example when parallel query is used, each slave reads
it's own set and the returning order isn't guaranteed.
The same with indexes, what if
Tanel
No ORDER BY. It upsets we DBAs, but some sites purchase Oracle and don't
hire a DBA. Especially smaller sites. Naturally Oracle doesn't want to make
it sound like they must have a DBA or the customer will probably buy MS SQL.
So as a vendor you must have your application run reasonably
Beware, though, that without explicit ORDER BY clauses, you're not
guaranteed to get the results in the order you expect (I think you mentioned
this yourself, for example, when an index is missing). It's not just a
performance problem. In some applications, you'll get the *wrong answer* if
you
In cases where the SQL is an important (ie frequently used) part of the
application, you do get significant gains with the index hint technique.
The ORDER BY is a guarantee - you have to pay your insurance premiums (ie
additional sort).
The index hint is a trade-off with the devil - you have to
]
co.nz cc:
Sent by: Subject: Re: SELECT Output Default
Ordering
This is not so much a response to Binley's comment as a general note on the
whole thread.
Not using ORDER BY when you need it is a huge risk... It doesn't matter what
hint you have, if someone drops the index, you're hosed. That's what the
ORDER BY is for. If you use the ORDER BY clause with the
14 matches
Mail list logo
