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

1
vote
2
answers
5.3k
views

"Order by RAND()" - can use Index?

Feb 23 at 12:06 Markus Winand ♦♦ 936
random index mysql order-by-rand
0
votes
2
answers
1.1k
views

index for better performance for a merge operation

Feb 20 at 11:43 antilet 1
merge index
0
votes
0
answers
2.1k
views

Join with inequalities only

Dec 16 '14 at 12:06 Markus Winand ♦♦ 936
inequality join