Min and Max

I was just reading Martin Widlake’s Blog http://mwidlake.wordpress.com/2009/05/27/min-and-max-do-not-mix/ and thought to give it a try on my development environment to have a further look into the same.

Results on my environment i found to be a bit different

first create the test table having 1e5 – 1 rows

drop table jt_test purge;
create table jt_test
 as
 select
 rownum n1
 , 'Mark Williams' v1
 , to_date('01-Jan-2004', 'DD-Mon-RRRR') d1
 from dual
 connect by level < 1e5
 ;
create index jt_test_idx on jt_test(n1);
exec dbms_stats.gather_table_stats(user, 'JT_TEST');

So now we have created the test table and created a non-unique index and gathered the stats on this table.
Now let us see the trace output for count(*)

  
COUNT(*)                                                                                                                                  
----------                                                                                                                                  
     99999                                                                                                                                  
Execution Plan
----------------------------------------------------------                                                                                  
Plan hash value: 3397675622                                                                                                                 
----------------------------------------------------------------------                                                                      
| Id  | Operation          | Name    | Rows  | Cost (%CPU)| Time     |                                                                      
----------------------------------------------------------------------                                                                      
|   0 | SELECT STATEMENT   |         |     1 |   128   (2)| 00:00:02 |                                                                      
|   1 |  SORT AGGREGATE    |         |     1 |            |          |                                                                      
|   2 |   TABLE ACCESS FULL| JT_TEST | 99999 |   128   (2)| 00:00:02 |                                                                      
----------------------------------------------------------------------                                                                      
Statistics
----------------------------------------------------------                                                                                  
          1  recursive calls                                                                                                                
          0  db block gets                                                                                                                  
        446  consistent gets                                                                                                                
          0  physical reads                                                                                                                 
          0  redo size                                                                                                                      
        517  bytes sent via SQL*Net to client                                                                                               
        468  bytes received via SQL*Net from client                                                                                         
          2  SQL*Net roundtrips to/from client                                                                                              
          0  sorts (memory)                                                                                                                 
          0  sorts (disk)                                                                                                                   
          1  rows processed

So the cost of full scan is 128 and consistent gets 446
Now we will check trace output for min:

   MIN(N1)                                                                                                                                  
----------                                                                                                                                  
         1                                                                                                                                  
Execution Plan
----------------------------------------------------------                                                                                  
Plan hash value: 2482634300                                                                                                                 
------------------------------------------------------------------------------------------                                                  
| Id  | Operation                  | Name        | Rows  | Bytes | Cost (%CPU)| Time     |                                                  
------------------------------------------------------------------------------------------                                                  
|   0 | SELECT STATEMENT           |             |     1 |     5 |   128   (2)| 00:00:02 |                                                  
|   1 |  SORT AGGREGATE            |             |     1 |     5 |            |          |                                                  
|   2 |   INDEX FULL SCAN (MIN/MAX)| JT_TEST_IDX | 99999 |   488K|            |          |                                                  
------------------------------------------------------------------------------------------                                                  
Statistics
----------------------------------------------------------                                                                                  
          1  recursive calls                                                                                                                
          0  db block gets                                                                                                                  
          2  consistent gets                                                                                                                
          0  physical reads                                                                                                                 
          0  redo size                                                                                                                      
        514  bytes sent via SQL*Net to client                                                                                               
        468  bytes received via SQL*Net from client                                                                                         
          2  SQL*Net roundtrips to/from client                                                                                              
          0  sorts (memory)                                                                                                                 
          0  sorts (disk)                                                                                                                   
          1  rows processed                                                                                                                 

Now we can see that now Oracle is using the INDEX FULL SCAN (MIN/MAX) and the consistent gets have dropped to just 2, however the cost remains the same. Now we check the trace output of max(n1)

   MAX(N1)                                                                                                                                  
----------                                                                                                                                  
     99999                                                                                                                                  
