Tom,

I notice when I create an index of the form...

create index my_index on tablename (col1, col2 desc);

that oracle creates the index as a function based index. Does this mean that I cannot use descending indexes in Oracle 8.1.7 standard edition [since FBI's are not enabled], or are these a special case [I notice we do not need query_rewrite enabled to use them]?

Thanks for your confirmation

Hi Tom,


Can I create an IOT in a DESC order? Because I think
by nature they are in ASC order. Just curious.

SQL> sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Oct 24 14:45:50 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production With the Partitioning, OLAP and Data Mining options

SQL> set lin 200 trim on
SQL> set autotrace on
SQL> SELECT count(1)
  2  FROM scott.emp emp
  3  INNER JOIN scott.dept dept ON emp.deptno = dept.deptno
  4  WHERE dept.deptno IN (10,30);

  COUNT(1)
----------
         9


Execution Plan
----------------------------------------------------------
Plan hash value: 4223522633

-------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |     1 |     6 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE     |         |     1 |     6 |            |          |
|   2 |   NESTED LOOPS      |         |     6 |    36 |     3   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| EMP     |     9 |    27 |     3   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN| PK_DEPT |     1 |     3 |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   3 - filter("EMP"."DEPTNO"=10 OR "EMP"."DEPTNO"=30)
   4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
       filter("DEPT"."DEPTNO"=10 OR "DEPT"."DEPTNO"=30)


Statistics
----------------------------------------------------------
        194  recursive calls
          0  db block gets
         44  consistent gets
          0  physical reads
          0  redo size
        515  bytes sent via SQL*Net to client
        491  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> create index scott.X_DEPT_DEPTNO_DESC on scott.DEPT (DEPTNO DESC)
  2  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  3  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  4  TABLESPACE "USERS" ;

Index created.

SQL> SELECT count(1)
  2  FROM scott.emp emp
  3  INNER JOIN scott.dept dept ON emp.deptno = dept.deptno
  4  WHERE dept.deptno IN (10,30);

  COUNT(1)
----------
        28


Execution Plan
----------------------------------------------------------
Plan hash value: 902546181

--------------------------------------------------------------------------------------------
| Id  | Operation             | Name               | Rows  | Bytes | Cost (%CPU)
| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                    |     1 |     6 |     5   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |                    |     1 |     6 |  |          |
|   2 |   MERGE JOIN CARTESIAN|                    |    28 |   168 |     5   (0)| 00:00:01 |
|*  3 |    INDEX FULL SCAN    | X_DEPT_DEPTNO_DESC |     2 |     6 |     1   (0)| 00:00:01 |
|   4 |    BUFFER SORT        |                    |    14 |    42 |     4   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL | EMP                |    14 |    42 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

   3 - filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("DEPTNO"))=10 OR
              SYS_OP_UNDESCEND(SYS_OP_DESCEND("DEPTNO"))=30)


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

SQL> drop index scott.X_DEPT_DEPTNO_DESC;

Index dropped.

SQL> SELECT count(1)
  2  FROM scott.emp emp
  3  INNER JOIN scott.dept dept ON emp.deptno = dept.deptno
  4  WHERE dept.deptno IN (10,30);

  COUNT(1)
----------
         9


Execution Plan
----------------------------------------------------------
Plan hash value: 4223522633

-------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |     1 |     6 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE     |         |     1 |     6 |            |          |
|   2 |   NESTED LOOPS      |         |     6 |    36 |     3   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| EMP     |     9 |    27 |     3   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN| PK_DEPT |     1 |     3 |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   3 - filter("EMP"."DEPTNO"=10 OR "EMP"."DEPTNO"=30)
   4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
       filter("DEPT"."DEPTNO"=10 OR "DEPT"."DEPTNO"=30)


Statistics
----------------------------------------------------------
        194  recursive calls
          0  db block gets
         44  consistent gets
          0  physical reads
          0  redo size
        515  bytes sent via SQL*Net to client
        491  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
          1  rows processed

Is this a bug ?

I noticed that hints for descending indexes in 10g are not recognized unless Oracle's internal name of the descending column is used.

For example:

CREATE INDEX ora_user.idx_fname_sname_id ON ora_user.customer (fname, sname, id)

CREATE UNIQUE INDEX ora_user.idx_fname_sname_D_id ON ora_user.customer (fname, "sname" DESC, id)

*/ This creates a composite FBI because one of the columns in it is descending. The "id" column is unique and non-null */



/*
The query below does not use idx_fname_sname_D_id, despite the hint, it wrongly uses idx_fname_sname_id instead (note the descending ordering on "sname" in the ORDER BY clauses):
*/

SELECT
i,fname,sname
FROM (
SELECT /*+ INDEX(customer customer(fname,sname,id)) FIRST_ROWS */
row_number() OVER (PARTITION BY fname,sname ORDER BY fname,sname DESC ,id) i,
fname,
sname
FROM customer
WHERE fname>='ZIGGY' AND sname<='PIGGY'
ORDER BY fname,sname DESC ,id
) a
WHERE rownum<=10

/*
However if I substitute the internal column name SYS_NC00029$ in place of "sname" appearing in the query above, then the idx_fname_sname_D_id is used and the performance of the query below performs an order of magnitude better.
*/

SELECT
i,fname,sname
FROM (
SELECT /*+ INDEX(customer customer(fname,SYS_NC00029$,id)) FIRST_ROWS */
row_number() OVER (PARTITION BY fname,SYS_NC00029$ ORDER BY fname,sname DESC ,id) i,
fname,
sname
FROM customer
WHERE fname>='ZIGGY' AND SYS_NC00029$<='PIGGY'
ORDER BY fname,sname DESC ,id
) a
WHERE rownum<=10


Most likely I am lost in the woods, but experimenting has gotten me there.


Regards,
George

P.S.
The internal column name SYS_NC00029$ is specific to my FBI index. On your system you can find it in the system table: user_ind_columns.column_name

Tom,

What would you like for a "full test case" of this?

But, first let's get the issue of quotation marks out of the way.
This is what I do. It matters not if the descending column name is enclosed in quotes:


DROP INDEX ora_user.IDX_FNAME_SNAME_D;

CREATE UNIQUE INDEX ora_user.IDX_FNAME_SNAME_D ON ora_user.CUSTOMER(FNAME, SNAME DESC, ID);

SELECT * FROM user_ind_columns WHERE table_name = 'CUSTOMER';


