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

Thank you very much for your help,

Actually were are using WHERE day=to_date('20/05/2000','dd/mm/yyyy') I think partition elimination occured but can not understand why unique index do not give good response time. Please have a look on my answer to David Fitzjarrell.

Regards,

In article <962136676.24296.3.nnrp-11.9e984b29_at_news.demon.co.uk>,   "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
>
> 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.
>
>

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