Execution Plan
----------------------------------------------------------                                                                                  
Plan hash value: 2482634300                                                                                                                 
------------------------------------------------------------------------------------------                                                  
| Id  | Operation                  | Name        | Rows  | Bytes | Cost (%CPU)| Time     |                                                  
------------------------------------------------------------------------------------------                                                  
|   0 | SELECT STATEMENT           |             |     1 |     5 |   128   (2)| 00:00:02 |                                                  
|   1 |  SORT AGGREGATE            |             |     1 |     5 |            |          |                                                  
|   2 |   INDEX FULL SCAN (MIN/MAX)| JT_TEST_IDX | 99999 |   488K|            |          |                                                  
------------------------------------------------------------------------------------------                                                  
Statistics
----------------------------------------------------------                                                                                  
          1  recursive calls                                                                                                                
          0  db block gets                                                                                                                  
          2  consistent gets                                                                                                                
          0  physical reads                                                                                                                 
          0  redo size                                                                                                                      
        516  bytes sent via SQL*Net to client                                                                                               
        468  bytes received via SQL*Net from client                                                                                         
          2  SQL*Net roundtrips to/from client                                                                                              
          0  sorts (memory)                                                                                                                 
          0  sorts (disk)                                                                                                                   
          1  rows processed                                                                                                                 

Same as expected to be that of min execution.
Now let us combine the min and max in one single statement. And as mentioned by Martin in his blog sometimes people require both at the same time.
select min(n1), max(n1) from jt_test;
And check the trace output:

   MIN(N1)    MAX(N1)                                                                                                                       
---------- ----------                                                                                                                       
         1      99999                                                                                                                       


Execution Plan
----------------------------------------------------------                                                                                  
Plan hash value: 3397675622                                                                                                                 
                                                                                                                                            
------------------------------------------------------------------------------                                                              
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |                                                              
------------------------------------------------------------------------------                                                              
|   0 | SELECT STATEMENT   |         |     1 |     5 |   128   (2)| 00:00:02 |                                                              
|   1 |  SORT AGGREGATE    |         |     1 |     5 |            |          |                                                              
|   2 |   TABLE ACCESS FULL| JT_TEST | 99999 |   488K|   128   (2)| 00:00:02 |                                                              
------------------------------------------------------------------------------                                                              


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

It has gone to full table scan instead of using index at all. And the cost and consistent gets are same as that of count(*). Note: this index currently this table have is non-unique by definition though the data is still unique in there.
Now lets guide the optimizer to have a good plan for this requirement and check the trace output
select min(n1) from jt_test
union
select max(n1) from jt_test
;

   MIN(N1)                                                                                                                                  
----------                                                                                                                                  
         1                                                                                                                                  
     99999                                                                                                                                  
Execution Plan
----------------------------------------------------------                                                                                  
Plan hash value: 2944904043                                                                                                                 
----------------------------------------------------------------------------------------------------                                        
| Id  | Operation                    | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                                        
----------------------------------------------------------------------------------------------------                                        
|   0 | SELECT STATEMENT             |             |     2 |    10 |       |   885  (52)| 00:00:11 |                                        
|   1 |  SORT UNIQUE                 |             |     2 |    10 |  4753K|   885  (52)| 00:00:11 |                                        
|   2 |   UNION-ALL                  |             |       |       |       |            |          |                                        
|   3 |    SORT AGGREGATE            |             |     1 |     5 |       |   442   (3)| 00:00:06 |                                        
|   4 |     INDEX FULL SCAN (MIN/MAX)| JT_TEST_IDX | 99999 |   488K|       |            |          |                                        
|   5 |    SORT AGGREGATE            |             |     1 |     5 |       |   442   (3)| 00:00:06 |                                        
|   6 |     INDEX FULL SCAN (MIN/MAX)| JT_TEST_IDX | 99999 |   488K|       |            |          |                                        
----------------------------------------------------------------------------------------------------                                        
Statistics
----------------------------------------------------------                                                                                  
          1  recursive calls                                                                                                                
          0  db block gets                                                                                                                  
          4  consistent gets                                                                                                                
          0  physical reads                                                                                                                 
          0  redo size                                                                                                                      
        571  bytes sent via SQL*Net to client                                                                                               
        468  bytes received via SQL*Net from client                                                                                         
          2  SQL*Net roundtrips to/from client                                                                                              
          1  sorts (memory)                                                                                                                 
          0  sorts (disk)                                                                                                                   
          2  rows processed                                                                                                                 