INDEX_NAME TABLE_NAME COLUMN_NAME COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESCEND
--------------------------------------------------------------------------------------
IDX_CARD_NUM CUSTOMER CARD_NUM 1 8 8 ASC
IDX_FNAME_SNAME_ID_DOB CUSTOMER SNAME 2 80 80 ASC
IDX_FNAME_SNAME_ID_DOB CUSTOMER ID 3 22 0 ASC
IDX_SSN CUSTOMER SSN 1 11 11 ASC
IDX_FNAME_SNAME_ID_DOB CUSTOMER DOB 4 7 0 ASC
IDX_FNAME_SNAME_ID_DOB CUSTOMER FNAME 1 50 50 ASC
IDX_FNAME_ID_SNAME_DOB CUSTOMER ID 2 22 0 ASC
IDX_FNAME_ID_SNAME_DOB CUSTOMER SNAME 3 80 80 ASC
IDX_FNAME_SNAME_D CUSTOMER FNAME 1 50 50 ASC
IDX_FNAME_SNAME_D CUSTOMER SYS_NC00029$ 2 121 0 DESC
IDX_FNAME_SNAME_D CUSTOMER ID 3 22 0 ASC
IDX_FNAME_SNAME_DOB_ID CUSTOMER FNAME 1 50 50 ASC
IDX_FNAME_SNAME_DOB_ID CUSTOMER SNAME 2 80 80 ASC
IDX_FNAME_SNAME_DOB_ID CUSTOMER DOB 3 7 0 ASC
IDX_FNAME_SNAME_DOB_ID CUSTOMER ID 4 22 0 ASC
IDX_FNAME_ID_SNAME_DOB CUSTOMER DOB 4 7 0 ASC
IDX_FNAME_ID_SNAME_DOB CUSTOMER FNAME 1 50 50 ASC
PK_CUSTOMER CUSTOMER ID 1 22 0 ASC

Also the table Customer has the following columns:
ID NUMBER(10) NOT NULL,
CARD_NUM VARCHAR2(8 BYTE),
SNAME VARCHAR2(80 BYTE) NOT NULL,
FNAME VARCHAR2(50 BYTE),
DOB DATE,
SSN NUMBER(9)


Regards,
George

Tom,

Indeed using the index name in the INDEX hint works.
However, the method of identyfying the index by its columns would be more immune to surprises (e.g renaming an index).


I still have a problem with query performance when using descending sorting order.

CREATE TABLE ora_user.customer
(
  id               NUMBER(10),
  sname            VARCHAR2(80 BYTE)         NOT NULL,
  fname            VARCHAR2(50 BYTE),
  dob              DATE,
)


SQL> CREATE INDEX ora_user.idx_fname_sname_id ON ora_user.customer(fname, sname, id);
Index created.

SQL> CREATE UNIQUE INDEX ora_user.idx_fname_sname_D_id ON ora_user.customer(fname, sname DESC, id);
Index created.

SQL> CREATE INDEX ora_user.idx_fname_sname_dob_id ON ora_user.customer(fname, sname, dob, id);
Index created.

SQL> analyze table customer
  2  compute statistics
  3  for table
  4  for all indexes
  5  for all indexed columns;

Table analyzed.


SQL> SELECT
  2      fname,sname,dob, i
  3  FROM    (  
  4           SELECT /*+ INDEX(customer idx_fname_sname_id) FIRST_ROWS */
  5              row_number() OVER (PARTITION BY fname,sname ORDER BY fname,sname,id) i,
  6              fname,
  7              sname,
  8              dob
  9           FROM customer
 10           WHERE fname>='ANNA' AND (fname>'ANNA' OR sname>='Banana') 
 11           ORDER BY fname,sname,id
 12          ) a
 13  WHERE rownum<=5 AND (a.i>=3 OR fname>'ANNA' OR sname>'Banana'); 

fname      sname              dob          I
---------- ------------------ --------- ----
ANNA       Banana             05-MAY-69    3
ANNA       Banana             06-JUN-69    4
ANNA       Banana             08-AUG-69    5
ANNA       Banasiak           03-APR-81    1
ANNA       Banasiak           30-DEC-99    2


Execution Plan
----------------------------------------------------------
Plan hash value: 2707518446

---------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name              | Rows  | Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                   |     5 |   455 |   386K  (1)| 01:17:23|
|*  1 |  COUNT STOPKEY                 |                   |       |       |            |         |
|*  2 |   VIEW                         |                   |   382K|    33M|   386K  (1)| 01:17:23|
|   3 |    WINDOW NOSORT               |                   |   382K|    10M|   386K  (1)| 01:17:23|
|   4 |     TABLE ACCESS BY INDEX ROWID| customer          |   382K|    10M|   383K  (1)| 01:16:46|
|*  5 |      INDEX RANGE SCAN          | idx_fname_sname_id|   382K|       |  1808   (2)| 00:00:22|
---------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=5)
   2 - filter("A"."I">=3 OR "fname">'ANNA' OR "sname">'Banana')
   5 - access("fname">='ANNA' AND "fname" IS NOT NULL)
       filter("sname">='Banana' AND SYS_OP_DESCEND("sname")<=HEXTORAW('BD9E919E919EFF')  OR "fname">'ANNA')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         10  consistent gets
          4  physical reads
          0  redo size
        697  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)
          5  rows processed

/********* Everything is fine here. The correct index is being used and WINDOW NOSORT with COUNT STOPKEY makes it snappy *********/






/********* Now I will change the sorting order on one column by making the ORDER BY 'sname' - DESCending,
and modyfying the hint to use a composite index with exactly the same sorting order as in the ORDER BY clause,
and changing the direction of WHERE predicates accordingly *********/

SQL> SELECT
  2      fname,sname,dob, i
  3  FROM    (  
  4           SELECT /*+ INDEX(customer idx_fname_sname_D_id) FIRST_ROWS */
  5              row_number() OVER (PARTITION BY fname,sname ORDER BY fname,sname DESC,id) i,
  6              fname,
  7              sname,
  8              dob
  9           FROM customer
 10           WHERE fname>='ANNA' AND (fname>'ANNA' OR sname<='Banana')
 11           ORDER BY fname,sname DESC,id
 12          ) a
 13  WHERE rownum<=5 AND (a.i>=3 OR fname>'ANNA' OR sname<'Banana');


fname      sname              dob          I
---------- ------------------ --------- ----
ANNA       Banana             05-MAY-69    3
ANNA       Banana             06-JUN-69    4
ANNA       Banana             08-AUG-69    5
ANNA       Banaczek           13-AUG-85    1
ANNA       Banach             01-MAR-40    1


Execution Plan
----------------------------------------------------------
Plan hash value: 2241884980

