Re: Index Rebuilds

Hi,

Range Scan is used if the leading columns of the index are part of the equality predicate.
 Here is an example,

Some of the autotrace output is edited for brevity.

Example,

SQL> create index REV_DEMO_INDEX
  2 on EMP(EMPNO,HIREDATE)
  3 REVERSE; Index created.

SQL> set autotrace on

SQL> analyze index REV_DEMO_INDEX compute statistics;

SQL> select ename,empno,sal from emp where empno = 7369;

ENAME                   EMPNO                SAL
---------- ------------------ ------------------
SMITH                    7369               1000

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=18)    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=3 Card=1 Bytes=18)    2 1 INDEX (RANGE SCAN) OF 'REV_DEMO_INDEX' (NON-UNIQUE) (Cost=2 Card=1)

 SQL> select * from emp where empno > 1000

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=13 Card=10015 Bytes=440660)

   1 0 TABLE ACCESS (FULL) OF 'EMP' (Cost=13 Card=10015 Bytes=440660)

I would expect the same to work with partitioned tables too...not tested yet.

Cheers
anand

On 05/08/06, Christian Antognini <Christian.Antognini_at_trivadis.com> wrote:
>
> Stalin
>
> > Yes, regular coalesce after rebuild would solve the problem, but is
> > there a permanent solution for these ever growing index other than
> > regular coalesce.
>
> The only option I see is to use a reversed index. If I correctly
> understand what your application is doing it could lead to a very nice
> reutilization of the space in the leaf blocks. But, of course, range
> scans on the index will no more be available.
>
>
> HTH
> Chris
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l