Surprisingly cost has gone at so much high of 885 just 7 times of full table scan itself. But yes the consistent gets are as expected 4 (2 for min plus 2 for max). And as this was just union and not union all the optimizer has performed one another SORT UNIQUE which is having the cost of 50% for our execution. So now check with “union all”.
select min(n1) from jt_test
union all
select max(n1) from jt_test
;

   MIN(N1)                                                                                                                                  
----------                                                                                                                                  
         1                                                                                                                                  
     99999                                                                                                                                  
Execution Plan
----------------------------------------------------------                                                                                  
Plan hash value: 764868261                                                                                                                  
-------------------------------------------------------------------------------------------                                                 
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |                                                 
-------------------------------------------------------------------------------------------                                                 
|   0 | SELECT STATEMENT            |             |     2 |    10 |   256  (51)| 00:00:04 |                                                 
|   1 |  UNION-ALL                  |             |       |       |            |          |                                                 
|   2 |   SORT AGGREGATE            |             |     1 |     5 |            |          |                                                 
|   3 |    INDEX FULL SCAN (MIN/MAX)| JT_TEST_IDX | 99999 |   488K|            |          |                                                 
|   4 |   SORT AGGREGATE            |             |     1 |     5 |            |          |                                                 
|   5 |    INDEX FULL SCAN (MIN/MAX)| JT_TEST_IDX | 99999 |   488K|            |          |                                                 
-------------------------------------------------------------------------------------------                                                 
Statistics
----------------------------------------------------------                                                                                  
          1  recursive calls                                                                                                                
          0  db block gets                                                                                                                  
          4  consistent gets                                                                                                                
          0  physical reads                                                                                                                 
          0  redo size                                                                                                                      
        571  bytes sent via SQL*Net to client                                                                                               
        468  bytes received via SQL*Net from client                                                                                         
          2  SQL*Net roundtrips to/from client                                                                                              
          0  sorts (memory)                                                                                                                 
          0  sorts (disk)                                                                                                                   
          2  rows processed                                                                                                                 

Now see the cost has dropped surprisingly 25% of what was with just union. And consistent gets are as expected 4. Now we explore another trick as mentioned by Martin in his article of adding a dummy column to select min and max like scalar sub query an then check the trace output:
select min_val, max_val
from
(select min(n1) min_val, 1 dum from jt_test) a
, (select max(n1) max_val, 1 dum from jt_test) b
where a.dum = b.dum
;

   MIN_VAL    MAX_VAL                                                                                                                       
---------- ----------                                                                                                                       
         1      99999                                                                                                                       
Execution Plan
----------------------------------------------------------                                                                                  
Plan hash value: 693243002                                                                                                                  
--------------------------------------------------------------------------------------------                                                
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |                                                
--------------------------------------------------------------------------------------------                                                
|   0 | SELECT STATEMENT             |             |     1 |    32 |   256   (2)| 00:00:04 |                                                
|   1 |  NESTED LOOPS                |             |     1 |    32 |   256   (2)| 00:00:04 |                                                
|   2 |   VIEW                       |             |     1 |    16 |   128   (2)| 00:00:02 |                                                
|   3 |    SORT AGGREGATE            |             |     1 |     5 |            |          |                                                
|   4 |     INDEX FULL SCAN (MIN/MAX)| JT_TEST_IDX | 99999 |   488K|            |          |                                                
|*  5 |   VIEW                       |             |     1 |    16 |   128   (2)| 00:00:02 |                                                
|   6 |    SORT AGGREGATE            |             |     1 |     5 |            |          |                                                
|   7 |     INDEX FULL SCAN (MIN/MAX)| JT_TEST_IDX | 99999 |   488K|            |          |                                                
--------------------------------------------------------------------------------------------                                                
Predicate Information (identified by operation id):                                                                                         
---------------------------------------------------                                                                                         
   5 - filter("A"."DUM"="B"."DUM")                                                                                                          
