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