OraFAQ Forum: Performance Tuning » Analytical Functions and Backward Index scans

Home » RDBMS Server » Performance Tuning » Analytical Functions and Backward Index scans

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Analytical Functions and Backward Index scans [message #307386]

Tue, 18 March 2008 13:43

verpies
Messages: 28
Registered: March 2008
Location: Seattle

Junior Member

I am having a problem that the analytical functions do not use the Oracle BTree indexes backward (although I thought that indexes are bidirectional in Oracle 10g, and long before that).

For example, in the queries below, the index is used by the analytical function only in cases A and D.
In other cases index is not used, as evidenced by the undesirable "WINDOW SORT" in the query plan.

Also, it is necessary to use the SYS_OP_DESCEND() function to coerce the RDBMS to use a descending index even in the forward direction for an analytical fn.

Summary:
-------------------
A) CAN use an ascending index FORWARD. (good Window NoSort in query plan)

B) CANNOT use an ascending index BACKWARD (bad Window Sort appears in query plan).

D) CAN use a descending index FORWARD only after coercion with SYS_OP_DESCEND function. (good Window NoSort in query plan)

E) CANNOT use a descending index BACKWARD. (bad Window Sort appears in query plan).

Do any one of you have any info what is going on inside RDBMS that would cause this ?

Regards,
George Robinson

DROP INDEX IDX_USE_ME;
CREATE UNIQUE INDEX IDX_USE_ME ON CUSTOMER (SNAME ASC, ID ASC);

/* CASE A: THE ANALYTIC FN USES THE INDEX FORWARD*/
SELECT
    SNAME, I
FROM     (  
          SELECT /*+ INDEX_ASC(IDX_USE_ME) FIRST_ROWS*/
             row_number() OVER (PARTITION BY SNAME ORDER BY SNAME ASC, ID ASC) I,
             SNAME
          FROM CUSTOMER
          ORDER BY SNAME ASC, ID ASC
         )
WHERE rownum<=22
 

/* CASE B: THE ANALYTIC FN DOES NOT USE THE INDEX BACKWARD*/
SELECT
    SNAME, I
FROM     (  
          SELECT /*+ INDEX_DESC(IDX_USE_ME) FIRST_ROWS*/
             row_number() OVER (PARTITION BY SNAME ORDER BY SNAME DESC, ID DESC) I,
             SNAME
          FROM CUSTOMER
          ORDER BY SNAME DESC, ID DESC
         )
WHERE rownum<=22
 
 
 
/*  NOW LETS CREATE A DESCENDING INDEX FOR CASES C,D,E */
DROP INDEX IDX_USE_ME;
CREATE UNIQUE INDEX IDX_USE_ME ON CUSTOMER (SNAME DESC, ID DESC);

/* CASE C: THE ANALYTIC FN DOES NOT USE THE INDEX FORWARD*/
SELECT
    SNAME, I
FROM     (  
          SELECT /*+ INDEX_ASC(IDX_USE_ME) FIRST_ROWS*/
             row_number() OVER (PARTITION BY SNAME ORDER BY SNAME DESC, ID DESC) I,
             SNAME
          FROM CUSTOMER
          ORDER BY SNAME DESC, ID DESC
         ) a
WHERE rownum<=22
 

/* CASE D: THE ANALYTIC FN USES THE INDEX FORWARD AFTER COERCION WITH SYS_OP_DESCEND */
SELECT
    SNAME, I
FROM     (  
          SELECT /*+ INDEX_ASC(IDX_USE_ME) FIRST_ROWS*/
             row_number() OVER (PARTITION BY SYS_OP_DESCEND(SNAME) ORDER BY SNAME DESC, ID DESC) I,
             SNAME
          FROM CUSTOMER
          ORDER BY SNAME DESC, ID DESC
         ) a
WHERE rownum<=22
 
 
/* CASE E: THE ANALYTIC FN DOES NOT USE THE INDEX BACKWARD */
SELECT
    SNAME, I
FROM     (  
          SELECT /*+ INDEX_DESC(IDX_USE_ME) FIRST_ROWS*/
             row_number() OVER (PARTITION BY SNAME ORDER BY SNAME ASC, ID ASC) I,
             SNAME
          FROM CUSTOMER
          ORDER BY SNAME ASC, ID ASC
         ) a
WHERE rownum<=22