Statistics
----------------------------------------------------------                                                                                  
          1  recursive calls                                                                                                                
          0  db block gets                                                                                                                  
          4  consistent gets                                                                                                                
          0  physical reads                                                                                                                 
          0  redo size                                                                                                                      
        584  bytes sent via SQL*Net to client                                                                                               
        468  bytes received via SQL*Net from client                                                                                         
          2  SQL*Net roundtrips to/from client                                                                                              
          0  sorts (memory)                                                                                                                 
          0  sorts (disk)                                                                                                                   
          1  rows processed                                                                                                                 

Still we see the cost is same as that of union all and all other details of the trace match with that also except one filter applied which we have passed in our SQL. Now this all was with non-unique index. Now we drop this index and create a primary key on this table which will then essentially have a unique index on this column
rem drop the non-unique index
drop index jt_test_idx;

rem now create a primary on n1 column of this test table
rem and gather stats
alter table jt_test
add constraint jt_test_pk
primary key (n1);
exec dbms_stats.gather_table_stats(user, 'JT_TEST');

Now we will repeat all the above steps and check all trace outputs: first count(*)

  COUNT(*)                                                                                                                                  
----------                                                                                                                                  
     99999                                                                                                                                  
Execution Plan
----------------------------------------------------------                                                                                  
Plan hash value: 886640778                                                                                                                  
----------------------------------------------------------------------------                                                                
| Id  | Operation             | Name       | Rows  | Cost (%CPU)| Time     |                                                                
----------------------------------------------------------------------------                                                                
|   0 | SELECT STATEMENT      |            |     1 |    60   (4)| 00:00:01 |                                                                
|   1 |  SORT AGGREGATE       |            |     1 |            |          |                                                                
|   2 |   INDEX FAST FULL SCAN| JT_TEST_PK | 99959 |    60   (4)| 00:00:01 |                                                                
----------------------------------------------------------------------------                                                                
Statistics
----------------------------------------------------------                                                                                  
          1  recursive calls                                                                                                                
          0  db block gets                                                                                                                  
        215  consistent gets                                                                                                                
          0  physical reads                                                                                                                 
          0  redo size                                                                                                                      
        517  bytes sent via SQL*Net to client                                                                                               
        468  bytes received via SQL*Net from client                                                                                         
          2  SQL*Net roundtrips to/from client                                                                                              
          0  sorts (memory)                                                                                                                 
          0  sorts (disk)                                                                                                                   
          1  rows processed                                                                                                                 

Here optimizer has chosen the INDEX FAST FULL SCAN instead of TABLE ACCESS FULL (with non-unique index) and cost has also dropped to 60. And consistent gets also reduced to around half of previous test. Now min

   MIN(N1)                                                                                                                                  
----------                                                                                                                                  
         1                                                                                                                                  
