Home » RDBMS Server » Performance Tuning » Analytical Functions and Backward Index scans
Analytical Functions and Backward Index scans [message #307386] Tue, 18 March 2008 13:43 Go to next message
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
Re: Analytical Functions and Backward Index scans [message #307387 is a reply to message #307386] Tue, 18 March 2008 13:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to give your version with 4 decimals.
Also post a test case we can reproduce.

In any case don't use SYS_OP_DESCEND function which is an internal one.

Regards
Michel
Re: Analytical Functions and Backward Index scans [message #307389 is a reply to message #307386] Tue, 18 March 2008 14:04 Go to previous messageGo to next message
verpies
Messages: 28
Registered: March 2008
Location: Seattle
Junior Member
The version I tried this on was 9i and 10g (eg.: 10.2.0.3.0), but any version supporting the analytics will do.

Are you sure a full test case is necessary for such trivial queries ?

Q1: Can you get "WINDOW NOSORT" in the query plan of Case A ?
Q2: Can you get "WINDOW NOSORT" in the query plan of Case B ?

If you do not observe a difference between these two queries and your answer for Q1 & Q2 is the same, I will gladly post a full test case.

Also, I do not have any choice about NOT USING the SYS_OP_DESCEND function in Case D.
Without it, the analytic Fn does not use any descending indices AT ALL !!! (neither forward nor backward...)


Thanks for the feedback,
George Robinson

[Updated on: Tue, 18 March 2008 14:22]

Report message to a moderator

Re: Analytical Functions and Backward Index scans [message #307391 is a reply to message #307389] Tue, 18 March 2008 14:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Are you sure a full test case is necessary for such trivial queries ?

Yes because I will not make the effort to build it just to help you. If you don't want to make this effort to help us help you, I don't see why I should do it.

Regards
Michel
Re: Analytical Functions and Backward Index scans [message #307392 is a reply to message #307386] Tue, 18 March 2008 14:43 Go to previous messageGo to next message
verpies
Messages: 28
Registered: March 2008
Location: Seattle
Junior Member
Hi,

It's not that I do not want to make the effort.
Just thought it is too trivial to list it all here.

Anyway here it goes:

SQL> DROP INDEX IDX_USE_ME;
Index dropped.

SQL> CREATE UNIQUE INDEX IDX_USE_ME ON CUSTOMER (SNAME ASC, ID ASC);
Index created.

SQL> exec DBMS_STATS.GATHER_INDEX_STATS (ownname => 'ORAUSER', indname => 'IDX_USE_ME');
PL/SQL procedure successfully completed.
Commit complete.
SQL> 
SQL> /* CASE A: THE ANALYTIC FN USES THE INDEX FORWARD*/
SQL> SELECT
  2      I, SNAME
  3  FROM     (  
  4            SELECT /*+ INDEX_ASC(IDX_USE_ME) FIRST_ROWS*/
  5               row_number() OVER (PARTITION BY SNAME ORDER BY SNAME ASC, ID ASC) I,
  6               SNAME
  7            FROM CUSTOMER
  8            ORDER BY SNAME ASC, ID ASC
  9           )
 10  WHERE rownum<=9;

         I SNAME
---------- ---------------------------------------------------------------------
         1 /1000e8d1_LinkedHashMapValueIt
         2 /1000e8d1_LinkedHashMapValueIt
         1 /1005bd30_LnkdConstant
         2 /1005bd30_LnkdConstant
         1 /10076b23_OraCustomDatumClosur
         2 /10076b23_OraCustomDatumClosur
         1 /100c1606_StandardMidiFileRead
         2 /100c1606_StandardMidiFileRead
         1 /1013c29d_PlanarImageServerPro

9 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3940838639

--------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |     9 |   495 |   671   (2)| 00:00:09
|*  1 |  COUNT STOPKEY     |            |       |       |            |
|   2 |   VIEW             |            | 49849 |  2677K|   671   (2)| 00:00:09
|   3 |    WINDOW NOSORT   |            | 49849 |  1363K|   671   (2)| 00:00:09
|   4 |     INDEX FULL SCAN| IDX_USE_ME | 49849 |  1363K|   274   (1)| 00:00:04
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=9)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        701  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          9  rows processed

SQL> 
SQL> 
SQL> /* CASE B: THE ANALYTIC FN DOES NOT USE THE INDEX BACKWARD*/
SQL> SELECT
  2      I, SNAME
  3  FROM     (  
  4            SELECT /*+ INDEX_DESC(IDX_USE_ME) FIRST_ROWS*/
  5               row_number() OVER (PARTITION BY SNAME ORDER BY SNAME DESC, ID DESC) I,
  6               SNAME
  7            FROM CUSTOMER
  8            ORDER BY SNAME DESC, ID DESC
  9           )
 10  WHERE rownum<=9;

         I SNAME
---------- ---------------------------------------------------------------------
         1 yCbCrSubSamplingType221_T
         1 yCbCrPositioningType220_T
         1 yCbCrCoefficientsType210_T
         1 xmpMetadataType100_T
         1 xml-extension-type31_T
         1 xdbconfig60_T
         1 xdb-log9_TAB$xd
         1 xdb-log9_TAB
         1 xdb-log7_T

9 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 733892884

--------------------------------------------------------------------------------
| Id  | Operation               | Name       | Rows  | Bytes |TempSpc| Cost (%CP
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |            |     9 |   495 |       |  1069   (
|*  1 |  COUNT STOPKEY          |            |       |       |       |
|   2 |   VIEW                  |            | 49849 |  2677K|       |  1069   (
|*  3 |    SORT ORDER BY STOPKEY|            | 49849 |  1363K|  3544K|  1069   (
|   4 |     WINDOW SORT         |            | 49849 |  1363K|  3544K|  1069   (
|   5 |      INDEX FULL SCAN    | IDX_USE_ME | 49849 |  1363K|       |   274   (
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=9)
   3 - filter(ROWNUM<=9)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        272  consistent gets
          0  physical reads
          0  redo size
        707  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          9  rows processed

SQL> 
SQL> 
SQL> 
SQL> /*  NOW LETS CREATE A DESCENDING INDEX FOR CASES C,D,E */
SQL> DROP INDEX IDX_USE_ME;

Index dropped.

SQL> CREATE UNIQUE INDEX IDX_USE_ME ON CUSTOMER (SNAME DESC, ID DESC);

Index created.

SQL> exec DBMS_STATS.GATHER_INDEX_STATS (ownname => 'ORAUSER', indname => 'IDX_USE_ME');

PL/SQL procedure successfully completed.

Commit complete.
SQL> 
SQL> /* CASE C: THE ANALYTIC FN DOES NOT USE THE INDEX FORWARD*/
SQL> SELECT
  2      I, SNAME
  3  FROM     (  
  4            SELECT /*+ INDEX_ASC(IDX_USE_ME) FIRST_ROWS*/
  5               row_number() OVER (PARTITION BY SNAME ORDER BY SNAME DESC, ID DESC) I,
  6               SNAME
  7            FROM CUSTOMER
  8            ORDER BY SNAME DESC, ID DESC
  9           ) a
 10  WHERE rownum<=9;

         I SNAME
---------- ---------------------------------------------------------------------
         1 yCbCrSubSamplingType221_T
         1 yCbCrPositioningType220_T
         1 yCbCrCoefficientsType210_T
         1 xmpMetadataType100_T
         1 xml-extension-type31_T
         1 xdbconfig60_T
         1 xdb-log9_TAB$xd
         1 xdb-log9_TAB
         1 xdb-log7_T

9 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 733892884

--------------------------------------------------------------------------------
| Id  | Operation               | Name       | Rows  | Bytes |TempSpc| Cost (%CP
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |            |     9 |   495 |       |  1083   (
|*  1 |  COUNT STOPKEY          |            |       |       |       |
|   2 |   VIEW                  |            | 49849 |  2677K|       |  1083   (
|*  3 |    SORT ORDER BY STOPKEY|            | 49849 |  1363K|  3544K|  1083   (
|   4 |     WINDOW SORT         |            | 49849 |  1363K|  3544K|  1083   (
|   5 |      INDEX FULL SCAN    | IDX_USE_ME | 49849 |  1363K|       |   288   (
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=9)
   3 - filter(ROWNUM<=9)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        286  consistent gets
          0  physical reads
          0  redo size
        707  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          9  rows processed

SQL> 
SQL> 
SQL> /* CASE D: THE ANALYTIC FN USES THE INDEX FORWARD AFTER COERCION WITH SYS_OP_DESCEND */
SQL> SELECT
  2      I, SNAME
  3  FROM     (  
  4            SELECT /*+ INDEX_ASC(IDX_USE_ME) FIRST_ROWS*/
  5               row_number() OVER (PARTITION BY SYS_OP_DESCEND(SNAME) ORDER BY SNAME DESC, ID DESC) I,
  6               SNAME
  7            FROM CUSTOMER
  8            ORDER BY SNAME DESC, ID DESC
  9           ) a
 10  WHERE rownum<=9;

         I SNAME
---------- ---------------------------------------------------------------------
         1 yCbCrSubSamplingType221_T
         1 yCbCrPositioningType220_T
         1 yCbCrCoefficientsType210_T
         1 xmpMetadataType100_T
         1 xml-extension-type31_T
         1 xdbconfig60_T
         1 xdb-log9_TAB$xd
         1 xdb-log9_TAB
         1 xdb-log7_T

9 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3940838639

--------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |     9 |   495 |  1083   (2)| 00:00:13
|*  1 |  COUNT STOPKEY     |            |       |       |            |
|   2 |   VIEW             |            | 49849 |  2677K|  1083   (2)| 00:00:13
|   3 |    WINDOW NOSORT   |            | 49849 |  1363K|  1083   (2)| 00:00:13
|   4 |     INDEX FULL SCAN| IDX_USE_ME | 49849 |  1363K|   288   (1)| 00:00:04
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=9)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        707  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          9  rows processed

SQL> 
SQL> 
SQL> /* CASE E: THE ANALYTIC FN DOES NOT USE THE INDEX BACKWARD */
SQL> SELECT
  2      I, SNAME
  3  FROM     (  
  4            SELECT /*+ INDEX_DESC(IDX_USE_ME) FIRST_ROWS*/
  5               row_number() OVER (PARTITION BY SNAME ORDER BY SNAME ASC, ID ASC) I,
  6               SNAME
  7            FROM CUSTOMER
  8            ORDER BY SNAME ASC, ID ASC
  9           ) a
 10  WHERE rownum<=9;

         I SNAME
---------- ---------------------------------------------------------------------
         1 /1000e8d1_LinkedHashMapValueIt
         2 /1000e8d1_LinkedHashMapValueIt
         1 /1005bd30_LnkdConstant
         2 /1005bd30_LnkdConstant
         1 /10076b23_OraCustomDatumClosur
         2 /10076b23_OraCustomDatumClosur
         1 /100c1606_StandardMidiFileRead
         2 /100c1606_StandardMidiFileRead
         1 /1013c29d_PlanarImageServerPro

9 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 733892884

--------------------------------------------------------------------------------
| Id  | Operation               | Name       | Rows  | Bytes |TempSpc| Cost (%CP
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |            |     9 |   495 |       |   685   (
|*  1 |  COUNT STOPKEY          |            |       |       |       |
|   2 |   VIEW                  |            | 49849 |  2677K|       |   685   (
|*  3 |    SORT ORDER BY STOPKEY|            | 49849 |  1363K|  3544K|   685   (
|   4 |     WINDOW SORT         |            | 49849 |  1363K|  3544K|   685   (
|   5 |      INDEX FULL SCAN    | IDX_USE_ME | 49849 |  1363K|       |   288   (
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=9)
   3 - filter(ROWNUM<=9)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        286  consistent gets
          0  physical reads
          0  redo size
        701  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          9  rows processed

SQL> 
Re: Analytical Functions and Backward Index scans [message #307393 is a reply to message #307392] Tue, 18 March 2008 14:48 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
What we are expecting is a script that we can copy and paste that includes create table and insert statements, so that we can easily reproduce the problem on our database and examine it.
Re: Analytical Functions and Backward Index scans [message #307394 is a reply to message #307386] Tue, 18 March 2008 14:50 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
Descending indexes are treated the same as function based indexes in oracle, so you need to analyze the index AND the table before running the query.

Does that do anything?
Re: Analytical Functions and Backward Index scans [message #307396 is a reply to message #307386] Tue, 18 March 2008 15:09 Go to previous messageGo to next message
verpies
Messages: 28
Registered: March 2008
Location: Seattle
Junior Member
Dear Barbara

Here is a script for creating the table and generating data for it from ALL_OBJECTS.

Analyzing the table and indices makes a difference for non-analytic functions (e.g.: the regular ORDER BY clause after the FROM clause).
However it makes no difference for the analytic function Sad

Please note that there are 2 problems.
1) Analytic Fn not using the index backwards in any case (neither ascending nor descending index)
2) Analytic Fn not using the descending index (neither forward nor backward), without coercion with SYS_OP_DESCEND (which helps only in forward direction).

See for yourself...

DROP TABLE ORAUSER.CUSTOMER;

CREATE TABLE ORAUSER.CUSTOMER
(
  ID               NUMBER(10),
  SNAME            VARCHAR2(80 BYTE)         NOT NULL,
  FNAME            VARCHAR2(50 BYTE),
  DOB              DATE
);



INSERT INTO CUSTOMER (ID, FNAME, SNAME, DOB)
SELECT rownum, CHR(mod(rownum,26)+64) || substr(to_char(TIMESTAMP),4,14), to_char(OBJECT_NAME), LAST_DDL_TIME
FROM ALL_OBJECTS;


exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'ORAUSER', tabname => 'CUSTOMER');

[Updated on: Tue, 18 March 2008 15:14]

Report message to a moderator

Re: Analytical Functions and Backward Index scans [message #307397 is a reply to message #307396] Tue, 18 March 2008 15:43 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
I am getting different results. The only one that does not appear to use the index is E. I don't know if this is due to differences in versions (I am using 11g) or data (in all_objects) or if we did something different. Did you remember to run dbms_stats.gather_table_stats again after you dropped and recreated the index? I have provided below first my results, followed by the script that produced it, so that you can copy and paste and run it to make sure that we are running the same thing and see if you still get the same results.

-- results:
SCOTT@orcl_11g> DROP TABLE CUSTOMER
  2  /

Table dropped.

SCOTT@orcl_11g> CREATE TABLE CUSTOMER
  2  (
  3    ID		NUMBER(10),
  4    SNAME		VARCHAR2(80 BYTE)	  NOT NULL,
  5    FNAME		VARCHAR2(50 BYTE),
  6    DOB		DATE
  7  )
  8  /

Table created.

SCOTT@orcl_11g> INSERT INTO CUSTOMER (ID, FNAME, SNAME, DOB)
  2  SELECT rownum, CHR(mod(rownum,26)+64) || substr(to_char(TIMESTAMP),4,14), to_char(OBJECT_NAME), LAST_DDL_TIME
  3  FROM ALL_OBJECTS
  4  /

68625 rows created.

SCOTT@orcl_11g> CREATE UNIQUE INDEX IDX_USE_ME ON CUSTOMER (SNAME ASC, ID ASC)
  2  /

Index created.

SCOTT@orcl_11g> exec DBMS_STATS.GATHER_TABLE_STATS (USER, 'CUSTOMER')

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> /* CASE A: THE ANALYTIC FN USES THE INDEX FORWARD*/
SCOTT@orcl_11g> SET AUTOTRACE ON EXPLAIN
SCOTT@orcl_11g> SELECT
  2  	 SNAME, I
  3  FROM     (
  4  	       SELECT /*+ INDEX_ASC(IDX_USE_ME) FIRST_ROWS*/
  5  		  row_number() OVER (PARTITION BY SNAME ORDER BY SNAME ASC, ID ASC) I,
  6  		  SNAME
  7  	       FROM CUSTOMER
  8  	       ORDER BY SNAME ASC, ID ASC
  9  	      )
 10  WHERE rownum<=22
 11  /

SNAME                                                                                     I
-------------------------------------------------------------------------------- ----------
/1000323d_DelegateInvocationHa                                                            1
/1000323d_DelegateInvocationHa                                                            2
/1000e8d1_LinkedHashMapValueIt                                                            1
/1000e8d1_LinkedHashMapValueIt                                                            2
/1005bd30_LnkdConstant                                                                    1
/1005bd30_LnkdConstant                                                                    2
/10076b23_OraCustomDatumClosur                                                            1
/10076b23_OraCustomDatumClosur                                                            2
/100c1606_StandardMidiFileRead                                                            1
/100c1606_StandardMidiFileRead                                                            2
/100cb3d7_DicomLocatorPathDico                                                            1
/100cb3d7_DicomLocatorPathDico                                                            2
/10128284_OpenMBeanAttributeIn                                                            1
/10128284_OpenMBeanAttributeIn                                                            2
/1013c29d_PlanarImageServerPro                                                            1
/1013c29d_PlanarImageServerPro                                                            2
/101419a4_NormalDataCollector                                                             1
/101419a4_NormalDataCollector                                                             2
/1020ed5e_Param                                                                           1
/1020ed5e_Param                                                                           2
/1023e902_OraCharsetUTFE                                                                  1
/1023e902_OraCharsetUTFE                                                                  2

22 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3940838639

---------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |    22 |  1210 |   379   (1)| 00:00:05 |
|*  1 |  COUNT STOPKEY     |            |       |       |            |          |
|   2 |   VIEW             |            | 68625 |  3685K|   379   (1)| 00:00:05 |
|   3 |    WINDOW NOSORT   |            | 68625 |  2010K|   379   (1)| 00:00:05 |
|   4 |     INDEX FULL SCAN| IDX_USE_ME | 68625 |  2010K|   379   (1)| 00:00:05 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=22)

SCOTT@orcl_11g> /* CASE B: THE ANALYTIC FN DOES NOT USE THE INDEX BACKWARD*/
SCOTT@orcl_11g> -- ON MY SYSTEM, IT DOES USE THE INDEX BACKWARD
SCOTT@orcl_11g> SELECT
  2  	 SNAME, I
  3  FROM     (
  4  	       SELECT /*+ INDEX_DESC(IDX_USE_ME) FIRST_ROWS*/
  5  		  row_number() OVER (PARTITION BY SNAME ORDER BY SNAME DESC, ID DESC) I,
  6  		  SNAME
  7  	       FROM CUSTOMER
  8  	       ORDER BY SNAME DESC, ID DESC
  9  	      )
 10  WHERE rownum<=22
 11  /

SNAME                                                                                     I
-------------------------------------------------------------------------------- ----------
yCbCrSubSamplingType295_T                                                                 1
yCbCrPositioningType294_T                                                                 1
yCbCrCoefficientsType284_T                                                                1
xmpMetadataType569_T                                                                      1
xdb-log13_TAB$xd                                                                          1
xdb-log13_TAB                                                                             1
xdb-log11_T                                                                               1
xdb-log-entry12_COLL                                                                      1
xdb-log-entry-type10_T                                                                    1
whitePointType287_T                                                                       1
whiteBalanceType279_T                                                                     1
uuidType272_T                                                                             1
timeType309_T                                                                             1
supplementalCategory564_COLL                                                              1
sun/util/calendar/ZoneInfoFile                                                            1
sun/util/calendar/ZoneInfoFile                                                            2
sun/util/calendar/ZoneInfo                                                                1
sun/util/calendar/ZoneInfo                                                                2
sun/util/calendar/Gregorian                                                               1
sun/util/calendar/Gregorian                                                               2
sun/util/calendar/Era                                                                     1
sun/util/calendar/Era                                                                     2

22 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1730477067

--------------------------------------------------------------------------------------------
| Id  | Operation                     | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |            |    22 |  1210 |   379   (1)| 00:00:05 |
|*  1 |  COUNT STOPKEY                |            |       |       |            |          |
|   2 |   VIEW                        |            | 68625 |  3685K|   379   (1)| 00:00:05 |
|   3 |    WINDOW NOSORT              |            | 68625 |  2010K|   379   (1)| 00:00:05 |
|   4 |     INDEX FULL SCAN DESCENDING| IDX_USE_ME | 68625 |  2010K|   379   (1)| 00:00:05 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=22)

SCOTT@orcl_11g> 
SCOTT@orcl_11g> /*  NOW LETS CREATE A DESCENDING INDEX FOR CASES C,D,E */
SCOTT@orcl_11g> DROP INDEX IDX_USE_ME;

Index dropped.

SCOTT@orcl_11g> CREATE UNIQUE INDEX IDX_USE_ME ON CUSTOMER (SNAME DESC, ID DESC)
  2  /

Index created.

SCOTT@orcl_11g> -- MUST ANALYZE AGAIN:
SCOTT@orcl_11g> EXEC DBMS_STATS.GATHER_TABLE_STATS (USER, 'CUSTOMER')

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> /* CASE C: THE ANALYTIC FN DOES NOT USE THE INDEX FORWARD*/
SCOTT@orcl_11g> -- ON MY SYSTEM IT DOES USE THE INDEX FORWARD
SCOTT@orcl_11g> SELECT
  2  	 SNAME, I
  3  FROM     (
  4  	       SELECT /*+ INDEX_ASC(IDX_USE_ME) FIRST_ROWS*/
  5  		  row_number() OVER (PARTITION BY SNAME ORDER BY SNAME DESC, ID DESC) I,
  6  		  SNAME
  7  	       FROM CUSTOMER
  8  	       ORDER BY SNAME DESC, ID DESC
  9  	      ) a
 10  WHERE rownum<=22
 11  /

SNAME                                                                                     I
-------------------------------------------------------------------------------- ----------
yCbCrSubSamplingType295_T                                                                 1
yCbCrPositioningType294_T                                                                 1
yCbCrCoefficientsType284_T                                                                1
xmpMetadataType569_T                                                                      1
xdb-log13_TAB$xd                                                                          1
xdb-log13_TAB                                                                             1
xdb-log11_T                                                                               1
xdb-log-entry12_COLL                                                                      1
xdb-log-entry-type10_T                                                                    1
whitePointType287_T                                                                       1
whiteBalanceType279_T                                                                     1
uuidType272_T                                                                             1
timeType309_T                                                                             1
supplementalCategory564_COLL                                                              1
sun/util/calendar/ZoneInfoFile                                                            1
sun/util/calendar/ZoneInfoFile                                                            2
sun/util/calendar/ZoneInfo                                                                1
sun/util/calendar/ZoneInfo                                                                2
sun/util/calendar/Gregorian                                                               1
sun/util/calendar/Gregorian                                                               2
sun/util/calendar/Era                                                                     1
sun/util/calendar/Era                                                                     2

22 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1492562620

---------------------------------------------------------------------------------------------
| Id  | Operation                      | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |            |    22 |  1210 | 33755   (1)| 00:06:46 |
|*  1 |  COUNT STOPKEY                 |            |       |       |            |          |
|   2 |   VIEW                         |            | 68625 |  3685K| 33755   (1)| 00:06:46 |
|   3 |    WINDOW NOSORT               |            | 68625 |  2010K| 33755   (1)| 00:06:46 |
|   4 |     TABLE ACCESS BY INDEX ROWID| CUSTOMER   | 68625 |  2010K| 33755   (1)| 00:06:46 |
|   5 |      INDEX FULL SCAN           | IDX_USE_ME | 68625 |       |   398   (1)| 00:00:05 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=22)

SCOTT@orcl_11g> /* CASE D: THE ANALYTIC FN USES THE INDEX FORWARD AFTER COERCION WITH SYS_OP_DESCEND */
SCOTT@orcl_11g> SELECT
  2  	 SNAME, I
  3  FROM     (
  4  	       SELECT /*+ INDEX_ASC(IDX_USE_ME) FIRST_ROWS*/
  5  		  row_number() OVER (PARTITION BY SYS_OP_DESCEND(SNAME) ORDER BY SNAME DESC, ID DESC) I,
  6  		  SNAME
  7  	       FROM CUSTOMER
  8  	       ORDER BY SNAME DESC, ID DESC
  9  	      ) a
 10  WHERE rownum<=22
 11  /

SNAME                                                                                     I
-------------------------------------------------------------------------------- ----------
yCbCrSubSamplingType295_T                                                                 1
yCbCrPositioningType294_T                                                                 1
yCbCrCoefficientsType284_T                                                                1
xmpMetadataType569_T                                                                      1
xdb-log13_TAB$xd                                                                          1
xdb-log13_TAB                                                                             1
xdb-log11_T                                                                               1
xdb-log-entry12_COLL                                                                      1
xdb-log-entry-type10_T                                                                    1
whitePointType287_T                                                                       1
whiteBalanceType279_T                                                                     1
uuidType272_T                                                                             1
timeType309_T                                                                             1
supplementalCategory564_COLL                                                              1
sun/util/calendar/ZoneInfoFile                                                            1
sun/util/calendar/ZoneInfoFile                                                            2
sun/util/calendar/ZoneInfo                                                                1
sun/util/calendar/ZoneInfo                                                                2
sun/util/calendar/Gregorian                                                               1
sun/util/calendar/Gregorian                                                               2
sun/util/calendar/Era                                                                     1
sun/util/calendar/Era                                                                     2

22 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3940838639

---------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |    22 |  1210 |  2276   (1)| 00:00:28 |
|*  1 |  COUNT STOPKEY     |            |       |       |            |          |
|   2 |   VIEW             |            | 68625 |  3685K|  2276   (1)| 00:00:28 |
|   3 |    WINDOW NOSORT   |            | 68625 |  3752K|  2276   (1)| 00:00:28 |
|   4 |     INDEX FULL SCAN| IDX_USE_ME | 68625 |  3752K|   398   (1)| 00:00:05 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=22)

SCOTT@orcl_11g> /* CASE E: THE ANALYTIC FN DOES NOT USE THE INDEX BACKWARD */
SCOTT@orcl_11g> -- THIS IS THE ONLY ONE THAT DOES NOT USE THE INDEX ON MY SYSTEM
SCOTT@orcl_11g> SELECT
  2  	 SNAME, I
  3  FROM     (
  4  	       SELECT /*+ INDEX_DESC(IDX_USE_ME) FIRST_ROWS*/
  5  		  row_number() OVER (PARTITION BY SNAME ORDER BY SNAME ASC, ID ASC) I,
  6  		  SNAME
  7  	       FROM CUSTOMER
  8  	       ORDER BY SNAME ASC, ID ASC
  9  	      ) a
 10  WHERE rownum<=22
 11  /

SNAME                                                                                     I
-------------------------------------------------------------------------------- ----------
/1000323d_DelegateInvocationHa                                                            1
/1000323d_DelegateInvocationHa                                                            2
/1000e8d1_LinkedHashMapValueIt                                                            1
/1000e8d1_LinkedHashMapValueIt                                                            2
/1005bd30_LnkdConstant                                                                    1
/1005bd30_LnkdConstant                                                                    2
/10076b23_OraCustomDatumClosur                                                            1
/10076b23_OraCustomDatumClosur                                                            2
/100c1606_StandardMidiFileRead                                                            1
/100c1606_StandardMidiFileRead                                                            2
/100cb3d7_DicomLocatorPathDico                                                            1
/100cb3d7_DicomLocatorPathDico                                                            2
/10128284_OpenMBeanAttributeIn                                                            1
/10128284_OpenMBeanAttributeIn                                                            2
/1013c29d_PlanarImageServerPro                                                            1
/1013c29d_PlanarImageServerPro                                                            2
/101419a4_NormalDataCollector                                                             1
/101419a4_NormalDataCollector                                                             2
/1020ed5e_Param                                                                           1
/1020ed5e_Param                                                                           2
/1023e902_OraCharsetUTFE                                                                  1
/1023e902_OraCharsetUTFE                                                                  2

22 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1603918779

---------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |    22 |  1210 |   171   (1)| 00:00:03 |
|*  1 |  COUNT STOPKEY       |          |       |       |            |          |
|   2 |   VIEW               |          | 68625 |  3685K|   171   (1)| 00:00:03 |
|   3 |    WINDOW SORT       |          | 68625 |  2010K|   171   (1)| 00:00:03 |
|   4 |     TABLE ACCESS FULL| CUSTOMER | 68625 |  2010K|   171   (1)| 00:00:03 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=22)

SCOTT@orcl_11g> SET AUTOTRACE OFF



-- script:
DROP TABLE CUSTOMER
/
CREATE TABLE CUSTOMER
(
  ID               NUMBER(10),
  SNAME            VARCHAR2(80 BYTE)         NOT NULL,
  FNAME            VARCHAR2(50 BYTE),
  DOB              DATE
) 
/
INSERT INTO CUSTOMER (ID, FNAME, SNAME, DOB)
SELECT rownum, CHR(mod(rownum,26)+64) || substr(to_char(TIMESTAMP),4,14), to_char(OBJECT_NAME), LAST_DDL_TIME
FROM ALL_OBJECTS
/
CREATE UNIQUE INDEX IDX_USE_ME ON CUSTOMER (SNAME ASC, ID ASC)
/
exec DBMS_STATS.GATHER_TABLE_STATS (USER, 'CUSTOMER') 
/* CASE A: THE ANALYTIC FN USES THE INDEX FORWARD*/
SET AUTOTRACE ON EXPLAIN
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*/
-- ON MY SYSTEM, IT DOES 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)
/
-- MUST ANALYZE AGAIN:
EXEC DBMS_STATS.GATHER_TABLE_STATS (USER, 'CUSTOMER')
/* CASE C: THE ANALYTIC FN DOES NOT USE THE INDEX FORWARD*/
-- ON MY SYSTEM IT DOES 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 */
-- THIS IS THE ONLY ONE THAT DOES NOT USE THE INDEX ON MY SYSTEM
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
/
SET AUTOTRACE OFF


Re: Analytical Functions and Backward Index scans [message #307398 is a reply to message #307394] Tue, 18 March 2008 15:45 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
coleing wrote on Tue, 18 March 2008 12:50
Descending indexes are treated the same as function based indexes in oracle, so you need to analyze the index AND the table before running the query.

Does that do anything?


Dbms_stats.gather_table_stats gathers table and index stats.
Re: Analytical Functions and Backward Index scans [message #307400 is a reply to message #307396] Tue, 18 March 2008 16:12 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
I see you have asked Tom Kyte, so I guess we can just wait for his response:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:10445280893164#720748100346204751

I noticed you have also asked on a dba forum and I assume other places. Tom Kyte usually gives excellent explanations and demonstrations.

Re: Analytical Functions and Backward Index scans [message #307402 is a reply to message #307400] Tue, 18 March 2008 16:30 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I was about to do the same.

Regards

Raj
Re: Analytical Functions and Backward Index scans [message #307700 is a reply to message #307386] Wed, 19 March 2008 09:56 Go to previous messageGo to next message
verpies
Messages: 28
Registered: March 2008
Location: Seattle
Junior Member
Dear All,

Yes, I asked in other places. I hope the members of this forum do not treat it as cross posting. After all, it's a different forum.

First of all, I'd like to share that the hint format in my test case WAS wrong. Very wrong!

It was /*+ INDEX_DESC(INDEX_NAME) */
...and it should be at least:
/*+ INDEX_DESC(TABLE_ALIAS INDEX_NAME) */

...and, should be at most:
http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10752/hintsref.htm#22275

Now, just to pardon myself: In the past, I was using the complex index hint syntax depicted at the URL above, but found out that it does not work when refeing to COLUMN NAMES in a descending index (Tom Kyte confirmed that).
Thus, I did a quick Search&Replace and messed up the table alias in the process. Sorry about that - to err is human...

Now, after correcting the hint syntax I get the same results as Barbara (one of the problems is gone Smile

The second problem is still there Sad
The CASE E still does not use the descending index backward.

I was able to coerce CASE E into good behavior with the SYS_OP_DESCEND function, though.

If anyone has an idea how to avoid using SYS_OP_DESCEND, please speak up.


Thanks for all your help guys,
George Robinson
Re: Analytical Functions and Backward Index scans [message #307711 is a reply to message #307700] Wed, 19 March 2008 11:36 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
There is no problem with posting on other forums and it is not considered cross-posting. Sometimes it helps to provide links so that we avoid duplicating what someone else has already done. We usually wait until we have exhausted all efforts ourselves or have disagreement amongst ourselves, before going to Tom Kyte. Once we get to the point, we usually just sit back and wait for the grand master of all Oracle gurus to respond and it appears that you have already gone to that point. However, apparently most Oracle employees are busy with the latest Oracle conference or whatever as they are noticably absent from the OTN forums and such, so it could be a while.

For the most part, the optimizer does a good job of selecting the best execution plan, but there are exceptions. Sometimes it is hard to tell whether what we are trying to force it to do is better than what it is doing, so it is hard to tell when to give hints and when not to and why it chooses to ignore them. Some little things like why it makes a difference if you provide the table name for a query that only selects from one table can be baffling. Here is my latest experiment for case E:

SCOTT@orcl_11g> CREATE UNIQUE INDEX IDX_USE_ME ON CUSTOMER (SNAME DESC, ID DESC)
  2  /

Index created.

SCOTT@orcl_11g> EXEC DBMS_STATS.GATHER_TABLE_STATS (USER, 'CUSTOMER')

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SELECT
  2  	 SNAME, I
  3  FROM     (
  4  	       SELECT /*+ INDEX_DESC(customer IDX_USE_ME) FIRST_ROWS*/
  5  		  row_number() OVER (PARTITION BY SNAME ORDER BY SNAME ASC, ID ASC) I,
  6  		  SNAME
  7  	       FROM CUSTOMER
  8  	       ORDER BY SNAME ASC, ID ASC
  9  	      ) a
 10  WHERE rownum<=22
 11  /

SNAME                                                                                     I
-------------------------------------------------------------------------------- ----------
/1000323d_DelegateInvocationHa                                                            1
/1000323d_DelegateInvocationHa                                                            2
/1000e8d1_LinkedHashMapValueIt                                                            1
/1000e8d1_LinkedHashMapValueIt                                                            2
/1005bd30_LnkdConstant                                                                    1
/1005bd30_LnkdConstant                                                                    2
/10076b23_OraCustomDatumClosur                                                            1
/10076b23_OraCustomDatumClosur                                                            2
/100c1606_StandardMidiFileRead                                                            1
/100c1606_StandardMidiFileRead                                                            2
/100cb3d7_DicomLocatorPathDico                                                            1
/100cb3d7_DicomLocatorPathDico                                                            2
/10128284_OpenMBeanAttributeIn                                                            1
/10128284_OpenMBeanAttributeIn                                                            2
/1013c29d_PlanarImageServerPro                                                            1
/1013c29d_PlanarImageServerPro                                                            2
/101419a4_NormalDataCollector                                                             1
/101419a4_NormalDataCollector                                                             2
/1020ed5e_Param                                                                           1
/1020ed5e_Param                                                                           2
/1023e902_OraCharsetUTFE                                                                  1
/1023e902_OraCharsetUTFE                                                                  2

22 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 494970170

----------------------------------------------------------------------------------------------
| Id  | Operation                       | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |            |    22 |  1210 | 33756   (1)| 00:06:46 |
|*  1 |  COUNT STOPKEY                  |            |       |       |            |          |
|   2 |   VIEW                          |            | 68627 |  3686K| 33756   (1)| 00:06:46 |
|*  3 |    SORT ORDER BY STOPKEY        |            | 68627 |  2010K| 33756   (1)| 00:06:46 |
|   4 |     WINDOW SORT                 |            | 68627 |  2010K| 33756   (1)| 00:06:46 |
|   5 |      TABLE ACCESS BY INDEX ROWID| CUSTOMER   | 68627 |  2010K| 33756   (1)| 00:06:46 |
|   6 |       INDEX FULL SCAN DESCENDING| IDX_USE_ME | 68627 |       |   398   (1)| 00:00:05 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=22)
   3 - filter(ROWNUM<=22)

SCOTT@orcl_11g>



[Updated on: Wed, 19 March 2008 11:47]

Report message to a moderator

Re: Analytical Functions and Backward Index scans [message #307719 is a reply to message #307386] Wed, 19 March 2008 12:07 Go to previous messageGo to next message
verpies
Messages: 28
Registered: March 2008
Location: Seattle
Junior Member
Barbara & All,

Thanks for your help.
I was fighting with this problem since November 2007 (see Tom's forum).
I got so exasperated with it that I asked for help in many places and decided to use the undocumented SYS_OP_DESCEND function.

Now, I need to test if it still works with the NLSSORT function inside. Like this:
SYS_OP_DESCEND( NLSSORT(columname) ).
NLSSORT goes inside the SYS_OP_DESCEND, right ?

Below is my latest experiment with Case E.
I sprinkled SYS_OP_DESCEND here and there and named it Case F.
Voila! Query plan shows WINDOW NOSORT

Regards,
George

P.S.
I have no idea where your TABLE ACCESS BY INDEX ROWID comes from...


SQL> DROP INDEX IDX_USE_ME;
Index dropped.

SQL> CREATE UNIQUE INDEX IDX_USE_ME ON CUSTOMER (SNAME ASC, ID ASC);
Index created.

SQL> exec DBMS_STATS.GATHER_INDEX_STATS (ownname => 'ORAUSER', indname => 'IDX_USE_ME');
PL/SQL procedure successfully completed.

SQL> 
SQL> /* CASE A: THE ANALYTIC FN USES THE ASCENDING INDEX FORWARD*/
SQL> SELECT
  2      I, SNAME
  3  FROM     (  
  4            SELECT /*+ INDEX_ASC(CUSTOMER IDX_USE_ME) FIRST_ROWS*/
  5               row_number() OVER (PARTITION BY SNAME ORDER BY SNAME ASC, ID ASC) I,
  6               SNAME
  7            FROM CUSTOMER
  8            ORDER BY SNAME ASC, ID ASC
  9           )
 10  WHERE rownum<=9;

         I SNAME
---------- --------------------------------------------------------------------------------
         1 /1000e8d1_LinkedHashMapValueIt
         2 /1000e8d1_LinkedHashMapValueIt
         1 /1005bd30_LnkdConstant
         2 /1005bd30_LnkdConstant
         1 /10076b23_OraCustomDatumClosur
         2 /10076b23_OraCustomDatumClosur
         1 /100c1606_StandardMidiFileRead
         2 /100c1606_StandardMidiFileRead
         1 /1013c29d_PlanarImageServerPro

9 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3940838639

---------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |     9 |   495 |   689   (2)| 00:00:09 |
|*  1 |  COUNT STOPKEY     |            |       |       |            |          |
|   2 |   VIEW             |            | 49849 |  2677K|   689   (2)| 00:00:09 |
|   3 |    WINDOW NOSORT   |            | 49849 |  1460K|   689   (2)| 00:00:09 |
|   4 |     INDEX FULL SCAN| IDX_USE_ME | 49849 |  1460K|   274   (1)| 00:00:04 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=9)

SQL> 
SQL> 
SQL> /* CASE B: THE ANALYTIC FN USES THE ASCENDING INDEX BACKWARD*/
SQL> SELECT
  2      I, SNAME
  3  FROM     (  
  4            SELECT /*+ INDEX_DESC(CUSTOMER IDX_USE_ME) FIRST_ROWS*/
  5               row_number() OVER (PARTITION BY SNAME ORDER BY SNAME DESC, ID DESC) I,
  6               SNAME
  7            FROM CUSTOMER
  8            ORDER BY SNAME DESC, ID DESC
  9           )
 10  WHERE rownum<=9;

         I SNAME
---------- --------------------------------------------------------------------------------
         1 yCbCrSubSamplingType221_T
         1 yCbCrPositioningType220_T
         1 yCbCrCoefficientsType210_T
         1 xmpMetadataType100_T
         1 xml-extension-type31_T
         1 xdbconfig60_T
         1 xdb-log9_TAB$xd
         1 xdb-log9_TAB
         1 xdb-log7_T

9 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1730477067

--------------------------------------------------------------------------------------------
| Id  | Operation                     | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |            |     9 |   495 |  1105   (2)| 00:00:14 |
|*  1 |  COUNT STOPKEY                |            |       |       |            |          |
|   2 |   VIEW                        |            | 49849 |  2677K|  1105   (2)| 00:00:14 |
|   3 |    WINDOW NOSORT              |            | 49849 |  1460K|  1105   (2)| 00:00:14 |
|   4 |     INDEX FULL SCAN DESCENDING| IDX_USE_ME | 49849 |  1460K|   274   (1)| 00:00:04 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=9)




SQL> /*  NOW LETS CREATE A DESCENDING INDEX FOR CASES C,D,E,F */
SQL> DROP INDEX IDX_USE_ME;
Index dropped.

SQL> CREATE UNIQUE INDEX IDX_USE_ME ON CUSTOMER (SNAME DESC, ID DESC);
Index created.

SQL> exec DBMS_STATS.GATHER_INDEX_STATS (ownname => 'ORAUSER', indname => 'IDX_USE_ME');
PL/SQL procedure successfully completed.



SQL> 
SQL> /* CASE C: THE ANALYTIC FN DOES NOT USE THE DESCENDING INDEX FORWARD*/
SQL> SELECT
  2      I, SNAME
  3  FROM     (  
  4            SELECT /*+ INDEX_ASC(CUSTOMER IDX_USE_ME) FIRST_ROWS*/
  5               row_number() OVER (PARTITION BY SNAME ORDER BY SNAME DESC, ID DESC) I,
  6               SNAME
  7            FROM CUSTOMER
  8            ORDER BY SNAME DESC, ID DESC
  9           ) a
 10  WHERE rownum<=9;

         I SNAME
---------- --------------------------------------------------------------------------------
         1 yCbCrSubSamplingType221_T
         1 yCbCrPositioningType220_T
         1 yCbCrCoefficientsType210_T
         1 xmpMetadataType100_T
         1 xml-extension-type31_T
         1 xdbconfig60_T
         1 xdb-log9_TAB$xd
         1 xdb-log9_TAB
         1 xdb-log7_T

9 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 733892884

----------------------------------------------------------------------------------------------
| Id  | Operation               | Name       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |            |     9 |   495 |       |  1119   (2)| 00:00:14 |
|*  1 |  COUNT STOPKEY          |            |       |       |       |            |          |
|   2 |   VIEW                  |            | 49849 |  2677K|       |  1119   (2)| 00:00:14 |
|*  3 |    SORT ORDER BY STOPKEY|            | 49849 |  1460K|  3928K|  1119   (2)| 00:00:14 |
|   4 |     WINDOW SORT         |            | 49849 |  1460K|  3928K|  1119   (2)| 00:00:14 |
|   5 |      INDEX FULL SCAN    | IDX_USE_ME | 49849 |  1460K|       |   288   (1)| 00:00:04 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=9)
   3 - filter(ROWNUM<=9)

SQL> 
SQL> 
SQL> /* CASE D: THE ANALYTIC FN USES THE DESCENDING INDEX FORWARD AFTER COERCION WITH SYS_OP_DESCEND */
SQL> SELECT
  2      I, SNAME
  3  FROM     (  
  4            SELECT /*+ INDEX_ASC(CUSTOMER IDX_USE_ME) FIRST_ROWS*/
  5               row_number() OVER (PARTITION BY SYS_OP_DESCEND(SNAME) ORDER BY SNAME DESC, ID DESC) I,
  6               SNAME
  7            FROM CUSTOMER
  8            ORDER BY SNAME DESC, ID DESC
  9           ) a
 10  WHERE rownum<=9;

         I SNAME
---------- --------------------------------------------------------------------------------
         1 yCbCrSubSamplingType221_T
         1 yCbCrPositioningType220_T
         1 yCbCrCoefficientsType210_T
         1 xmpMetadataType100_T
         1 xml-extension-type31_T
         1 xdbconfig60_T
         1 xdb-log9_TAB$xd
         1 xdb-log9_TAB
         1 xdb-log7_T

9 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3940838639

---------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |     9 |   495 |  1119   (2)| 00:00:14 |
|*  1 |  COUNT STOPKEY     |            |       |       |            |          |
|   2 |   VIEW             |            | 49849 |  2677K|  1119   (2)| 00:00:14 |
|   3 |    WINDOW NOSORT   |            | 49849 |  1460K|  1119   (2)| 00:00:14 |
|   4 |     INDEX FULL SCAN| IDX_USE_ME | 49849 |  1460K|   288   (1)| 00:00:04 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=9)

SQL> 
SQL> 
SQL> /* CASE E: THE ANALYTIC FN DOES NOT USE THE DESCENDING INDEX BACKWARD */
SQL> SELECT
  2      I, SNAME
  3  FROM     (  
  4            SELECT /*+ INDEX_DESC(CUSTOMER IDX_USE_ME) FIRST_ROWS*/
  5               row_number() OVER (PARTITION BY SNAME ORDER BY SNAME ASC, ID ASC) I,
  6               SNAME
  7            FROM CUSTOMER
  8            ORDER BY SNAME ASC, ID ASC
  9           ) a
 10  WHERE rownum<=9;

         I SNAME
---------- --------------------------------------------------------------------------------
         1 /1000e8d1_LinkedHashMapValueIt
         2 /1000e8d1_LinkedHashMapValueIt
         1 /1005bd30_LnkdConstant
         2 /1005bd30_LnkdConstant
         1 /10076b23_OraCustomDatumClosur
         2 /10076b23_OraCustomDatumClosur
         1 /100c1606_StandardMidiFileRead
         2 /100c1606_StandardMidiFileRead
         1 /1013c29d_PlanarImageServerPro

9 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2375959224

-----------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |            |     9 |   495 |       |   703   (2)| 00:00:09 |
|*  1 |  COUNT STOPKEY                 |            |       |       |       |            |          |
|   2 |   VIEW                         |            | 49849 |  2677K|       |   703   (2)| 00:00:09 |
|*  3 |    SORT ORDER BY STOPKEY       |            | 49849 |  1460K|  3928K|   703   (2)| 00:00:09 |
|   4 |     WINDOW SORT                |            | 49849 |  1460K|  3928K|   703   (2)| 00:00:09 |
|   5 |      INDEX FULL SCAN DESCENDING| IDX_USE_ME | 49849 |  1460K|       |   288   (1)| 00:00:04 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=9)
   3 - filter(ROWNUM<=9)

SQL> 
SQL> 
SQL> 
SQL> /* CASE F: THE ANALYTIC FN USES THE DESCENDING INDEX BACKWARD AFTER COERCION WITH SYS_OP_DESCEND */
SQL> SELECT
  2      I, SNAME
  3  FROM     (  
  4            SELECT /*+ INDEX_DESC(CUSTOMER IDX_USE_ME) FIRST_ROWS */
  5               row_number() OVER (PARTITION BY SYS_OP_DESCEND(SNAME) ORDER BY  SYS_OP_DESCEND(SNAME) DESC, SYS_OP_DESCEND(ID) DESC) I,
  6               SNAME
  7            FROM CUSTOMER
  8            ORDER BY SNAME ASC, ID ASC
  9           ) a
 10  WHERE rownum<=9;

         I SNAME
---------- --------------------------------------------------------------------------------
         1 /1000e8d1_LinkedHashMapValueIt
         2 /1000e8d1_LinkedHashMapValueIt
         1 /1005bd30_LnkdConstant
         2 /1005bd30_LnkdConstant
         1 /10076b23_OraCustomDatumClosur
         2 /10076b23_OraCustomDatumClosur
         1 /100c1606_StandardMidiFileRead
         2 /100c1606_StandardMidiFileRead
         1 /1013c29d_PlanarImageServerPro

9 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1730477067

--------------------------------------------------------------------------------------------
| Id  | Operation                     | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |            |     9 |   495 |  1119   (2)| 00:00:14 |
|*  1 |  COUNT STOPKEY                |            |       |       |            |          |
|   2 |   VIEW                        |            | 49849 |  2677K|  1119   (2)| 00:00:14 |
|   3 |    WINDOW NOSORT              |            | 49849 |  1460K|  1119   (2)| 00:00:14 |
|   4 |     INDEX FULL SCAN DESCENDING| IDX_USE_ME | 49849 |  1460K|   288   (1)| 00:00:04 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=9)

SQL>

[Updated on: Wed, 19 March 2008 13:34]

Report message to a moderator

Re: Analytical Functions and Backward Index scans [message #308007 is a reply to message #307386] Thu, 20 March 2008 14:50 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
Here is something interesting:-

Could it be the "partition by" clause causing all the grief?. partition by column name seems to always want to partition the data by having it in ascending order.

Turn it into a function index, and its different


SQL> DROP INDEX IDX_USE_ME;

Index dropped.

SQL> 
SQL> CREATE INDEX IDX_USE_ME ON CUSTOMER (SNAME, SNAME DESC, ID DESC);

Index created.

SQL> 
SQL> 
SQL> exec DBMS_STATS.GATHER_INDEX_STATS (ownname => 'SYSTEM', indname => 'IDX_USE_ME');

PL/SQL procedure successfully completed.

SQL> exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'SYSTEM', tabname => 'CUSTOMER');

PL/SQL procedure successfully completed.

SQL> 
SQL> /* CASE G WITH EXTRA COLUMN IN INDEX ASCENDING ORDER TO CATER FOR PARTITION BY */
SQL> SELECT
  2        I, SNAME
  3    FROM     (  
  4              SELECT /*+ INDEX_ASC(CUSTOMER IDX_USE_ME) FIRST_ROWS*/
  5                 row_number() OVER (PARTITION BY SNAME ORDER BY SNAME DESC, ID DESC) I,
  6                 SNAME
  7              FROM CUSTOMER
  8              ORDER BY SNAME ASC, ID ASC
  9             ) a
 10  WHERE rownum<=9;

         I SNAME
---------- --------------------------------------------------------------------------------
         2 /1000e8d1_LinkedHashMapValueIt
         1 /1000e8d1_LinkedHashMapValueIt
         2 /1005bd30_LnkdConstant
         1 /1005bd30_LnkdConstant
         2 /10076b23_OraCustomDatumClosur
         1 /10076b23_OraCustomDatumClosur
         2 /100c1606_StandardMidiFileRead
         1 /100c1606_StandardMidiFileRead
         2 /1013c29d_PlanarImageServerPro

9 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 733892884

----------------------------------------------------------------------------------------------
| Id  | Operation               | Name       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |            |     9 |   495 |       |  1358   (2)| 00:00:17 |
|*  1 |  COUNT STOPKEY          |            |       |       |       |            |          |
|   2 |   VIEW                  |            | 53160 |  2855K|       |  1358   (2)| 00:00:17 |
|*  3 |    SORT ORDER BY STOPKEY|            | 53160 |  1505K|  4184K|  1358   (2)| 00:00:17 |
|   4 |     WINDOW SORT         |            | 53160 |  1505K|  4184K|  1358   (2)| 00:00:17 |
|   5 |      INDEX FULL SCAN    | IDX_USE_ME | 53160 |  1505K|       |   493   (1)| 00:00:06 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=9)
   3 - filter(ROWNUM<=9)

SQL> 
SQL> 
SQL> 
SQL> 
SQL> DROP INDEX IDX_USE_ME;

Index dropped.

SQL> 
SQL> CREATE INDEX IDX_USE_ME ON CUSTOMER (UPPER(SNAME), SNAME DESC, ID DESC);

Index created.

SQL> 
SQL> 
SQL> exec DBMS_STATS.GATHER_INDEX_STATS (ownname => 'SYSTEM', indname => 'IDX_USE_ME');

PL/SQL procedure successfully completed.

SQL> exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'SYSTEM', tabname => 'CUSTOMER');

PL/SQL procedure successfully completed.

SQL> 
SQL> /* CASE H WITH EXTRA COLUMN IN INDEX ASCENDING ORDER TO CATER FOR PARTITION BY */
SQL> SELECT
  2        I, SNAME
  3    FROM     (  
  4              SELECT /*+ INDEX_ASC(CUSTOMER IDX_USE_ME) FIRST_ROWS*/
  5                 row_number() OVER (PARTITION BY UPPER(SNAME) ORDER BY SNAME DESC, ID DESC) I,
  6                 SNAME
  7              FROM CUSTOMER
  8              ORDER BY SNAME ASC, ID ASC
  9             ) a
 10  WHERE rownum<=9;

         I SNAME
---------- --------------------------------------------------------------------------------
         2 /1000e8d1_LinkedHashMapValueIt
         1 /1000e8d1_LinkedHashMapValueIt
         2 /1005bd30_LnkdConstant
         1 /1005bd30_LnkdConstant
         2 /10076b23_OraCustomDatumClosur
         1 /10076b23_OraCustomDatumClosur
         2 /100c1606_StandardMidiFileRead
         1 /100c1606_StandardMidiFileRead
         2 /1013c29d_PlanarImageServerPro

9 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2846590933

----------------------------------------------------------------------------------------------
| Id  | Operation               | Name       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |            |     9 |   495 |       |   925   (2)| 00:00:12 |
|*  1 |  COUNT STOPKEY          |            |       |       |       |            |          |
|   2 |   VIEW                  |            | 53160 |  2855K|       |   925   (2)| 00:00:12 |
|*  3 |    SORT ORDER BY STOPKEY|            | 53160 |  1505K|  4184K|   925   (2)| 00:00:12 |
|   4 |     WINDOW NOSORT       |            | 53160 |  1505K|       |   925   (2)| 00:00:12 |
|   5 |      INDEX FULL SCAN    | IDX_USE_ME | 53160 |  1505K|       |   493   (1)| 00:00:06 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=9)
   3 - filter(ROWNUM<=9)

SQL> 
SQL> 
SQL> 

[Updated on: Thu, 20 March 2008 14:54]

Report message to a moderator

Re: Analytical Functions and Backward Index scans [message #308020 is a reply to message #308007] Thu, 20 March 2008 15:48 Go to previous messageGo to next message
verpies
Messages: 28
Registered: March 2008
Location: Seattle
Junior Member
coleing,

It definitely is the PARTITION BY clause!
It just does not recognize descending columns in indexes. Aaaaarrrggggghhhh

I prefer the SYS_OP_DESCEND trick because it allows me to reuse an existing index and does not force me to create a new one.

Do you think the whole PARTITION BY DESC issue can be considered a bug ?


Regards,
George Robinson

[Updated on: Thu, 20 March 2008 15:49]

Report message to a moderator

Re: Analytical Functions and Backward Index scans [message #308035 is a reply to message #307386] Thu, 20 March 2008 16:54 Go to previous message
coleing
Messages: 213
Registered: February 2008
Senior Member
I would say its a bug. Oracle will probably say its an unimplemented feature.

But considering it works with a functional index, you would have to assume that its a bug not to do it with a standard one.
Previous Topic: Efficient Data Management.
Next Topic: ADDM Findings from OEM
Goto Forum:
  


Current Time: Thu Dec 08 16:41:37 CST 2016

Total time taken to generate the page: 0.07215 seconds