------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                        |     5 |   455 |  9865   (2)| 00:01:59 |
|*  1 |  COUNT STOPKEY        |                        |       |       |            |          |
|*  2 |   VIEW                |                        |   367K|    31M|  9865   (2)| 00:01:59 |
|   3 |    SORT ORDER BY      |                        |   367K|     9M|  9865   (2)| 00:01:59 |
|   4 |     WINDOW SORT       |                        |   367K|     9M|  9865   (2)| 00:01:59 |
|   5 |      CONCATENATION    |                        |       |       |            |          |
|*  6 |       INDEX RANGE SCAN| idx_fname_sname_dob_id | 10233 |   279K|  2237   (1)| 00:00:27 |
|*  7 |       INDEX RANGE SCAN| idx_fname_sname_dob_id |   357K|  9766K|  2142   (1)| 00:00:26 |
------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=5)
   2 - filter("A"."I">=3 OR "fname">'ANNA' OR "sname"<'Banana')
   6 - access("fname">='ANNA' AND "fname" IS NOT NULL)
       filter("sname"<='Banana')
   7 - access("fname">'ANNA' AND "fname" IS NOT NULL)
       filter("fname">='ANNA' AND (LNNVL("sname"<='Banana') OR LNNVL(SYS_OP_DESCEND("sname")>=HEXTORAW('BD9E919E919EFF') )))


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       4323  consistent gets
       2204  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)
          5  rows processed

/********* We only changed the sorting direction on one column 'sname'
and now an unexpected index is being used in the query above
and SORT ORDER BY and WINDOW SORT appears
and the query takes 10x longer than with the non-descending 'sname'.
Arrrgggggghhh! Why? *********/


/********* Let's delete an irrelevant index that does not even match the ORDER BY clause ***********/

SQL> DROP INDEX ora_user.idx_fname_sname_dob_id;
Index dropped.

/********* ...and let's execute the same query again ***********/

SQL> SELECT
  2      fname,sname,dob, i
  3  FROM    (  
  4           SELECT /*+ INDEX(customer idx_fname_sname_D_id) FIRST_ROWS */
  5              row_number() OVER (PARTITION BY fname,sname ORDER BY fname,sname DESC,id) i,
  6              fname,
  7              sname,
  8              dob
  9           FROM customer
 10           WHERE fname>='ANNA' AND (fname>'ANNA' OR sname<='Banana')
 11           ORDER BY fname,sname DESC,id
 12          ) a
 13  WHERE rownum<=5 AND (a.i>=3 OR fname>'ANNA' OR sname<'Banana');

fname      sname              dob          I
---------- ------------------ --------- ----
ANNA       Banana             05-MAY-69    3
ANNA       Banana             06-JUN-69    4
ANNA       Banana             08-AUG-69    5
ANNA       Banaczek           13-AUG-85    1
ANNA       Banach             01-MAR-40    1


Execution Plan
----------------------------------------------------------
Plan hash value: 1801208678

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                 | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                      |     5 |   455 |       |   343K  (1)| 01:08:39 |
|*  1 |  COUNT STOPKEY                  |                      |       |       |       |            |          |
|*  2 |   VIEW                          |                      |   336K|    29M|       |   343K  (1)| 01:08:39 |
|   3 |    SORT ORDER BY                |                      |   336K|  9199K|    25M|   343K  (1)| 01:08:39 |
|   4 |     WINDOW SORT                 |                      |   336K|  9199K|    25M|   343K  (1)| 01:08:39 |
|   5 |      TABLE ACCESS BY INDEX ROWID| customer             |   336K|  9199K|       |   337K  (1)| 01:07:35 |
|*  6 |       INDEX RANGE SCAN          | idx_fname_sname_D_id |   336K|       |       |  1808   (2)| 00:00:22 |
----------------------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=5)
   2 - filter("A"."I">=3 OR "fname">'ANNA' OR "sname"<'Banana')
   6 - access("fname">='ANNA' AND "fname" IS NOT NULL)
       filter("fname">'ANNA' OR SYS_OP_UNDESCEND(SYS_OP_DESCEND("sname"))<='Banana' AND SYS_OP_DESCEND("sname")>=HEXTORAW('BD9E919E919EFF') )


Statistics
----------------------------------------------------------
        425  recursive calls
          0  db block gets
     367606  consistent gets
       1777  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
         13  sorts (memory)
          0  sorts (disk)
          5  rows processed

/********* The only difference here is that we dropped an irrelevant index, and now the expected index IS being used,
but we still have inefficient SORT ORDER BY and WINDOW SORT and the query takes 10x longer than with the non-descending 'sname'.
Why? *********/


Regards,
George

Tom,

On my system 10.2.0.3.0 and SQL*Plus of the same version, I can type the column name in double quotes or without them during CREATE INDEX, and it succeeds in either case.

Toad 9.1.0.62 also accepts double quotes around column names.

Tom wrote:
"...CREATE UNIQUE INDEX ora_user.idx_fname_sname_D_id ON ora_user.customer (fname, "sname" DESC, id) will *fail*, there is no column "sname" in there..."


Regards,
George

ops$tkyte%ORA10GR2> create table t ( id number, sname varchar2(30) );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create index t_idx on t( id, "sname" DESC );
create index t_idx on t( id, "sname" DESC )
                             *
ERROR at line 1:
ORA-00904: "sname": invalid identifier


ops$tkyte%ORA10GR2> alter table t add "sname" varchar2(30);

Table altered.

ops$tkyte%ORA10GR2> create index t_idx on t( id, "sname" DESC );

Index created.

ops$tkyte%ORA10GR2> desc t
 Name                                 Null?    Type
 ------------------------------------ -------- -------------------------
 ID                                            NUMBER
 SNAME                                         VARCHAR2(30)
 sname                                         VARCHAR2(30)

Maybe the previous commenter had used uppercase for the quoted column name in the query or index creation. "SNAME" is the same as SNAME.

David,

Excellent suggestion!

Indeed I was using sometimes uppercase and sometimes lowercase, and did not expect that:

"SNAME" == SNAME
"sname" <> sname

weird...

Thanks a lot,
George

Tom,

Per David's suggestion, I changed all of the column names to uppercase (table definition and SQL)

I still have the same problem with query performance when using descending sorting order on one of the columns.

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


SQL> CREATE INDEX ORA_USER.IDX_FNAME_SNAME_ID ON ORA_USER.CUSTOMER(FNAME, SNAME, ID);
Index created.

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

SQL> CREATE INDEX ORA_USER.IDX_FNAME_SNAME_DOB_ID ON ORA_USER.CUSTOMER(FNAME, SNAME, DOB, ID);
Index created.

SQL> analyze table CUSTOMER
  2  compute statistics
  3  for table
  4  for all indexes
  5  for all indexed columns;

Table analyzed.


SQL> SELECT
  2      FNAME,SNAME,DOB, I
  3  FROM    (  
  4           SELECT /*+ INDEX(CUSTOMER IDX_FNAME_SNAME_ID) FIRST_ROWS */
  5              row_number() OVER (PARTITION BY FNAME,SNAME ORDER BY FNAME,SNAME,ID) I,
  6              FNAME,
  7              SNAME,
  8              DOB
  9           FROM CUSTOMER
 10           WHERE FNAME>='ANNA' AND (FNAME>'ANNA' OR SNAME>='Banana') 
 11           ORDER BY FNAME,SNAME,ID
 12          ) a
 13  WHERE rownum<=5 AND (a.I>=3 OR FNAME>'ANNA' OR SNAME>'Banana'); 

