01 February, 2009

MIN/MAX Queries, Execution Plans and COST


Note : I had earlier posted this note with a test case where the Indexed columns were not defined as
NOT NULL (ie, were NULLable) inspite of not really having any NULL values. That resulted in a
different execution plan when querying for both MIN and MAX. I withdrew that post when I realised
my mistake but, being busy through the day, didn't have time to republish it with the corrected test
case where the columns are NOT NULL.
I am including both NULLable and NOT NULL scenarios in this test case.

This test shows how the performance of queries that attempt to retrieve both MIN and MAX values together can be much poorer than two seperate queries for MIN and MAX. The test then goes on to demonstrate 3 methods to handle the performance issue : a) UNION of the two queries, b) Inline Views and c) a Materialized View.
I also demonstrate the difference between the Indexed column being NULLable and NOT NULL.


SQL>
SQL> REM ===========================================================================
SQL> REM Create the table TEST_MINMAX
SQL> REM This is built from SOURCE_TABLE, which is a multiplied copy of DBA_OBJECTS
SQL> REM I will be loading the table with 1.6million rows
SQL> REM ===========================================================================
SQL> REM ===========================================================================
SQL>
SQL> desc source_table;
Name Null? Type
------------------------------------------------------------------------ -------- -------------------------------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)

SQL> select count(*) from source_table;

COUNT(*)
----------
1620512
SQL>
SQL> drop table test_minmax ;
SQL> create table test_minmax as select * from source_table where 1=2;
SQL> alter table test_minmax nologging;
SQL> insert /*+ APPEND */ into test_minmax select * from source_table where object_id is not null;
SQL>
SQL> create index test_minmax_objectid_ndx on test_minmax(object_id) nologging;
SQL> create index test_minmax_created_ndx on test_minmax(created) nologging;
SQL>
SQL> exec dbms_stats.gather_table_stats('HEMANT','TEST_MINMAX',estimate_percent=>NULL,method_opt=>'FOR ALL COLUMNS SIZE 1',cascade=>TRUE);
SQL>
SQL> select count(*) from test_minmax;

COUNT(*)
----------
1620512
SQL> select blocks, num_rows from user_tables where table_name = 'TEST_MINMAX';

BLOCKS NUM_ROWS
---------- ----------
22448 1620512
SQL> select leaf_blocks, num_rows from user_indexes where index_name = 'TEST_MINMAX_OBJECTID_NDX';

LEAF_BLOCKS NUM_ROWS
----------- ----------
3572 1620512
SQL> select leaf_blocks, num_rows from user_indexes where index_name = 'TEST_MINMAX_CREATED_NDX';

LEAF_BLOCKS NUM_ROWS
----------- ----------
4299 1620512
SQL>
SQL> REM ===========================================================================
SQL> REM ***************************************************************************
SQL> REM Get the query execution plan for MIN queries
SQL> REM ###########################################################################
SQL>
SQL> select min(object_id) from test_minmax
2 /

MIN(OBJECT_ID)
--------------
2
SQL> select min(created) from test_minmax
2 /

