Index usage for Min/Max select

  • From: "Laimutis Nedzinskas" <Laimutis.Nedzinskas@xxxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 11 Sep 2006 16:52:15 -0000

The question is about index usage to get min(column) or max(column) in
order to avoid "sort aggregate".
I have two selects which by my best knowledge are equivalent(if not than
I am fool, fool, fool) but both have different costs.

Note that columns involved are all not null.

The first select "Select min(datetime) from A where type=1" uses SORT
AGGREGATE on 255K of columns, isn't it: 

| Id  | Operation          | Name                           | Rows  |
Bytes | Cost (%CPU)| Time     |
|   1 |  SORT AGGREGATE   |                                |     1 |
11 |            |          |
|*  2 |   INDEX RANGE SCAN| FAT_INDEX_ON_TYPE_DATETIME     |   255K|
2749K|    13   (8)| 00:00:01 |

The second select ()

"Select datetime  from 
        (Select datetime from A where type=1 order by datetime) 
where rownum <=1"

performs much better:

| Id  | Operation          | Name                           | Rows  |
Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT   |                                |     1 |
9 |     3   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY     |                                |       |
|            |          |
|   2 |   VIEW             |                                |     2 |
18 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN|  FAT_INDEX_ON_TYPE_DATETIME    |     2 |
22 |     3   (0)| 00:00:01 |

I remember I used to see CBO operation like min/max scan or something
like that which essentially did the same as "order by" select. I.e. it
was enough for the engine to search index tree. remember that there are
no null pitfalls in this case. I do not think there are any NLS pitfalls
as there are no character columns. Even if it was my understanding is
that min and order by use the same NLS comparison semantics.
My best guess is that Oracle does no handle min/max specifically while
in the general case aggregate functions need to process the whole set of
rows, for example AVG.







Fyrirvari/Disclaimer
http://www.landsbanki.is/disclaimer
--
//www.freelists.org/webpage/oracle-l


Other related posts:

  • » Index usage for Min/Max select