FNAME      SNAME              DOB          I
---------- ------------------ --------- ----
ANNA       Banana             05-MAY-69    3
ANNA       Banana             06-JUN-69    4
ANNA       Banana             08-AUG-69    5
ANNA       Banasiak           03-APR-81    1
ANNA       Banasiak           30-DEC-99    2


Execution Plan
----------------------------------------------------------
Plan hash value: 2707518446

---------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name              | Rows  | Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                   |     5 |   455 |   386K  (1)| 01:17:23|
|*  1 |  COUNT STOPKEY                 |                   |       |       |            |         |
|*  2 |   VIEW                         |                   |   382K|    33M|   386K  (1)| 01:17:23|
|   3 |    WINDOW NOSORT               |                   |   382K|    10M|   386K  (1)| 01:17:23|
|   4 |     TABLE ACCESS BY INDEX ROWID| CUSTOMER          |   382K|    10M|   383K  (1)| 01:16:46|
|*  5 |      INDEX RANGE SCAN          | IDX_FNAME_SNAME_ID|   382K|       |  1808   (2)| 00:00:22|
---------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=5)
   2 - filter("A"."I">=3 OR "FNAME">'ANNA' OR "SNAME">'Banana')
   5 - access("FNAME">='ANNA' AND "FNAME" IS NOT NULL)
       filter("SNAME">='Banana' AND SYS_OP_DESCEND("SNAME")<=HEXTORAW('BD9E919E919EFF')  OR 
"FNAME">'ANNA')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         10  consistent gets
          4  physical reads
          0  redo size
        697  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)
          5  rows processed

/********* Everything is fine here. The correct index is being used and WINDOW NOSORT with COUNT STOPKEY makes it snappy *********/






/********* Now I will change the sorting order on one column by making the ORDER BY 'SNAME' - DESCending,
and modyfying the hint to use a composite index with exactly the same sorting order as in the ORDER BY clause,
and changing the direction of WHERE predicates accordingly *********/

SQL> SELECT
  2      FNAME,SNAME,DOB, I
  3  FROM    (  
  4           SELECT /*+ INDEX(CUSTOMER IDX_FNAME_SNAME_D_ID) FIRST_ROWS */
  5              row_number() OVER (PARTITION BY FNAME,SNAME ORDER BY FNAME,SNAME DESC,ID) I,
  6              FNAME,
  7              SNAME,
  8              DOB
  9           FROM CUSTOMER
 10           WHERE FNAME>='ANNA' AND (FNAME>'ANNA' OR SNAME<='Banana')
 11           ORDER BY FNAME,SNAME DESC,ID
 12          ) a
 13  WHERE rownum<=5 AND (a.I>=3 OR FNAME>'ANNA' OR SNAME<'Banana');


FNAME      SNAME              DOB          I
---------- ------------------ --------- ----
ANNA       Banana             05-MAY-69    3
ANNA       Banana             06-JUN-69    4
ANNA       Banana             08-AUG-69    5
ANNA       Banaczek           13-AUG-85    1
ANNA       Banach             01-MAR-40    1


Execution Plan
----------------------------------------------------------
Plan hash value: 2241884980

------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                        |     5 |   455 |  9865   (2)| 00:01:59 |
|*  1 |  COUNT STOPKEY        |                        |       |       |            |          |
|*  2 |   VIEW                |                        |   367K|    31M|  9865   (2)| 00:01:59 |
|   3 |    SORT ORDER BY      |                        |   367K|     9M|  9865   (2)| 00:01:59 |
|   4 |     WINDOW SORT       |                        |   367K|     9M|  9865   (2)| 00:01:59 |
|   5 |      CONCATENATION    |                        |       |       |            |          |
|*  6 |       INDEX RANGE SCAN| IDX_FNAME_SNAME_DOB_ID | 10233 |   279K|  2237   (1)| 00:00:27 |
|*  7 |       INDEX RANGE SCAN| IDX_FNAME_SNAME_DOB_ID |   357K|  9766K|  2142   (1)| 00:00:26 |
------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=5)
   2 - filter("A"."I">=3 OR "FNAME">'ANNA' OR "SNAME"<'Banana')
   6 - access("FNAME">='ANNA' AND "FNAME" IS NOT NULL)
       filter("SNAME"<='Banana')
   7 - access("FNAME">'ANNA' AND "FNAME" IS NOT NULL)
       filter("FNAME">='ANNA' AND (LNNVL("SNAME"<='Banana') OR 
LNNVL(SYS_OP_DESCEND("SNAME")>=HEXTORAW('BD9E919E919EFF') )))


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       4323  consistent gets
       2204  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)
          5  rows processed

/********* We only changed the sorting direction on one column 'SNAME'
and now an unexpected index is being used in the query above
and SORT ORDER BY and WINDOW SORT appears
and the query takes 10x longer than with the non-descending 'SNAME'.
Arrrgggggghhh! Why? *********/


/********* Let's delete an irrelevant index that does not even match the ORDER BY clause ***********/

SQL> DROP INDEX ORA_USER.IDX_FNAME_SNAME_DOB_ID;
Index dropped.

/********* ...and let's execute the same query again ***********/

SQL> SELECT
  2      FNAME,SNAME,DOB, I
  3  FROM    (  
  4           SELECT /*+ INDEX(CUSTOMER IDX_FNAME_SNAME_D_ID) FIRST_ROWS */
  5              row_number() OVER (PARTITION BY FNAME,SNAME ORDER BY FNAME,SNAME DESC,ID) I,
  6              FNAME,
  7              SNAME,
  8              DOB
  9           FROM CUSTOMER
 10           WHERE FNAME>='ANNA' AND (FNAME>'ANNA' OR SNAME<='Banana')
 11           ORDER BY FNAME,SNAME DESC,ID
 12          ) a
 13  WHERE rownum<=5 AND (a.I>=3 OR FNAME>'ANNA' OR SNAME<'Banana');

FNAME      SNAME              DOB          I
---------- ------------------ --------- ----
ANNA       Banana             05-MAY-69    3
ANNA       Banana             06-JUN-69    4
ANNA       Banana             08-AUG-69    5
ANNA       Banaczek           13-AUG-85    1
ANNA       Banach             01-MAR-40    1


Execution Plan
----------------------------------------------------------
Plan hash value: 1801208678

