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

As an author, trainer, and coach Markus Winand specializes in helping developers cope with SQL performance issues. He also published the book SQL Performance Explained and tweets his best performance tips via @SQLPerfTips.http://winand.at/

Recent Questions at Ask.Use-The-Index-Luke.com

2
votes
1
answer
7
views

Table Names in Plural Form

2 hours ago Markus Winand ♦♦ 556
table
0
votes
1
answer
156
views

We want to buy the book but I can't

Jul 18 at 21:36 Markus Winand ♦♦ 556
book
0
votes
2
answers
161
views