Execution Plan
----------------------------------------------------------                                                                                  
Plan hash value: 2487590171                                                                                                                 
-----------------------------------------------------------------------------------------                                                   
| Id  | Operation                  | Name       | Rows  | Bytes | Cost (%CPU)| Time     |                                                   
-----------------------------------------------------------------------------------------                                                   
|   0 | SELECT STATEMENT           |            |     1 |     5 |     2   (0)| 00:00:01 |                                                   
|   1 |  SORT AGGREGATE            |            |     1 |     5 |            |          |                                                   
|   2 |   INDEX FULL SCAN (MIN/MAX)| JT_TEST_PK | 99959 |   488K|     2   (0)| 00:00:01 |                                                   
-----------------------------------------------------------------------------------------                                                   
Statistics
----------------------------------------------------------                                                                                  
          1  recursive calls                                                                                                                
          0  db block gets                                                                                                                  
          2  consistent gets                                                                                                                
          0  physical reads                                                                                                                 
          0  redo size                                                                                                                      
        514  bytes sent via SQL*Net to client                                                                                               
        468  bytes received via SQL*Net from client                                                                                         
          2  SQL*Net roundtrips to/from client                                                                                              
          0  sorts (memory)                                                                                                                 
          0  sorts (disk)                                                                                                                   
          1  rows processed                                                                                                                 

Now we see a dramatic changes in cost it has reduced to just 2 from 128 in the previous test that looks good and it is also using the INDEX FULL SCAN (MIN/MAX). Now check for max

   MAX(N1)                                                                                                                                  
----------                                                                                                                                  
     99999                                                                                                                                  
Execution Plan
----------------------------------------------------------                                                                                  
Plan hash value: 2487590171                                                                                                                 
-----------------------------------------------------------------------------------------                                                   
| Id  | Operation                  | Name       | Rows  | Bytes | Cost (%CPU)| Time     |                                                   
-----------------------------------------------------------------------------------------                                                   
|   0 | SELECT STATEMENT           |            |     1 |     5 |     2   (0)| 00:00:01 |                                                   
|   1 |  SORT AGGREGATE            |            |     1 |     5 |            |          |                                                   
|   2 |   INDEX FULL SCAN (MIN/MAX)| JT_TEST_PK | 99959 |   488K|     2   (0)| 00:00:01 |                                                   
-----------------------------------------------------------------------------------------                                                   
Statistics
----------------------------------------------------------                                                                                  
          1  recursive calls                                                                                                                
          0  db block gets                                                                                                                  
          2  consistent gets                                                                                                                
          0  physical reads                                                                                                                 
          0  redo size                                                                                                                      
        516  bytes sent via SQL*Net to client                                                                                               
        468  bytes received via SQL*Net from client                                                                                         
          2  SQL*Net roundtrips to/from client                                                                                              
          0  sorts (memory)                                                                                                                 
          0  sorts (disk)                                                                                                                   
          1  rows processed                                                                                                                 

Same as that of min so far so good. Now let us check for min, max combined in single statement.
select min(n1), max(n1) from jt_test;

   MIN(N1)    MAX(N1)                                                                                                                       
---------- ----------                                                                                                                       
         1      99999                                                                                                                       
Execution Plan
----------------------------------------------------------                                                                                  
Plan hash value: 886640778                                                                                                                  
------------------------------------------------------------------------------------                                                        
| Id  | Operation             | Name       | Rows  | Bytes | Cost (%CPU)| Time     |                                                        
------------------------------------------------------------------------------------                                                        
|   0 | SELECT STATEMENT      |            |     1 |     5 |    60   (4)| 00:00:01 |                                                        
|   1 |  SORT AGGREGATE       |            |     1 |     5 |            |          |                                                        
|   2 |   INDEX FAST FULL SCAN| JT_TEST_PK | 99959 |   488K|    60   (4)| 00:00:01 |                                                        
------------------------------------------------------------------------------------                                                        
Statistics
----------------------------------------------------------                                                                                  
          1  recursive calls                                                                                                                
          0  db block gets                                                                                                                  
        215  consistent gets                                                                                                                
          0  physical reads                                                                                                                 
          0  redo size                                                                                                                      
        584  bytes sent via SQL*Net to client                                                                                               
        468  bytes received via SQL*Net from client                                                                                         
          2  SQL*Net roundtrips to/from client                                                                                              
          0  sorts (memory)                                                                                                                 
          0  sorts (disk)                                                                                                                   
          1  rows processed                                                                                                                 

