Oracle Example Scripts for “Sorting and Grouping”


This section contains the create, insert and PL/SQL code to run the examples from Chapter 6 in an Oracle 11gR2 database.

Indexed Order By

DB2
Explain Plan
-----------------------------------------------------------
ID | Operation            |                     Rows | Cost
 1 | RETURN               |                          |  688
 2 |  FETCH SALES         |     394 of 394 (100.00%) |  688
 3 |   IXSCAN SALES_DT_PR | 394 of 1009326 (   .04%) |   24

Predicate Information
 3 - START (Q1.SALE_DATE = (CURRENT DATE - 1 DAYS))
      STOP (Q1.SALE_DATE = (CURRENT DATE - 1 DAYS))
Oracle
---------------------------------------------------------------
|Id | Operation                   | Name        | Rows | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT            |             |  320 |  300 |
| 1 |  TABLE ACCESS BY INDEX ROWID| SALES       |  320 |  300 |
|*2 |   INDEX RANGE SCAN          | SALES_DT_PR |  320 |    4 |
---------------------------------------------------------------

Gathering new statistics is good practice after changing indexes:

BEGIN
     DBMS_STATS.GATHER_TABLE_STATS(null, 'SALES', 
     METHOD_OPT=>'for all indexed columns', CASCADE => true);
END;
/

Indexed Group By

There is one particular problem in the Oracle database (11g) that appears when ordering the grouped result in reverse index order:

SELECT product_id, sum(eur_value)
  FROM sales
 WHERE sale_date = TRUNC(sysdate) - INTERVAL '1' DAY
 GROUP BY product_id
 ORDER BY product_id DESC;
--------------------------------------------------------------
|Id | Operation                   | Name       | Rows | Cost |
--------------------------------------------------------------
| 0 |SELECT STATEMENT             |            |   24 |  193 |
| 1 | SORT GROUP BY               |            |   24 |  193 |
| 2 |  TABLE ACCESS BY INDEX ROWID| SALES      |  321 |  192 |
|*3 |   INDEX RANGE SCAN          | SALES_DT_PR|  321 |    3 |
--------------------------------------------------------------

Although it can use the index when ordering in index order:

SELECT product_id, sum(eur_value)
  FROM sales
 WHERE sale_date = TRUNC(sysdate) - INTERVAL '1' DAY
 GROUP BY product_id
 ORDER BY product_id ASC;
--------------------------------------------------------------
|Id | Operation                   | Name       | Rows | Cost |
--------------------------------------------------------------
| 0 |SELECT STATEMENT             |            |   24 |  192 |
| 1 | SORT GROUP BY NOSORT        |            |   24 |  192 |
| 2 |  TABLE ACCESS BY INDEX ROWID| SALES      |  321 |  192 |
|*3 |   INDEX RANGE SCAN          | SALES_DT_PR|  321 |    3 |
--------------------------------------------------------------

There is no known workaround for this problem.

About the Author

Photo of Markus Winand
Markus Winand tunes developers for high SQL performance. He also published the book SQL Performance Explained and offers in-house training as well as remote coaching at http://winand.at/

?Recent questions at
Ask.Use-The-Index-Luke.com

0
votes
1
answer
70
views

PostgreSQL Bitmap Heap Scan on index is very slow but Index Only Scan is fast

1 hour ago Markus Winand ♦♦ 881
index postgresql postgres sql
3
votes
2
answers
356
views

pagination with nulls

yesterday Rocky 46
pagination
0
votes
2
answers
66
views