Re: long running select min(timestamp) query

On Jan 28, 10:40 am, "bobdu..._at_gmail.com" <bobdu..._at_gmail.com> wrote:
> Hi,
>
> I have a basic event table in my 10g database, primary integer key,
> and a nonnull timestamp "eventdate" field.  When executing the
> following command:
>
> select min(eventdate) from events;
>
> It can occasionally take a VERY long time.  There is a standard btree
> index on "eventdate", and roughly 20 other columns, a few of which
> also have basic btree indexes.
>
> In my current scenario, the table has less than a million records, and
> the query seems to be taking upwards of 10 minutes!!!  Here's what the
> console says the plan is:
>
> Operation       Object  Object Type     Order   Rows    Size (KB)       Cost    Time (sec)      CPU
> Cost    I/O Cost
> SELECT STATEMENT                        3                               4
>  SORT AGGREGATE                         2       1       0.011
>   INDEX FULL SCAN (MIN/MAX) EVENTS_EVENTDATE    INDEX   1       736195  7,908.345
> 4       1       28686   4
>
> I've run the ADDM, it updated the stats, but that doesn't seem to help
> (i run it again it has no recommendations).  Am i doing something
> silly with this table or is there some better way for me to phrase
> this query?
>
> Thanks,
> Bob

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-

|   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. Received on Mon Jan 28 2008 - 10:39:13 CST