Looking for advice on solving performance problem

From: Calvin Crumrine <Calvin_Crumrine_at_dced.state.ak.us>
Date: Tue, 03 Aug 1999 14:22:54 -0800
Message-ID: <37A76BBE.F8E5EC14@dced.state.ak.us>

Using Oracle Server 7.3.4 we have a view joining 3 tables with multi-column primary keys. When selecting on one column of the key we get good response but when selecting on another we don't. Example:

Create Table1 (Field1 Number(2), Field2 Number(3), FieldX VarChar2(50)
Constraint Table1 PK Primary Key (Field1, Field2));
Create Table2 (Field1 Number(2), Field2 Number(3), Field3 Number(7), 

Constraint Table2_PK Primary Key (Field1, Field2, Field3)); Create Table3 (Field1 Number(2), Field2 Number(3), Field3 Number(7), Constraint Table3_PK Primary Key (Field1, Field2, Field3));

Create View Table123 as select b.field1 Field1, b.field2 Field2, b.field3 Field3, a.fieldX FieldX
from table1 a, table2 b, table3 c
where a.field1 || a.field2 = b.field1 || b.field2 and b.field1 || b.field2 || b.field3 = c.field1 || c.field2 || c.field3;

Select * from Table123; returns 4400 rows in approx. 15 seconds.
Select * from Table123 where Field1=Y returns 25 rows in approx. 15 minutes.
Select * from Table123 where Field2=Z returns 400 rows in approx. 15 seconds.

Question: Why does selection on Field1 take so much longer? It's not contention because nobody else is using the database. Received on Tue Aug 03 1999 - 17:22:54 CDT