MIN(CREAT
---------
12-MAR-08
SQL> set autotrace on
SQL> select min(object_id) from test_minmax
2 /

MIN(OBJECT_ID)
--------------
2

Execution Plan
----------------------------------------------------------
Plan hash value: 1603217912

-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 4109 (2)| 00:00:50 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
| 2 | INDEX FULL SCAN (MIN/MAX)| TEST_MINMAX_OBJECTID_NDX | 1620K| 7912K| | |
-------------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
521 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select min(created) from test_minmax
2 /

MIN(CREAT
---------
12-MAR-08

Execution Plan
----------------------------------------------------------
Plan hash value: 201212520

------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 4123 (3)| 00:00:50 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
| 2 | INDEX FULL SCAN (MIN/MAX)| TEST_MINMAX_CREATED_NDX | 1620K| 12M| | |
------------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
524 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> set autotrace off
SQL>
SQL> REM --> that was only 3 Block Gets for both queries.
SQL> REM --> note that the COST was 4,109 and 4,123 !!
SQL> REM --> -- although Oracle really had to read only 3 Blocks
SQL>
SQL> REM ===========================================================================
SQL> REM ***************************************************************************
SQL> REM Get the query execution plan for MAX queries
SQL> REM ###########################################################################
SQL>
SQL> select max(object_id) from test_minmax
2 /

MAX(OBJECT_ID)
--------------
59122
SQL> select max(created) from test_minmax
2 /

MAX(CREAT
---------
02-NOV-08
SQL> set autotrace on
SQL> select max(object_id) from test_minmax
2 /

MAX(OBJECT_ID)
--------------
59122

Execution Plan
----------------------------------------------------------
Plan hash value: 1603217912

-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 4109 (2)| 00:00:50 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
| 2 | INDEX FULL SCAN (MIN/MAX)| TEST_MINMAX_OBJECTID_NDX | 1620K| 7912K| | |
-------------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
523 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select max(created) from test_minmax
2 /

MAX(CREAT
---------
02-NOV-08

Execution Plan
----------------------------------------------------------
Plan hash value: 201212520

------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 4123 (3)| 00:00:50 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
| 2 | INDEX FULL SCAN (MIN/MAX)| TEST_MINMAX_CREATED_NDX | 1620K| 12M| | |
------------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
524 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> set autotrace off
SQL>
SQL> REM --> Again, only 3 Block Gets for both queries
SQL> REM --> Also, the COST is very high, but the real effort is very low
SQL> REM ===========================================================================
SQL> REM ***************************************************************************
SQL> REM Get Min and Max together. NOW we will be surprised !
SQL> REM ###########################################################################
SQL>
SQL> select min(object_id), max(object_id) from test_minmax
2 /

MIN(OBJECT_ID) MAX(OBJECT_ID)
-------------- --------------
2 59122
SQL> select min(created), max(created) from test_minmax
2 /

MIN(CREAT MAX(CREAT
--------- ---------
12-MAR-08 02-NOV-08
SQL> set autotrace on
SQL> select min(object_id), max(object_id) from test_minmax
2 /

MIN(OBJECT_ID) MAX(OBJECT_ID)
-------------- --------------
2 59122

Execution Plan
----------------------------------------------------------
Plan hash value: 1671342682

----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 4109 (2)| 00:00:50 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
| 2 | TABLE ACCESS FULL| TEST_MINMAX | 1620K| 7912K| 4109 (2)| 00:00:50 |
----------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
22269 consistent gets
22257 physical reads
0 redo size
598 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select min(created), max(created) from test_minmax
2 /

MIN(CREAT MAX(CREAT
--------- ---------
12-MAR-08 02-NOV-08

Execution Plan
----------------------------------------------------------
Plan hash value: 1671342682

----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 4123 (3)| 00:00:50 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
| 2 | TABLE ACCESS FULL| TEST_MINMAX | 1620K| 12M| 4123 (3)| 00:00:50 |
----------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
22269 consistent gets
22256 physical reads
0 redo size
602 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> set autotrace off
SQL>
SQL> REM --> That took 22,269 Block Gets -- at the same "COST" of 4,109 or 4,123 !
SQL> REM --> For my 1.6million rows, Oracle prefers a FULL TABLE SCAN.
SQL> REM --> BUT WAIT ! There's more. See the AFTERNOTE section at the end of this round of testing
SQL>
SQL>
SQL> REM ===========================================================================
SQL> REM ***************************************************************************
SQL> REM Try using a UNION
SQL> REM ###########################################################################
SQL>
SQL> select min(object_id) Min_object_id, NULL Max_object_id from test_minmax
2 union
3 select NULL Min_object_id, max(object_id) Max_object_id from test_minmax
4 /

MIN_OBJECT_ID MAX_OBJECT_ID
------------- -------------
2
59122
SQL> select min(created) Min_date, NULL Max_date from test_minmax
2 union
3 select NULL Min_date, max(created) Max_date from test_minmax
4 /

MIN_DATE MAX_DATE
--------- ---------
12-MAR-08
02-NOV-08
SQL> set autotrace on
SQL> select min(object_id) Min_object_id, NULL Max_object_id from test_minmax
2 union
3 select NULL Min_object_id, max(object_id) Max_object_id from test_minmax
4 /

MIN_OBJECT_ID MAX_OBJECT_ID
------------- -------------
2
59122

Execution Plan
----------------------------------------------------------
Plan hash value: 1629869728

-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 10 | | 18482 (52)| 00:03:42 |
| 1 | SORT UNIQUE | | 2 | 10 | 74M| 18482 (52)| 00:03:42 |
| 2 | UNION-ALL | | | | | | |
| 3 | SORT AGGREGATE | | 1 | 5 | | 9241 (4)| 00:01:51 |
| 4 | INDEX FULL SCAN (MIN/MAX)| TEST_MINMAX_OBJECTID_NDX | 1620K| 7912K| | | |
| 5 | SORT AGGREGATE | | 1 | 5 | | 9241 (4)| 00:01:51 |
| 6 | INDEX FULL SCAN (MIN/MAX)| TEST_MINMAX_OBJECTID_NDX | 1620K| 7912K| | | |
-----------------------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
650 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2 rows processed

SQL> select min(created) Min_date, NULL Max_date from test_minmax
2 union
3 select NULL Min_date, max(created) Max_date from test_minmax
4 /

MIN_DATE MAX_DATE
--------- ---------
12-MAR-08
02-NOV-08

Execution Plan
----------------------------------------------------------
Plan hash value: 662419327

----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 16 | | 20349 (52)| 00:04:05 |
| 1 | SORT UNIQUE | | 2 | 16 | 99M| 20349 (52)| 00:04:05 |
| 2 | UNION-ALL | | | | | | |
| 3 | SORT AGGREGATE | | 1 | 8 | | 10174 (4)| 00:02:03 |
| 4 | INDEX FULL SCAN (MIN/MAX)| TEST_MINMAX_CREATED_NDX | 1620K| 12M| | | |
| 5 | SORT AGGREGATE | | 1 | 8 | | 10174 (4)| 00:02:03 |
| 6 | INDEX FULL SCAN (MIN/MAX)| TEST_MINMAX_CREATED_NDX | 1620K| 12M| | | |
----------------------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
648 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2 rows processed

SQL> set autotrace off
SQL>
SQL> REM --> Only 6 Block Gets, at *much higher* COSTS of 18,482 and 20,349 !
SQL>
SQL> REM ===========================================================================
SQL> REM ***************************************************************************
SQL> REM Try using Inline Views to get the two values together in single row
SQL> REM ###########################################################################
SQL>
SQL> select a.Min_object_id, b.Max_object_id from
2 (select min(object_id) Min_object_id from test_minmax) a,
3 (select max(object_id) Max_object_id from test_minmax) b
4 /

MIN_OBJECT_ID MAX_OBJECT_ID
------------- -------------
2 59122
SQL> select a.Min_date, b.Max_date from
2 (select min(created) Min_date from test_minmax) a,
3 (select max(created) Max_date from test_minmax) b
4 /

MIN_DATE MAX_DATE
--------- ---------
12-MAR-08 02-NOV-08
SQL> set autotrace on
SQL> select a.Min_object_id, b.Max_object_id from
2 (select min(object_id) Min_object_id from test_minmax) a,
3 (select max(object_id) Max_object_id from test_minmax) b
4 /

MIN_OBJECT_ID MAX_OBJECT_ID
------------- -------------
2 59122

Execution Plan
----------------------------------------------------------
Plan hash value: 796082343

---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 8218 (2)| 00:01:39 |
| 1 | NESTED LOOPS | | 1 | 26 | 8218 (2)| 00:01:39 |
| 2 | VIEW | | 1 | 13 | 4109 (2)| 00:00:50 |
| 3 | SORT AGGREGATE | | 1 | 5 | | |
| 4 | INDEX FULL SCAN (MIN/MAX)| TEST_MINMAX_OBJECTID_NDX | 1620K| 7912K| | |
| 5 | VIEW | | 1 | 13 | 4109 (2)| 00:00:50 |
| 6 | SORT AGGREGATE | | 1 | 5 | | |
| 7 | INDEX FULL SCAN (MIN/MAX)| TEST_MINMAX_OBJECTID_NDX | 1620K| 7912K| | |
---------------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
596 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select a.Min_date, b.Max_date from
2 (select min(created) Min_date from test_minmax) a,
3 (select max(created) Max_date from test_minmax) b
4 /

MIN_DATE MAX_DATE
--------- ---------
12-MAR-08 02-NOV-08

Execution Plan
----------------------------------------------------------
Plan hash value: 2568336276

--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18 | 8246 (3)| 00:01:39 |
| 1 | NESTED LOOPS | | 1 | 18 | 8246 (3)| 00:01:39 |
| 2 | VIEW | | 1 | 9 | 4123 (3)| 00:00:50 |
| 3 | SORT AGGREGATE | | 1 | 8 | | |
| 4 | INDEX FULL SCAN (MIN/MAX)| TEST_MINMAX_CREATED_NDX | 1620K| 12M| | |
| 5 | VIEW | | 1 | 9 | 4123 (3)| 00:00:50 |
| 6 | SORT AGGREGATE | | 1 | 8 | | |
| 7 | INDEX FULL SCAN (MIN/MAX)| TEST_MINMAX_CREATED_NDX | 1620K| 12M| | |
--------------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
594 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> set autotrace off
SQL>
SQL> REM --> Only 6 Block Gets, although the "COST" is still high.
SQL> REM --> By now, you would have gotten my hint. IGNORE the "COST" !
SQL>
SQL>
SQL> REM ===========================================================================
SQL> REM ***************************************************************************
SQL> REM Using a Materialized View
SQL> REM ###########################################################################
SQL> set autotrace off
SQL> drop materialized view test_minmax_values_mv;
SQL> create materialized view test_minmax_values_mv
2 refresh on commit
3 as
4 select min(object_id) Min_Object_ID, max(object_id) Max_Object_ID, min(created) Min_Created, max(created) Max_Created
5 from test_minmax
6 /
SQL> exec dbms_stats.gather_table_stats('HEMANT','TEST_MINMAX_VALUES_MV',estimate_percent=>NULL,method_opt=>'FOR ALL COLUMNS SIZE 1',cascade=>TRUE);
SQL>
SQL> select Min_Object_ID, Max_Object_ID from test_minmax_values_mv
2 /

MIN_OBJECT_ID MAX_OBJECT_ID
------------- -------------
2 59122
SQL> select Min_Created, Max_Created from test_minmax_values_mv
2 /

MIN_CREAT MAX_CREAT
--------- ---------
12-MAR-08 02-NOV-08
SQL> set autotrace on
SQL> select Min_Object_ID, Max_Object_ID from test_minmax_values_mv
2 /

MIN_OBJECT_ID MAX_OBJECT_ID
------------- -------------
2 59122

Execution Plan
----------------------------------------------------------
Plan hash value: 1925756672

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 2 (0)| 00:00:01 |
| 1 | MAT_VIEW ACCESS FULL| TEST_MINMAX_VALUES_MV | 1 | 8 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
596 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select Min_Created, Max_Created from test_minmax_values_mv
2 /

MIN_CREAT MAX_CREAT
--------- ---------
12-MAR-08 02-NOV-08

Execution Plan
----------------------------------------------------------
Plan hash value: 1925756672

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 | 2 (0)| 00:00:01 |
| 1 | MAT_VIEW ACCESS FULL| TEST_MINMAX_VALUES_MV | 1 | 16 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
600 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> set autotrace off
SQL>
SQL> REM --> Only 3 Block Gets !
SQL> REM --> Yeah. The FASTEST MIN/MAX Query !
SQL>
SQL>
SQL> REM ===========================================================================
SQL> REM ***************************************************************************
SQL> REM AFTERNOTE : Re-visiting the case of FullTableScan on querying both MIN and MAX together
SQL> REM ###########################################################################
SQL>
SQL> REM --> What happens if the columns were actually NOT NULLs
SQL> alter table test_minmax modify (object_id not null);
SQL> alter table test_minmax modify (created not null);
SQL>
SQL> select min(object_id), max(object_id) from test_minmax
2 /

MIN(OBJECT_ID) MAX(OBJECT_ID)
-------------- --------------
2 59122
SQL> select min(created), max(created) from test_minmax
2 /

MIN(CREAT MAX(CREAT
--------- ---------
12-MAR-08 02-NOV-08
SQL> set autotrace on
SQL> select min(object_id), max(object_id) from test_minmax
2 /

MIN(OBJECT_ID) MAX(OBJECT_ID)
-------------- --------------
2 59122

Execution Plan
----------------------------------------------------------
Plan hash value: 2667923095

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 676 (5)| 00:00:09 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
| 2 | INDEX FAST FULL SCAN| TEST_MINMAX_OBJECTID_NDX | 1620K| 7912K| 676 (5)| 00:00:09 |
--------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3592 consistent gets
0 physical reads
0 redo size
598 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select min(created), max(created) from test_minmax
2 /

MIN(CREAT MAX(CREAT
--------- ---------
12-MAR-08 02-NOV-08

Execution Plan
----------------------------------------------------------
Plan hash value: 2239184443

-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 807 (5)| 00:00:10 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
| 2 | INDEX FAST FULL SCAN| TEST_MINMAX_CREATED_NDX | 1620K| 12M| 807 (5)| 00:00:10 |
-------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4324 consistent gets
0 physical reads
0 redo size
602 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> set autotrace off
SQL>
SQL> REM --> When the column is NOT NULL, Oracle knows that it can do an Index Fast Full Scan
SQL> REM --> Yet, this requires 3,592 and 4,324 block gets
SQL> REM --> That is still much higher than the 3 and 6 block gets with the alternate methods
SQL>
SQL> REM ===========================================================================
SQL> REM ***************************************************************************
SQL> REM REM REM REM
SQL> REM ###########################################################################
SQL> REM What have we learnt ?
SQL> REM 1. Min/Max Queries CAN be improved using UNIONs, Inline Views or Materialized Views
SQL> REM 2. COST isn't always a reliable indicator
SQL> REM 3. Whether a column is NULLable or NOT NULL makes a difference to the Optimizer
SQL>
SQL>

3 comments:

Randolf said...

Hemant,

interesting test case. I haven't gone through it myself yet, but I find the cost reported for the INDEX FULL SCAN quite astonishing, given the fact that it takes really only a minimum amount of consistent gets to return the result, but still the cost reported is > 4,000. I'll have to check the 10053 trace once I find some time if there are any clues why there is this large discrepancy in runtime costs and optimizer estimates.

Best Regards,
Randolf

robert said...

The interesting question for me is: why does Oracle not bother to optimize the min/max case? If there are only min and max queries on indexed columns you would expect Oracle to satisfy them with a few index lookups only. Is this query so rare in practice? I cannot believe so. Is it so complicated to identify this situation? The CBO seems to be able to figure much more tricky situations. So, why is it the way it is?

Btw, what Oracle version was this?

Cheers

Hemant K Chitale said...

Robert,
You could see

http://momendba.blogspot.com/2008/07/min-and-max-functions-in-single-query.html

or
http://karenmorton.blogspot.com/2008/07/minmax-and-index-full-scans.html


There are even a few bugs.
See MetaLink Notes # 419574.1 and 5742678.8 , both of which are about the same bug, which still isn't exactly the same behaviour as I've seen.
However, my testing is on 10.2.0.4 (64bit Linux), so I presume that bug 5742678 is fixed in my test environment.

Hemant