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

In article <8jaifb$fcd$1_at_nnrp1.deja.com>,   kerbiquet_at_hotmail.com wrote:
> 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.
>

You should run 'explain plan' on the queries to determine the optimizer path Oracle is taking:

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

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

The run the following query to display the results:

set pagesize 0 verify off linesize 132
select decode(id,0,'',

        lpad(' ', 2*(level -1))||level||'.'||position)||' '||    operation||' '||options||' '||object_name||' '||    object_type||' '||
   decode(id,0,'Cost = '||cost) Query_plan from plan_table
connect by prior id = parent_id
and statement_id = '&1'
start with id = 0 and statement_id = '&1' /

Pass in the appropriate statement_id, i.e., message or messagetxt, to display the associated query plan:

Enter value for 1: message

(query plan displayed)

/
Enter value for 1: messagetxt

(query plan displayed)

This should assist you in determining what Oracle is doing to process the queries and what, if anything, you might want to change to obtain the desired behavior.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.