title: Selecting both MIN and MAX From the Table is slower than expected
body: <p>I have a table <code>MYTABLE</code> with a date column <code>SDATE</code> which is the primary key of the table and has a unique index on it. </p>
  
  <p>When I run this query:</p>
  
  <pre><code>SELECT MIN(SDATE) FROM MYTABLE
  </code></pre>
  
  <p>it gives answer instantly. The same happens for: </p>
  
  <pre><code>SELECT MAX(SDATE) FROM MYTABLE
  </code></pre>
  
  <p>But, if I query both together:</p>
  
  <pre><code>SELECT MIN(SDATE), MAX(SDATE) FROM MYTABLE
  </code></pre>
  
  <p>it takes much more time to execute. I analyzed the plans and found when one of min or max is queried, it uses INDEX FULL SCAN(MIN/MAX) but when both are queried at the same time, it does a FULL TABLE SCAN.  </p>
  
  <p>why?</p>
  
  <p><strong>Test Data:</strong></p>
  
  <p>version <code>11g</code></p>
  
  <pre><code>create table MYTABLE
  (
    SDATE  DATE not null,
    CELL   VARCHAR2(10),
    data NUMBER
  )
  tablespace CHIPS
    pctfree 10
    pctused 40
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    );
  
  alter table MYTABLE
    add constraint PK_SDATE primary key (SDATE)
    using index 
    tablespace SYSTEM
    pctfree 10
    initrans 2
    maxtrans 255
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    );
  </code></pre>
  
  <p>Load table:</p>
  
  <pre><code>declare 
    i integer;
  begin
    for i in 0 .. 100000 loop
       insert into MYTABLE(sdate, cell, data)
       values(sysdate - i/24, 'T' || i, i);     
       commit;
    end loop;
  end;
  </code></pre>
  
  <p>Gather stats: </p>
  
  <pre><code>begin
    dbms_stats.gather_table_stats(tabname =&gt; 'MYTABLE', ownname =&gt; 'SYS');
  end;
  </code></pre>
  
  <p>Plan1:</p>
  
  <p><img src="https://i.stack.imgur.com/jHwT8.jpg" alt="enter image description here"></p>
  
  <p>Plan2:</p>
  
  <p><img src="https://i.stack.imgur.com/MVgsh.jpg" alt="enter image description here"></p>