----------------------------------------------------------------------------------------------------
------------
| Id  | Operation                       | Name                 | Rows  | Bytes |TempSpc| Cost 
(%CPU)| Time     |
----------------------------------------------------------------------------------------------------
------------
|   0 | SELECT STATEMENT                |                      |     5 |   455 |       |   343K  
(1)| 01:08:39 |
|*  1 |  COUNT STOPKEY                  |                      |       |       |       |            
|          |
|*  2 |   VIEW                          |                      |   336K|    29M|       |   343K  
(1)| 01:08:39 |
|   3 |    SORT ORDER BY                |                      |   336K|  9199K|    25M|   343K  
(1)| 01:08:39 |
|   4 |     WINDOW SORT                 |                      |   336K|  9199K|    25M|   343K  
(1)| 01:08:39 |
|   5 |      TABLE ACCESS BY INDEX ROWID| CUSTOMER             |   336K|  9199K|       |   337K  
(1)| 01:07:35 |
|*  6 |       INDEX RANGE SCAN          | IDX_FNAME_SNAME_D_ID |   336K|       |       |  1808   
(2)| 00:00:22 |
----------------------------------------------------------------------------------------------------
------------

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

   1 - filter(ROWNUM<=5)
   2 - filter("A"."I">=3 OR "FNAME">'ANNA' OR "SNAME"<'Banana')
   6 - access("FNAME">='ANNA' AND "FNAME" IS NOT NULL)
       filter("FNAME">'ANNA' OR SYS_OP_UNDESCEND(SYS_OP_DESCEND("SNAME"))<='Banana' AND 
SYS_OP_DESCEND("SNAME")>=HEXTORAW('BD9E919E919EFF') )


Statistics
----------------------------------------------------------
        425  recursive calls
          0  db block gets
     367606  consistent gets
       1777  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
         13  sorts (memory)
          0  sorts (disk)
          5  rows processed

/********* The only difference here is that we dropped an index that wasn not even matching the ORDER BY clause, but it had a positive effect and now the expected index IS being used,
but we still have inefficient SORT ORDER BY and WINDOW SORT and the query takes 10x longer than with the non-descending 'SNAME'.
Why? *********/


Regards,
George

Tom,

So far using DBMS_STATS.GATHER_TABLE_STATS did not have an influence on the performance of the query with one descending column.

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

I will try to use all_objects for you as soon as I figure out how.
Don't the contents of all_objects vary from system to system ?


Regards,
George

Tom,

Here is the test case, using data generated from ALL_OBJECTS.

SQL> alter session set nls_date_format='YYYY.MM.DD';
Session altered.

SQL> CREATE TABLE ORAUSER.CUSTOMER
  2  (
  3    ID               NUMBER(10),
  4    SNAME            VARCHAR2(80 BYTE)         NOT NULL,
  5    FNAME            VARCHAR2(50 BYTE),
  6    DOB              DATE
  7  );

Table created.

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

49839 rows created.



SQL> INSERT INTO CUSTOMER (ID, FNAME, SNAME, DOB) VALUES (77769,'ANNA','Agana','1969.05.05');
1 row created.

SQL> INSERT INTO CUSTOMER (ID, FNAME, SNAME, DOB) VALUES (77770,'ANNA','Arcana','1969.05.05');
1 row created.

SQL> INSERT INTO CUSTOMER (ID, FNAME, SNAME, DOB) VALUES (77771,'ANNA','Banana','1969.05.05');
1 row created.

SQL> INSERT INTO CUSTOMER (ID, FNAME, SNAME, DOB) VALUES (77772,'ANNA','Banana','1969.06.06');
1 row created.

SQL> INSERT INTO CUSTOMER (ID, FNAME, SNAME, DOB) VALUES (77773,'ANNA','Banana','1969.07.07');
1 row created.

SQL> INSERT INTO CUSTOMER (ID, FNAME, SNAME, DOB) VALUES (77774,'ANNA','Banana','1969.08.08');
1 row created.

SQL> INSERT INTO CUSTOMER (ID, FNAME, SNAME, DOB) VALUES (77775,'ANNA','Banana','1969.09.09');
1 row created.

SQL> INSERT INTO CUSTOMER (ID, FNAME, SNAME, DOB) VALUES (77776,'ANNA','Cabana','1969.09.09');
1 row created.

SQL> INSERT INTO CUSTOMER (ID, FNAME, SNAME, DOB) VALUES (77777,'ANNA','Nirvana','1969.09.09');
1 row created.

SQL> INSERT INTO CUSTOMER (ID, FNAME, SNAME, DOB) VALUES (77778,'ANNA','Marijuana','1969.09.09');
1 row created.

SQL> commit;
Commit complete.

SQL> CREATE INDEX ORAUSER.IDX_FNAME_SNAME_ID ON ORAUSER.CUSTOMER(FNAME, SNAME, ID);
Index created.

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

SQL> CREATE INDEX ORAUSER.IDX_FNAME_SNAME_DOB_ID ON ORAUSER.CUSTOMER(FNAME, SNAME, DOB, ID);
Index created.

SQL> exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'ORAUSER', tabname => 'CUSTOMER');
PL/SQL procedure successfully completed.

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

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

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

SQL> Select count(distinct FNAME) from CUSTOMER;

COUNT(DISTINCTFNAME)
--------------------
                1887


SQL> Select count(distinct SNAME) from CUSTOMER;

COUNT(DISTINCTSNAME)
--------------------
               29581


SQL> Select count(distinct DOB) from CUSTOMER;

COUNT(DISTINCTDOB)
------------------
              2333


SQL> set autotrace on;


SQL> SELECT
  2      FNAME,SNAME,DOB, I
  3  FROM    (  
  4            SELECT /*+ INDEX(CUSTOMER IDX_FNAME_SNAME_ID) FIRST_ROWS */
  5               row_number() OVER (PARTITION BY FNAME,SNAME ORDER BY FNAME,SNAME,ID) I,
  6               FNAME,
  7               SNAME,
  8               DOB
  9            FROM CUSTOMER
 10            WHERE FNAME>='ANNA' AND (FNAME>'ANNA' OR SNAME>='Banana') 
 11            ORDER BY FNAME,SNAME,ID
 12           ) a
 13   WHERE rownum<=5 AND (a.I>=3 OR FNAME>'ANNA' OR SNAME>'Banana');

FNAME                                              SNAME                                                                            DOB                 I
-------------------------------------------------- -------------------------------------------------------------------------------- ---------- ----------
ANNA                                               Banana                                                                           1969.07.07          3
ANNA                                               Banana                                                                           1969.08.08          4
ANNA                                               Banana                                                                           1969.09.09          5
ANNA                                               Cabana                                                                           1969.09.09          1
ANNA                                               Marijuana                                                                        1969.09.09          1


Execution Plan
----------------------------------------------------------
Plan hash value: 2795605592

