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 s
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 r
ORACLE-L <[EMAIL PROTECTED]>
co.nz> cc:
Sent by: Subject:
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 u
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 don'
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 wel
> 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 fa
> 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 appl
The official answer is, however the database feels like doing it at the time.
In practice, it depends on the access method. If an index is used the output may be
sorted by that index. Otherwise it might be by rowid. There are no guarantees and no
defaults. Relational data has no default order.
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 or
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
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 th
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 implici
13 matches
Mail list logo
