Re: Bad performence using DESCENDING with index in ORACLE 7.3

A copy of this was sent to michael_bialik_at_my-deja.com (if that email address didn't require changing) On Thu, 23 Sep 1999 21:10:58 GMT, you wrote:

>Hi.
>
> The reverse pointers avaliable from Oracle 7.3, but
> as far as I know you have to use hint INDEX_DESC
> to enforce their usage by optimizer.
> In Oracle 8i there is a possibility to create
> descending indexes as well as ascending.
>

In 7.3 (and 8.x) you would be wanting to use the cost based optimizer to make use of this. As with all new optimizer features -- they are only available to be used with CBO (eg: bitmapped indexes, function based indexes, reading the index backwards and so on).

This shows that the RBO won't see the opportunity to read the index backwards:

tkyte_at_ORA734.US.ORACLE.COM> set autotrace on tkyte_at_ORA734.US.ORACLE.COM> select sal from emp where sal > 5 order by sal desc;

       SAL

      5000
  [...snip...]

       800

14 rows selected.

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 SORT (ORDER BY)
   2 1 INDEX (RANGE SCAN) OF 'SAL_IDX' (NON-UNIQUE) whereas the CBO picks it right up:

tkyte_at_ORA734.US.ORACLE.COM> analyze table emp compute statistics;

Table analyzed.

tkyte_at_ORA734.US.ORACLE.COM> select sal from emp where sal > 5 order by sal desc;

       SAL

      5000
  [...snip...]

       800

14 rows selected.

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=14 Bytes=364)    1 0 INDEX (RANGE SCAN DESCENDING) OF 'SAL_IDX' (NON-UNIQUE) (C           ost=1 Card=14 Bytes=364)

Hinting the query works (since that invokes the CBO) as well.

the above was done with 7.3.4 and no hints on solaris.

> Michael.
>
>In article <37eafe10.2230557_at_88.0.3.103>,
> sh_ya_at_yahoo.com (Shaya) wrote:
>> Hi All
>>
>> One of my customers is having performance problems trying to do a
>> SELECT with a ORDER BY ... DESCENDING on an indexed column.
>> I was once told that pirior to vertion 7.3 oracle had to read the
>> index to a work area, sort it in decending order and than fetch the
>> data, but from ver 7.3 this problem is solved, and the index had
>> backward pointers that enable it to be read in reverse order.
>> My customer said that when he asked, he was told that the problem is
>> solved only from version 8.0.
>>
>> Can anyone solve this mystry for me?
>> Is the reverse pointers available from version 7.3 or just from
>> version 8.0?
>>
>> Thanks
>>
>> Shaya
>>
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Sep 23 1999 - 16:56:03 CDT