-----------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                    |     5 |   455 | 39184   (1)| 00:07:51 |
|*  1 |  COUNT STOPKEY                 |                    |       |       |            |          |
|*  2 |   VIEW                         |                    | 47125 |  4187K| 39184   (1)| 00:07:51 |
|   3 |    WINDOW NOSORT               |                    | 47125 |  2485K| 39184   (1)| 00:07:51 |
|   4 |     TABLE ACCESS BY INDEX ROWID| CUSTOMER           | 47125 |  2485K| 38552   (1)| 00:07:43 |
|*  5 |      INDEX RANGE SCAN          | IDX_FNAME_SNAME_ID | 47125 |       |   379   (1)| 00:00:05 |
-----------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=5)
   2 - filter("A"."I">=3 OR "FNAME">'ANNA' OR "SNAME">'Banana')
   5 - access("FNAME">='ANNA' AND "FNAME" IS NOT NULL)
       filter("FNAME">'ANNA' OR "SNAME">='Banana' AND
              SYS_OP_DESCEND("SNAME")<=HEXTORAW('BD9E919E919EFF') )


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
        680  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)
          5  rows processed

/********* Everything is fine above. The correct index is being used and WINDOW NOSORT with COUNT STOPKEY makes it snappy *********/





/********* Now I will change the sorting order on one column by making the ORDER BY 'SNAME' - DESCending,
and modyfying the hint to use a composite index with exactly the same sorting order as appearing in the ORDER BY clause,
and changing the direction of WHERE predicates accordingly *********/

SQL>  SELECT
  2      FNAME,SNAME,DOB, I
  3   FROM    (  
  4            SELECT /*+ INDEX(CUSTOMER IDX_FNAME_SNAME_D_ID) FIRST_ROWS */
  5               row_number() OVER (PARTITION BY FNAME,SNAME ORDER BY FNAME,SNAME DESC,ID) I,
  6               FNAME,
  7               SNAME,
  8               DOB
  9            FROM CUSTOMER
 10            WHERE FNAME>='ANNA' AND (FNAME>'ANNA' OR SNAME<='Banana')
 11            ORDER BY FNAME,SNAME DESC,ID
 12           ) a
 13   WHERE rownum<=5 AND (a.I>=3 OR FNAME>'ANNA' OR SNAME<'Banana');

FNAME                                              SNAME                                                                            DOB                 I
-------------------------------------------------- -------------------------------------------------------------------------------- ---------- ----------
ANNA                                               Banana                                                                           1969.07.07          3
ANNA                                               Banana                                                                           1969.08.08          4
ANNA                                               Banana                                                                           1969.09.09          5
ANNA                                               Arcana                                                                           1969.05.05          1
ANNA                                               Agana                                                                            1969.05.05          1


Execution Plan
----------------------------------------------------------
Plan hash value: 910787172

