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

---------------------------------------------------------------
|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
1
answer
59
views

How to query for "previous page" with keyset pagination?

yesterday alextsg 16
pagination postgresql
0
votes
0
answers
92
views
2
votes
1
answer
163
views

Table Names in Plural Form

Jul 31 at 16:43 simas 36
table