Re: long running select min(timestamp) query
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