On Jan 28, 10:40 am, "bobdu...@gmail.com" <bobdu...@gmail.com> wrote:

Try a simple test:
First, create a table with a single column and an index on that
column:
CREATE TABLE T1 (EVENTDATE TIMESTAMP NOT NULL);
CREATE INDEX T1_IND1 ON T1(EVENTDATE);

Now, insert 900,000 rows into the table, commit and gather table and
index stats:
INSERT INTO
  T1
SELECT
  TRUNC(SYSDATE-3000)+ROWNUM/100
FROM
  DUAL
CONNECT BY
  LEVEL<=900000;

COMMIT;

EXEC
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE)

Finally, run a simple query:
SELECT
  MIN(EVENTDATE)
FROM
  T1;

From a 10046 trace, only 3 blocks read, all by consistent read:
EXEC #12:c=0,e=352,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=7550820123
FETCH #12:c=0,e=86,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=1,tim=7550820562

The DBMS XPLAN:
--------------------------------------------------------------------------- ---------------------
| Id  | Operation                  | Name    | Starts | E-Rows | A-
Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------- ---------------------
|   1 |  SORT AGGREGATE            |         |      1 |      1 |
1 |00:00:00.01 |       3 |
|   2 |   INDEX FULL SCAN (MIN/MAX)| T1_IND1 |      1 |    904K|
1 |00:00:00.01 |       3 |
--------------------------------------------------------------------------- ---------------------

Does the above experiment execute quickly, and read only 3 blocks on
your system?

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.