Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Looking for advice on solving performance problem
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
![]() |
![]() |