Re: Do I need "ORDER BY"

Jonathan Lewis wrote in message
<936979590.23983.0.nnrp-07.9e984b29_at_news.demon.co.uk>...
>If you are running rule-based, yes.

I got different results on my 8.0.5 linux machine running a rule query on emp with an index on empno. It appears you don't need an order by on rule.

select /*+ rule */
 empno,ename from emp
where empno > 0

Execution Plan

   0 SELECT STATEMENT Optimizer=HINT: RULE    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'    2 1 INDEX (RANGE SCAN) OF 'EMP' (NON-UNIQUE)
>If you are running cost-based FIRST_ROWS, quite possibly.
>If you are running cost-based ALL_ROW, probably not.
>
>For cost-based optimisation it all depends on
>number of rows and packing of data according to
>that column etc.
>
>BTW - if you are after a character value that is
>smaller than all those in the column, then chr(0)
>is probably the best bet.
>
>
>--
>
>Jonathan Lewis
>Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
>Mark Schenkel wrote in message <37D92475.437F671B_at_alpinesoftware.com>...
>>Will the following two SQL Statements retrieve the same result sets if
>>there is a index on MKEY?:
>>
>>SELECT * FROM MYTABLE WHERE MKEY >= ' '
>>
>>SELECT * FROM MYTABLE WHERE MKEY >= ' ' ORDER BY MKEY
>>
>>My thinking is that for the first statement the server will use the
>>index because of the "WHERE" clause. It will then continue to use this
>>index when it returns the records from the server.
>
>
>
Received on Sat Sep 11 1999 - 09:02:05 CDT