Index usage for Min/Max select

From: Laimutis Nedzinskas <Laimutis.Nedzinskas_at_landsbanki.is>
Date: Mon, 11 Sep 2006 16:52:15 -0000
Message-ID: <5A8896FB2AFC5445A7DCFC5903CCA6B06FEAB3@W03856.li01r1d.lais.net>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Sep 11 2006 - 11:52:15 CDT