------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                        |     5 |   455 |  1958   (2)| 00:00:24 |
|*  1 |  COUNT STOPKEY        |                        |       |       |            |          |
|*  2 |   VIEW                |                        | 47648 |  4234K|  1958   (2)| 00:00:24 |
|   3 |    SORT ORDER BY      |                        | 47648 |  2512K|  1958   (2)| 00:00:24 |
|   4 |     WINDOW SORT       |                        | 47648 |  2512K|  1958   (2)| 00:00:24 |
|   5 |      CONCATENATION    |                        |       |       |            |          |
|*  6 |       INDEX RANGE SCAN| IDX_FNAME_SNAME_DOB_ID | 12500 |   659K|   432   (1)| 00:00:06 |
|*  7 |       INDEX RANGE SCAN| IDX_FNAME_SNAME_DOB_ID | 35148 |  1853K|   432   (1)| 00:00:06 |
------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=5)
   2 - filter("A"."I">=3 OR "FNAME">'ANNA' OR "SNAME"<'Banana')
   6 - access("FNAME">='ANNA' AND "FNAME" IS NOT NULL)
       filter("SNAME"<='Banana')
   7 - access("FNAME">'ANNA' AND "FNAME" IS NOT NULL)
       filter((LNNVL("SNAME"<='Banana') OR LNNVL(SYS_OP_DESCEND("SNAME")>=HEXTORAW('BD9E
              919E919EFF') )) AND "FNAME">='ANNA')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        828  consistent gets
          0  physical reads
          0  redo size
        684  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)
          5  rows processed

/********* We only changed the sorting direction on one column 'SNAME'
and now an unexpected index is being used in the query above
and SORT ORDER BY and WINDOW SORT appears
and the query takes 10x longer than with the non-descending 'SNAME'.
Why? *********/

/********* Let's delete that index that did not even match the ORDER BY clause ***********/

SQL> DROP INDEX ORAUSER.IDX_FNAME_SNAME_DOB_ID;
Index dropped.

/********* ...and let's execute the same query again ***********/

SQL> SELECT
  2      FNAME,SNAME,DOB, I
  3   FROM    (  
  4            SELECT /*+ INDEX(CUSTOMER IDX_FNAME_SNAME_D_ID) FIRST_ROWS */
  5               row_number() OVER (PARTITION BY FNAME,SNAME ORDER BY FNAME,SNAME DESC,ID) I,
  6               FNAME,
  7               SNAME,
  8               DOB
  9            FROM CUSTOMER
 10            WHERE FNAME>='ANNA' AND (FNAME>'ANNA' OR SNAME<='Banana')
 11            ORDER BY FNAME,SNAME DESC,ID
 12           ) a
 13   WHERE rownum<=5 AND (a.I>=3 OR FNAME>'ANNA' OR SNAME<'Banana');

FNAME                                              SNAME                                                                            DOB                 I
-------------------------------------------------- -------------------------------------------------------------------------------- ---------- ----------
ANNA                                               Banana                                                                           1969.07.07          3
ANNA                                               Banana                                                                           1969.08.08          4
ANNA                                               Banana                                                                           1969.09.09          5
ANNA                                               Arcana                                                                           1969.05.05          1
ANNA                                               Agana                                                                            1969.05.05          1


Execution Plan
----------------------------------------------------------
Plan hash value: 2728038503

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                 | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                      |     5 |   455 |       | 39031   (1)| 00:07:49 |
|*  1 |  COUNT STOPKEY                  |                      |       |       |       |            |          |
|*  2 |   VIEW                          |                      | 46119 |  4098K|       | 39031   (1)| 00:07:49 |
|   3 |    SORT ORDER BY                |                      | 46119 |  2432K|  6168K| 39031   (1)| 00:07:49 |
|   4 |     WINDOW SORT                 |                      | 46119 |  2432K|  6168K| 39031   (1)| 00:07:49 |
|   5 |      TABLE ACCESS BY INDEX ROWID| CUSTOMER             | 46119 |  2432K|       | 37790   (1)| 00:07:34 |
|*  6 |       INDEX RANGE SCAN          | IDX_FNAME_SNAME_D_ID | 46119 |       |       |   379   (1)| 00:00:05 |
----------------------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=5)
   2 - filter("A"."I">=3 OR "FNAME">'ANNA' OR "SNAME"<'Banana')
   6 - access("FNAME">='ANNA' AND "FNAME" IS NOT NULL)
       filter("FNAME">'ANNA' OR SYS_OP_UNDESCEND(SYS_OP_DESCEND("SNAME"))<='Banana' AND
              SYS_OP_DESCEND("SNAME")>=HEXTORAW('BD9E919E919EFF') )


Statistics
----------------------------------------------------------
        219  recursive calls
          0  db block gets
      37725  consistent gets
          0  physical reads
          0  redo size
        684  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          8  sorts (memory)
          0  sorts (disk)
          5  rows processed

/********* Now the expected index IS being used however we still have inefficient SORT ORDER BY and WINDOW SORT
and the query takes 10x longer than with the non-descending 'SNAME'.
Why? *********/

SQL>


Regards,
George

Tom,

In you followup, does the word "currently" refer to the current RDBMS version ?

Unfortunately it is illegal to write DESC in PARTITION BY clause like this:

partition by FNAME,SNAME DESC order by BY FNAME,SNAME DESC,ID

...so what is a poor user to do ?

Maybe this:

SQL> CREATE INDEX ORAUSER.IDX_FNAME_SNAME_ID ON ORAUSER.CUSTOMER(FNAME, SNAME, ID);
Index created.

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

SQL> CREATE INDEX ORAUSER.IDX_FNAME_SNAME_DOB_ID ON ORAUSER.CUSTOMER(FNAME, SNAME, DOB, ID);
Index created.


SQL> Select * FROM user_ind_columns WHERE table_name = 'CUSTOMER';

INDEX_NAME            TABLE_NAME COLUMN_NAME   COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESC
--------------------  ----------- ------------- --------------- ------------- ----------- -----
IDX_FNAME_SNAME_ID    CUSTOMER  FNAME      1              50           50      ASC
IDX_FNAME_SNAME_ID    CUSTOMER  SNAME         2        80      80     ASC
IDX_FNAME_SNAME_ID    CUSTOMER  ID            3        22      0      ASC
IDX_FNAME_SNAME_D_ID  CUSTOMER  FNAME         1        50      50     ASC
IDX_FNAME_SNAME_D_ID  CUSTOMER  SYS_NC00005$  2        121           0      DESC
IDX_FNAME_SNAME_D_ID  CUSTOMER  ID      3        22      0      ASC

6 rows selected.


SQL> set autotrace on;

/*********** Now lets substitiute the Oracle's internal name SYS_NC00005$ for SNAME *************/

SQL> SELECT
  2      FNAME,SNAME,DOB, I
  3   FROM    (  
  4            SELECT /*+ INDEX(CUSTOMER IDX_FNAME_SNAME_D_ID) FIRST_ROWS */
  5               row_number() OVER (PARTITION BY FNAME,SYS_NC00005$ ORDER BY FNAME,SNAME DESC,ID) I,
  6               FNAME,
  7               SNAME,
  8               DOB
  9            FROM CUSTOMER
 10            WHERE FNAME>='ANNA' AND (FNAME>'ANNA' OR SYS_NC00005$<='Banana')
 11            ORDER BY FNAME,SNAME DESC,ID
 12           ) a
 13   WHERE rownum<=5 AND (a.I>=3 OR FNAME>'ANNA' OR SNAME<'Banana');

FNAME                                              SNAME                                                                            DOB                I
-------------------------------------------------- -------------------------------------------------------------------------------- --------- ----------
ANNA                                               Banana                                                                           07-JUL-69          3
ANNA                                               Banana                                                                           08-AUG-69          4
ANNA                                               Banana                                                                           09-SEP-69          5
ANNA                                               Arcana                                                                           05-MAY-69          1
ANNA                                               Agana                                                                            05-MAY-69          1


Execution Plan
----------------------------------------------------------
Plan hash value: 1300505472

-------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                      |     5 |   455 | 38654   (1)| 00:07:44 |
|*  1 |  COUNT STOPKEY                 |                      |       |       |            |          |
|*  2 |   VIEW                         |                      | 45670 |  4058K| 38654   (1)| 00:07:44 |
|   3 |    WINDOW NOSORT               |                      | 45670 |  2408K| 38654   (1)| 00:07:44 |
|   4 |     TABLE ACCESS BY INDEX ROWID| CUSTOMER             | 45670 |  2408K| 37426   (1)| 00:07:30 |
|*  5 |      INDEX RANGE SCAN          | IDX_FNAME_SNAME_D_ID | 45670 |       |   379   (1)| 00:00:05 |
-------------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=5)
   2 - filter("A"."I">=3 OR "FNAME">'ANNA' OR "SNAME"<'Banana')
   5 - access("FNAME">='ANNA' AND "FNAME" IS NOT NULL)
       filter("FNAME">'ANNA' OR RAWTOHEX(SYS_OP_DESCEND("SNAME"))<='Banana')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
        684  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)
          5  rows processed

/*********** Everything seems to be fine with this hack, despite partitioning with an index containing one descending column *************/

What do you think ?

Regards,
George

Yeah, I know :( ...that's why I call it a "hack".

If I knew a better way, I wouldn't even bother you about it.


Thanks for looking into it,
George

Dear Tom,

I've been thinking about a less fragile solution to the problem of PARTITION BY clauses not using descending indexes as fast access paths, and came up with the following solution using SYS_OP_DESCEND() function, listed below.

It works just as well as the version with the internal column names from USER_IND_COLUMNS.
The only inconvenience is that the direction of comparisons must be reversed in the WHERE clause between the SYS_OP_DESCEND functions.


What do you think about it?


Regards,
George

SELECT
      FNAME,SNAME,DOB, I
FROM    (  
            SELECT /*+ INDEX(CUSTOMER IDX_FNAME_SNAME_D_ID) FIRST_ROWS */
               row_number() OVER (PARTITION BY FNAME,SYS_OP_DESCEND(SNAME)
                                  ORDER BY FNAME,SNAME DESC,ID) I,
               FNAME,
               SNAME,
               DOB
            FROM CUSTOMER
            WHERE FNAME>='ANNA' AND (FNAME>'ANNA' OR
                  SYS_OP_DESCEND(SNAME )>= SYS_OP_DESCEND('Banana'))
            ORDER BY FNAME,SNAME DESC,ID
        ) a
   WHERE rownum<=5 AND (a.I>=3 OR FNAME>'ANNA' OR SNAME<'Banana');

Tom,

Considering the cases above, can you confirm that Oracle's 10g analytic functions (such as "row_number") :

1) CAN use an ascending index FORWARD.

2) CAN use a descending index FORWARD only after coercion with SYS_OP_DESCEND function.

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

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


Forward/Backward refers to index scan direction relative to its creation direction parameters.
Non-analytics can traverse BTree indices bidirectionally without problems.


