 | |
#1
 
| |||
| |||
|
order by question -
10-23-2008
, 09:34 AM
#2
| |||
| |||
|
|
I have not been able to get a resultset which is unsorted acording to x.z but need to know that this would not happen in a productive environment. |
*unordered* collection *by design*, because that is mathematical
theory.
Ergo: Oracle does NOT guarantee any resultset is according to any
order, when there is no order by clause in the top level of the query.
It would be very silly to 'rely' on a specific ordering, and raises
suspicions your 'productive environment' is not so productive, as it
is processing a set as a bunch of records.
Which it shouldn't as that wouldn't scale.
--
Sybrand Bakker
Senior Oracle DBA
#3
| |||
| |||
|
|
I have not been able to get a resultset which is unsorted acording to x.z but need to know that this would not happen in a productive environment. |
*unordered* collection *by design*, because that is mathematical
theory.
Ergo: Oracle does NOT guarantee any resultset is according to any
order, when there is no order by clause in the top level of the query.
It would be very silly to 'rely' on a specific ordering, and raises
suspicions your 'productive environment' is not so productive, as it
is processing a set as a bunch of records.
Which it shouldn't as that wouldn't scale.
--
Sybrand Bakker
Senior Oracle DBA
#4
| |||
| |||
|
|
I have not been able to get a resultset which is unsorted acording to x.z but need to know that this would not happen in a productive environment. |
*unordered* collection *by design*, because that is mathematical
theory.
Ergo: Oracle does NOT guarantee any resultset is according to any
order, when there is no order by clause in the top level of the query.
It would be very silly to 'rely' on a specific ordering, and raises
suspicions your 'productive environment' is not so productive, as it
is processing a set as a bunch of records.
Which it shouldn't as that wouldn't scale.
--
Sybrand Bakker
Senior Oracle DBA
#5
| |||
| |||
|
|
I have not been able to get a resultset which is unsorted acording to x.z but need to know that this would not happen in a productive environment. |
*unordered* collection *by design*, because that is mathematical
theory.
Ergo: Oracle does NOT guarantee any resultset is according to any
order, when there is no order by clause in the top level of the query.
It would be very silly to 'rely' on a specific ordering, and raises
suspicions your 'productive environment' is not so productive, as it
is processing a set as a bunch of records.
Which it shouldn't as that wouldn't scale.
--
Sybrand Bakker
Senior Oracle DBA
#6
| |||
| |||
|
|
I wonder if following result set would be sorted according to x.z. Or is it possible that the optimizer for some reason, based on the outer conditions, decides to get the result unsorted because it would be more performant. select sub.* from (select x from y order by x.z) sub where ... and ... and ... and rownum < 10; If this works the (select x from y order by x.z ) would be a view. I have not been able to get a resultset which is unsorted acording to x.z but need to know that this would not happen in a productive environment. |
wouldn't rely on it.
I would add an additional "order by" at the end of the statement.
Of course there is reason to worry that there will be an additional sort
operation which would negatively affect performance, so I looked at
the explain plan for
SELECT *
FROM (SELECT vorname, nachname
FROM person
ORDER BY nachname) sub
WHERE rownum < 4
ORDER BY nachname
on my system, and I got:
------------------------------------------------------------------------------
Quote:
|
Id | Operation | Name |Rows |Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ 0 | SELECT STATEMENT | | 3 | 372 | 4 (25)| 00:00:01 | * 1 | COUNT STOPKEY | | | | | | 2 | VIEW | | 5 | 620 | 4 (25)| 00:00:01 | * 3 | SORT ORDER BY STOPKEY| | 5 | 80 | 4 (25)| 00:00:01 | 4 | TABLE ACCESS FULL | PERSON | 5 | 80 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------ |
So it seems that the optimizer is smart enough to remember that the rows
are already sorted and doesn't sort them again.
Yours,
Laurenz Albe
#7
| |||
| |||
|
|
I wonder if following result set would be sorted according to x.z. Or is it possible that the optimizer for some reason, based on the outer conditions, decides to get the result unsorted because it would be more performant. select sub.* from (select x from y order by x.z) sub where ... and ... and ... and rownum < 10; If this works the (select x from y order by x.z ) would be a view. I have not been able to get a resultset which is unsorted acording to x.z but need to know that this would not happen in a productive environment. |
wouldn't rely on it.
I would add an additional "order by" at the end of the statement.
Of course there is reason to worry that there will be an additional sort
operation which would negatively affect performance, so I looked at
the explain plan for
SELECT *
FROM (SELECT vorname, nachname
FROM person
ORDER BY nachname) sub
WHERE rownum < 4
ORDER BY nachname
on my system, and I got:
------------------------------------------------------------------------------
Quote:
|
Id | Operation | Name |Rows |Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ 0 | SELECT STATEMENT | | 3 | 372 | 4 (25)| 00:00:01 | * 1 | COUNT STOPKEY | | | | | | 2 | VIEW | | 5 | 620 | 4 (25)| 00:00:01 | * 3 | SORT ORDER BY STOPKEY| | 5 | 80 | 4 (25)| 00:00:01 | 4 | TABLE ACCESS FULL | PERSON | 5 | 80 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------ |
So it seems that the optimizer is smart enough to remember that the rows
are already sorted and doesn't sort them again.
Yours,
Laurenz Albe
#8
| |||
| |||
|
|
I wonder if following result set would be sorted according to x.z. Or is it possible that the optimizer for some reason, based on the outer conditions, decides to get the result unsorted because it would be more performant. select sub.* from (select x from y order by x.z) sub where ... and ... and ... and rownum < 10; If this works the (select x from y order by x.z ) would be a view. I have not been able to get a resultset which is unsorted acording to x.z but need to know that this would not happen in a productive environment. |
wouldn't rely on it.
I would add an additional "order by" at the end of the statement.
Of course there is reason to worry that there will be an additional sort
operation which would negatively affect performance, so I looked at
the explain plan for
SELECT *
FROM (SELECT vorname, nachname
FROM person
ORDER BY nachname) sub
WHERE rownum < 4
ORDER BY nachname
on my system, and I got:
------------------------------------------------------------------------------
Quote:
|
Id | Operation | Name |Rows |Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ 0 | SELECT STATEMENT | | 3 | 372 | 4 (25)| 00:00:01 | * 1 | COUNT STOPKEY | | | | | | 2 | VIEW | | 5 | 620 | 4 (25)| 00:00:01 | * 3 | SORT ORDER BY STOPKEY| | 5 | 80 | 4 (25)| 00:00:01 | 4 | TABLE ACCESS FULL | PERSON | 5 | 80 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------ |
So it seems that the optimizer is smart enough to remember that the rows
are already sorted and doesn't sort them again.
Yours,
Laurenz Albe
#9
| |||
| |||
|
|
I wonder if following result set would be sorted according to x.z. Or is it possible that the optimizer for some reason, based on the outer conditions, decides to get the result unsorted because it would be more performant. select sub.* from (select x from y order by x.z) sub where ... and ... and ... and rownum < 10; If this works the (select x from y order by x.z ) would be a view. I have not been able to get a resultset which is unsorted acording to x.z but need to know that this would not happen in a productive environment. |
wouldn't rely on it.
I would add an additional "order by" at the end of the statement.
Of course there is reason to worry that there will be an additional sort
operation which would negatively affect performance, so I looked at
the explain plan for
SELECT *
FROM (SELECT vorname, nachname
FROM person
ORDER BY nachname) sub
WHERE rownum < 4
ORDER BY nachname
on my system, and I got:
------------------------------------------------------------------------------
Quote:
|
Id | Operation | Name |Rows |Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ 0 | SELECT STATEMENT | | 3 | 372 | 4 (25)| 00:00:01 | * 1 | COUNT STOPKEY | | | | | | 2 | VIEW | | 5 | 620 | 4 (25)| 00:00:01 | * 3 | SORT ORDER BY STOPKEY| | 5 | 80 | 4 (25)| 00:00:01 | 4 | TABLE ACCESS FULL | PERSON | 5 | 80 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------ |
So it seems that the optimizer is smart enough to remember that the rows
are already sorted and doesn't sort them again.
Yours,
Laurenz Albe
#10
| |||
| |||
|
|
On Thu, 23 Oct 2008 07:34:51 -0700 (PDT), sbr... (AT) yahoo (DOT) com wrote: I have not been able to get a resultset which is unsorted acording to x.z but need to know that this would not happen in a productive environment. By definition all SQL prodiuces a set. The set is always an *unordered* collection *by design*, because that is mathematical theory. Ergo: Oracle does NOT guarantee any resultset is according to any order, when there is no order by clause in the top level of the query. It would be very silly to 'rely' on a specific ordering, and raises suspicions your 'productive environment' is not so productive, as it is processing a set as a bunch of records. Which it shouldn't as that wouldn't scale. -- Sybrand Bakker Senior Oracle DBA |
have an example of when the resultset is not ordered because I have
not been able to produce it. If yes then please say so. That would be
valuable information for me. I already know that "Oracle does NOT
guarantee any resultset is according to any order, when there is no
order by clause in the top level of the query". That is pretty much
basic stuff and this information is not valuable to me. And yes the
system is productive. The select is in a function which returns a
sys_refcursor to a java application. The select is based on tables
where several of them contains more than 10 million records and is
working well thus far. I do not see why it would not be scalable.
Slavko
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.