It has gone to give the same trace output as that of COUNT(*) and using INDEX FAST FULL SCAN. Now we check for union
select min(n1) from jt_test
union
select max(n1) from jt_test
;

   MIN(N1)                                                                                                                                  
----------                                                                                                                                  
         1                                                                                                                                  
     99999                                                                                                                                  
Execution Plan
----------------------------------------------------------                                                                                  
Plan hash value: 2768791359                                                                                                                 
---------------------------------------------------------------------------------------------------                                         
| Id  | Operation                    | Name       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                                         
---------------------------------------------------------------------------------------------------                                         
|   0 | SELECT STATEMENT             |            |     2 |    10 |       |   633  (52)| 00:00:08 |                                         
|   1 |  SORT UNIQUE                 |            |     2 |    10 |  4720K|   633  (52)| 00:00:08 |                                         
|   2 |   UNION-ALL                  |            |       |       |       |            |          |                                         
|   3 |    SORT AGGREGATE            |            |     1 |     5 |       |   316   (4)| 00:00:04 |                                         
|   4 |     INDEX FULL SCAN (MIN/MAX)| JT_TEST_PK | 99959 |   488K|       |     2   (0)| 00:00:01 |                                         
|   5 |    SORT AGGREGATE            |            |     1 |     5 |       |   316   (4)| 00:00:04 |                                         
|   6 |     INDEX FULL SCAN (MIN/MAX)| JT_TEST_PK | 99959 |   488K|       |     2   (0)| 00:00:01 |                                         
---------------------------------------------------------------------------------------------------                                         
Statistics
----------------------------------------------------------                                                                                  
          1  recursive calls                                                                                                                
          0  db block gets                                                                                                                  
          4  consistent gets                                                                                                                
          0  physical reads                                                                                                                 
          0  redo size                                                                                                                      
        571  bytes sent via SQL*Net to client                                                                                               
        468  bytes received via SQL*Net from client                                                                                         
          2  SQL*Net roundtrips to/from client                                                                                              
          1  sorts (memory)                                                                                                                 
          0  sorts (disk)                                                                                                                   
          2  rows processed                                                                                                                 

Now again the cost has gone high but it is lesser than with non-unique index. And consistent gets are good as expected. Now let us check union all:
select min(n1) from jt_test
union all
select max(n1) from jt_test
;

   MIN(N1)                                                                                                                                  
----------                                                                                                                                  
         1                                                                                                                                  
     99999                                                                                                                                  
Execution Plan
----------------------------------------------------------                                                                                  
Plan hash value: 736211320                                                                                                                  
                                                                                                                                            
------------------------------------------------------------------------------------------                                                  
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |                                                  
------------------------------------------------------------------------------------------                                                  
|   0 | SELECT STATEMENT            |            |     2 |    10 |     4  (50)| 00:00:01 |                                                  
|   1 |  UNION-ALL                  |            |       |       |            |          |                                                  
|   2 |   SORT AGGREGATE            |            |     1 |     5 |            |          |                                                  
|   3 |    INDEX FULL SCAN (MIN/MAX)| JT_TEST_PK | 99959 |   488K|     2   (0)| 00:00:01 |                                                  
|   4 |   SORT AGGREGATE            |            |     1 |     5 |            |          |                                                  
|   5 |    INDEX FULL SCAN (MIN/MAX)| JT_TEST_PK | 99959 |   488K|     2   (0)| 00:00:01 |                                                  
------------------------------------------------------------------------------------------                                                  
Statistics
----------------------------------------------------------                                                                                  
          1  recursive calls                                                                                                                
          0  db block gets                                                                                                                  
          4  consistent gets                                                                                                                
          0  physical reads                                                                                                                 
          0  redo size                                                                                                                      
        571  bytes sent via SQL*Net to client                                                                                               
        468  bytes received via SQL*Net from client                                                                                         
          2  SQL*Net roundtrips to/from client                                                                                              
          0  sorts (memory)                                                                                                                 
          0  sorts (disk)                                                                                                                   
          2  rows processed                                                                                                                 

