Bad performence using DESCENDING with index in ORACLE 7.3 
Author Message
 Bad performence using DESCENDING with index in ORACLE 7.3

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



Wed, 18 Jun 1902 08:00:00 GMT
 Bad performence using DESCENDING with index in ORACLE 7.3

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.

 Michael.



Quote:
> 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.


Wed, 18 Jun 1902 08:00:00 GMT
 Bad performence using DESCENDING with index in ORACLE 7.3

(if that email address didn't require changing)

Quote:

>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:



       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:


Table analyzed.


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.

Quote:
> Michael.



>> 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


Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation



Wed, 18 Jun 1902 08:00:00 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Oracle 7.3 and Forms 3.0 -- BAD, BAD, BAD!!!

2. Very bad performance when using the cache (Delphi C/S 3.0 + Oracle 7.3)

3. Oracle Bad Performence For Data Entry

4. Oracle Bad Performence for data entry

5. Using bitmap indexes on Oracle 7.3

6. 7.3 no longer using indexes for LIKE queries

7. Online Dynamic Server 7.3 : Index not used!

8. Oracle 7.3 - Displaying Japanese using Oracle Application Server (OAS)

9. using Oracle 8 and Oracle 7.3 together

10. Personal oracle 7.3 to Personal oracle 7.3 ( SQL*NET)

11. Oracle 7.3 bit map index problem


 
Powered by phpBB® Forum Software