Re: Bad performence using DESCENDING with index in ORACLE 7.3

Thomas Kyte wrote:
>
> 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

Agreed, but no-one ever mentions the nasty bug that occurs in 7.3.3.x (x<5) where INDEX_DESC (hinted or not) can return rows that are not even specified by the where clause criteria...

Viva la 7.3.4 !

Cheers
C Received on Wed Sep 29 1999 - 09:16:17 CDT