Regards,
George Robinson

P.S.
I can send you test cases if you wish, but they would be very similar to what's above. Just say a word if you want them.

Tom,

FYI: Someone in another forum proved me wrong in Case 3.

Case 4 is still a problem but can be coerced into good behavior with SYS_OP_DESCEND function.

Regards,
George Robinson

Tom,

I repectfuly disagree.

The index IS used by windowing funtions right out of the box (see cases A and B below), and in the remaining cases the windowing functions can be coerced to use the index (see cases D and F below).

See for yourself...


George,

SQL> CREATE TABLE ORAUSER.CUSTOMER
  2  (
  3    ID               NUMBER(10),
  4    SNAME            VARCHAR2(80 BYTE)         NOT NULL,
  5    FNAME            VARCHAR2(50 BYTE),
  6    DOB              DATE
  7  );

Table created.

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

50116 rows created.


exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'ORAUSER', tabname => 'CUSTOMER');
PL/SQL procedure successfully completed.

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

Tom,

We've never had a discussion about using SYS_OP_DESCEND to make the windowing funcion use the index an ALL cases (forward, backward, ascending, descending).

We only had a discussion about using the internal name of the column $SYS_XXXXXX from user_ind_columns for a descnding case, and you said:
"... it is rather fragile and not necessarily supported (if something goes wrong, they'll say "do not do that"".

I understand that your latest reply indicates that this previous comment applies to the SYS_OP_DESCEND method, as well.


Regards,
George

Dear Tom,

good day to you, I tried to search for help on sys_op_descend function but was not able to get much help on this. The url you have posted above doesn't lead to any documents / help, if you can please give me some pointers to get help on sys_op_descend.

Thanking you in advance for your help and time on this.

Regards,
Vijay

Hi Tom,

Hope you had a nice weekend :).

I got some strange output while doing analysis on desc index.


#############################
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jun 11 04:11:07 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter password: *****

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create table t as select level a from dual connect by level<5;

Table created.

SQL> create index t_a on t(a desc);

Index created.

SQL> set autotrace on explain
SQL> select * from t where a=2;

         A
----------
         2


Execution Plan
----------------------------------------------------------
Plan hash value: 1740566600

-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     3 |    39 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| T_A  |     3 |    39 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------

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

   1 - access(SYS_OP_DESCEND("A")=HEXTORAW('3EFCFF') )
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("A"))=2)

SQL> explain plan for select * from t where a=2 or a=4;

Explained.


SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 330146040

-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     3 |    39 |     1   (0)| 00:00:01 |
|*  1 |  INDEX FULL SCAN | T_A  |     3 |    39 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------

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

   1 - filter((SYS_OP_DESCEND("A")=HEXTORAW('3EFAFF')  OR
              SYS_OP_DESCEND("A")=HEXTORAW('3EFCFF') ) AND
              (SYS_OP_UNDESCEND(SYS_OP_DESCEND("A"))=2 OR
              SYS_OP_UNDESCEND(SYS_OP_DESCEND("A"))=4))

16 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2137789089

---------------------------------------------------------------------------------------------
| Id  | Operation                         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |         |  8168 | 16336 |    24   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY |       |       |            |          |
---------------------------------------------------------------------------------------------

SQL> select * from t where a=2 or a=4;
select * from t where a=2 or a=4
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel


############################

I did it more than once by connecting agint to scott ,but same strange out put ,even i did it in sqldeveloper it is showing 
some equivalent error as "No more data to read from socket".(in plan output it is showing some undocumented hint as you told to
vijay in recent post).

Can you please tell why it didnt work for more that one firlter condition( is it expected or a known bug ).
I dont have recent 11g's any release installed, can you please tell is it giving same output in latest release :( .



Thanks as always,
Biswaranjan.

   1 - filter((SYS_OP_DESCEND("A")=HEXTORAW('3EFAFF')  OR
              SYS_OP_DESCEND("A")=HEXTORAW('3EFCFF') ) AND
              (SYS_OP_UNDESCEND(SYS_OP_DESCEND("A"))=2 OR
              SYS_OP_UNDESCEND(SYS_OP_DESCEND("A"))=4))

16 rows selected.

<b>what are you typing in here, something is missing</b>

Execution Plan
----------------------------------------------------------
Plan hash value: 2137789089

Hi Tom,
I tried in my organization system in 10.2.0.1 database and 
got the same below error .

#########please try in your 10.2 database 
SQL> create table t as select level a from dual connect by level<5;

Table created.

SQL> 
SQL> create index t_a on t(a desc);

Index created.

SQL> 
SQL> select * from t where a=2;

         A
----------
         2

SQL> 
SQL> select * from t where a=2 or a=4;
select * from t where a=2 or a=4
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel


SQL> select * from dual;
ERROR:
ORA-03114: not connected to ORACLE


SQL> 

####################

even it plan it is showing 16 rows selected it is not giving any output (but threw an error).

Thanks as always.
Biswaranjan

 ops$tkyte%ORA10GR2> select * from v$version;
-
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Prod
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

ops$tkyte%ORA10GR2> create table t as select level a from dual connect by level<5;

Table created.

ops$tkyte%ORA10GR2> create index t_a on t(a desc);

Index created.

ops$tkyte%ORA10GR2> select * from t where a=2;

         A
----------
         2

ops$tkyte%ORA10GR2> select * from t where a=2 or a=4;

         A
----------
         4
         2

ops$tkyte%ORA10GR2>

Hi Tom,

Below is the complete thing I did and I think 
that would be a bug.

####################
SQL> drop table t;

Table dropped.

SQL> create table t as select level a from dual connect by level<5;

Table created.

SQL> select * from t;

         A
----------
         1
         2
         3
         4


Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     4 |    52 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T    |     4 |    52 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

SQL> create index t_1 on t(a desc);

Index created.

SQL> selct * from t where a=2;
SP2-0734: unknown command beginning "selct * fr..." - rest of line ignored
SQL> select * from t where a=2;

         A
----------
         2


Execution Plan
----------------------------------------------------------
Plan hash value: 1021882267

-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |    13 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| T_1  |     1 |    13 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------

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

   1 - access(SYS_OP_DESCEND("A")=HEXTORAW('3EFCFF') )
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("A"))=2)

Note
-----
   - dynamic sampling used for this statement

SQL> select * from t where a=2 or a=4;
select * from t where a=2 or a=4
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
################## 
for where a=2 or a=4 ,it is taking little time and threw the above error.
I expected it should return the record having "a" column value 2 or 4 but it didnt.
I tried many times too but no luck ,might be a bug.
I need your view on this.

thanks as always,
Biswaranjan

I will do utilize support.

Thanks as always,
Biswaranjan

I tried creating descending indexes. It doesn't work. I analyzed the table as well. Any clue Tom?