BAD PERF. for single row access with unique index

From: <kerbiquet_at_hotmail.com>
Date: 2000/06/27
Message-ID: <8jaifb$fcd$1@nnrp1.deja.com>#1/1

We have two similar tables, one contains message headers (MESSAGE), the other one contains the text lines of the message (MESSAGETXT).

desc MESSAGE:
DAY (PK col1)
SOURCE (PK col2)
NUMBER (PK col3)
GROUP
+10 additional columns

desc MESSAGETXT:
DAY (PK col1)

SOURCE (PK col2)
NUMBER (PK col3)
NUMTXT (PK col4)

TXT Both tables are partitioned by DAY.
There is a local index on both tables used for PK. There are others indexes on MESSAGE but following SQL statement use unique index.

MESSAGE contains more than 3 millions rows and MESSAGETXT more than 15 millions rows. Each table/index is on a separate tablespaces and datafiles are on same disks (striping).

SELECT COUNT(1) FROM messagetxt
WHERE day='20/05/00'
AND source='A'
AND number=2;

return 5 in 0.09s

SELECT COUNT(1) FROM message
WHERE day='20/05/00'
AND source='A'
AND number=2;

return 1 in 1.34s

Do you have any idea why is it so slow to access one row on MESSAGE even with a UNIQUE INDEX?

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Jun 27 2000 - 00:00:00 CDT