Re: BAD PERF. for single row access with unique index

I don't think it's such a hot idea to use field names which are reserved words in the environment you are working.

Doesn't help with your performance problems, but couldn't help throwing in my 2 cents worth.

<kerbiquet_at_hotmail.com> wrote in message news:8jaifb$fcd$1_at_nnrp1.deja.com...
> 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 Thu Jun 29 2000 - 00:00:00 CDT