Re: long running select min(timestamp) query

From: <bobdurie_at_gmail.com>
Date: Tue, 29 Jan 2008 06:39:16 -0800 (PST)
Message-ID: <cad35366-aaf2-4b2b-962a-c4512acfce0c@f10g2000hsf.googlegroups.com>


On Jan 28, 7:17 pm, joel garry <joel-ga..._at_home.com> wrote:

> On Jan 28, 7: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.
>
> Well, I'm guessing that your 20 column index is just not the best one
> for your min.  What happens if you make a btree index on just that
> eventdate column?  It's been my experience that any table with more
> than a few columns in an index isn't properly normalized.
>
> There might be bug/patch issues, too.  timestamp is relatively new,
> and you are using an unpatched base release.
>
> How are you loading this table?  What is the exact definition of the
> index?
>
> jg
> --
> @home.com is bogus.http://www.spacefem.com/hydrox/

All the indexes on the table only span ONE column, not all! Here's the ddl (i've modified some names, none of the content has been changed):

CREATE TABLE "MYSCHEMA"."EVENTS"
( "EVENTID" NUMBER,
"EVENTDATE" TIMESTAMP(6) NOT NULL ,
"HOSTID" NUMBER NOT NULL ,
"DIRECTION" NUMBER NOT NULL ,
"INTERFACE" NVARCHAR2(17),
"FRAME" NUMBER NOT NULL ,
"SOURCEMAC" NVARCHAR2(17),
"DESTINATIONMAC" NVARCHAR2(17),
"PROTOCOL" NUMBER NOT NULL ,
"FLAGS" NVARCHAR2(64),
"SOURCEIP" NVARCHAR2(15),
"SOURCEPORT" NUMBER NOT NULL ,
"DESTINATIONIP" NVARCHAR2(15),
"DESTINATIONPORT" NUMBER NOT NULL ,
"PACKETSIZE" NUMBER NOT NULL ,
"TYPE" NUMBER NOT NULL ,
"ACTION" NUMBER NOT NULL ,
"ITEMID" NUMBER,
"REPEATCOUNT" NUMBER NOT NULL ,
"ENDEVENTDATE" TIMESTAMP(6),
"RANK" NUMBER,
"HOSTASSETVALUE" NUMBER(5),
"FILTERSEVERITYVALUE" NUMBER(5),

PRIMARY KEY ("EVENTID") VALIDATE )
TABLESPACE "USERS" PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) LOGGING CREATE INDEX "MYSCHEMA"."EVENTS_EVENTDATE" ON
"MYSCHEMA"."EVENTS" ("EVENTDATE") TABLESPACE "USERS" PCTFREE 10
INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) LOGGING LOCAL Then there's a bunch of other indexes, 8 total, with the one above and the one PK, 6 more look similar to the one below:

CREATE INDEX "MYSCHEMA"."EVENTS_ACTION" ON
"MYSCHEMA"."EVENTS" ("ACTION") TABLESPACE "USERS" PCTFREE 10 INITRANS
2 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) LOGGING LOCAL Received on Tue Jan 29 2008 - 08:39:16 CST

Original text of this message