Re: Where-clause

Hi Wolf,
There is a practical and theoretical approach to this. 1 practical
If you are using the Rule Based Optimizer, Oracle will use the index, it will perform a range scan, and your data will be ordered. If you are using the Cost Based Optimizer, the optimizer will most likely decide you are retrieving all records and not use the index. In this case the data will return unsorted, and you need to do an order by. It is very likely you will be better off NOT using the index, and using a full table scan followed by sort-merge, as this will use db_file_multiblock_count and return 8 or 16 blocks in one I/O. 2 theoretical.
Officially you should not rely on any implicit order of the data. Also, as SQL is a relational language, and any set is by design unordered, this shouldn't be an issue.

Bottom line: if ordering is really important, you shouldn't rely on the use of indexes, especially not when using cost based optimizer. In group by you should definitely NOT rely on any implicit ordering.

Hth,

--
Sybrand Bakker, Oracle DBA
Wolf Grossi <wg_at_REMOVE_ANTI_SPAMmagro-soft.com> wrote in message news:37f623cb.0_at_195.70.96.56...
> Hi folks,
> can anyone tell me if it is granted that the results of the below
> select, assuming that c01 is an ascending index, are in ascening order:
> SELECT * FROM TAB01 WHERE C01 >= ' ';
> Is there any hint in the ORACLE Docu (7 or 8); I could not find any hint.
> Thanks, Wolf
>
>
>
>
>
Received on Sat Oct 02 1999 - 15:40:06 CDT