Now the cost has also dropped to just 4 dramatically and consistent gets are also as expected. Now the scalar sub query method:
select min_val, max_val
from
(select min(n1) min_val, 1 dum from jt_test) a
, (select max(n1) max_val, 1 dum from jt_test) b
where a.dum = b.dum
;

   MIN_VAL    MAX_VAL                                                                                                                       
---------- ----------                                                                                                                       
         1      99999                                                                                                                       
Execution Plan
----------------------------------------------------------                                                                                  
Plan hash value: 4181172301                                                                                                                 
-------------------------------------------------------------------------------------------                                                 
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |                                                 
-------------------------------------------------------------------------------------------                                                 
|   0 | SELECT STATEMENT             |            |     1 |    32 |     4   (0)| 00:00:01 |                                                 
|   1 |  NESTED LOOPS                |            |     1 |    32 |     4   (0)| 00:00:01 |                                                 
|   2 |   VIEW                       |            |     1 |    16 |     2   (0)| 00:00:01 |                                                 
|   3 |    SORT AGGREGATE            |            |     1 |     5 |            |          |                                                 
|   4 |     INDEX FULL SCAN (MIN/MAX)| JT_TEST_PK | 99959 |   488K|     2   (0)| 00:00:01 |                                                 
|*  5 |   VIEW                       |            |     1 |    16 |     2   (0)| 00:00:01 |                                                 
|   6 |    SORT AGGREGATE            |            |     1 |     5 |            |          |                                                 
|   7 |     INDEX FULL SCAN (MIN/MAX)| JT_TEST_PK | 99959 |   488K|     2   (0)| 00:00:01 |                                                 
-------------------------------------------------------------------------------------------                                                 
Predicate Information (identified by operation id):                                                                                         
---------------------------------------------------                                                                                         
   5 - filter("A"."DUM"="B"."DUM")                                                                                                          
Statistics
----------------------------------------------------------                                                                                  
          1  recursive calls                                                                                                                
          0  db block gets                                                                                                                  
          4  consistent gets                                                                                                                
          0  physical reads                                                                                                                 
          0  redo size                                                                                                                      
        584  bytes sent via SQL*Net to client                                                                                               
        468  bytes received via SQL*Net from client                                                                                         
          2  SQL*Net roundtrips to/from client                                                                                              
          0  sorts (memory)                                                                                                                 
          0  sorts (disk)                                                                                                                   
          1  rows processed                                                                                                                 

Now also the execution of this matches with union all execution. with same cost and consistent gets. I had also tested this set of execution by dropping the primary key and just creating the unique index explicitly but not putting the details here as to my utter surprise the results match with the non-unique index executions. I wonder why oracle has not used the unique index for giving the result when primary key is created it is also just supported by a unique index itself. I will try to look into this and update all with the findings.
Thanks again to Martin Widlake for his article.
I hope this might also help others and i have kept a copy of this on blog as a faster lookup for this analysis from anywhere using internet of-course :)
I would appreciate any feedback or comments on this which may also include discrepancies in this.

Cheers!!!
Jagdeep Singh Sangwan

About these ads

3 thoughts on “Min and Max

  1. I received a mail from one of my classmate from university time. She was having some confusion in understanding the term “Consistent gets”. Though i have tried to explain him in my reply to the mail. And i have also suggested two articles from two of my Favorite Oracle Experts:
    Jonathan Lewis: http://jonathanlewis.wordpress.com/2009/06/12/consistent-gets-2/
    Thomas Kyte: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:880343948514

    • Well my SQL engine have confusion on Gender of your friend. “She was having some confusion ” and now she turns to he ” i have tried to explain him ”

      Please explain how to process such data :D

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s