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

Follow the advice from David Fitzjarrell, but at first glance I would be prepared to guess that your problem is the clause

    >WHERE day='20/05/00'

For partition elimination to occur, Oracle usually needs to see dates with a full
4-digit year. The overheads of accessing all the partitions, even through the primary key, may be sufficient to push your access times up.

Try

    >WHERE day=to_date('20/05/2000','dd/mm/yyyy')

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

kerbiquet_at_hotmail.com wrote in message <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.