| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Looking for advice on solving performance problem
A copy of this was sent to Calvin Crumrine <Calvin_Crumrine_at_dced.state.ak.us>
(if that email address didn't require changing)
On Tue, 03 Aug 1999 14:22:54 -0800, you wrote:
>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.
because of the indexes you have and the nasty way you are joining.
The 1'st and 3'rd query cannot use any of the indexes on these tables (created for the primary keys). All three tables are full scanned, sorted and merged together.
the 2'cnd query lets us use an index, on table2, since field1 is on the leading edge of that index. field1 must not be very selective though (returns lots of data). What is happening is that we use the index to read table2 and for each row we find in table2, we do a full scan on table3 to find the matches, then we merge that result with a sorted table1.
The following shows how to get the plans for these various queries and also shows that you really should consider rewriting the view to use joins on columns -- NOT on concatenated columns. I believe the rewritten view (below) might outperform in all three cases.
SQL> Create table Table1 (Field1 Number(2), Field2 Number(3), FieldX
VarChar2(50),
2 Constraint Table1_PK Primary Key (Field1, Field2));
Table created.
SQL>
SQL> Create table Table2 (Field1 Number(2), Field2 Number(3), Field3 Number(7),
2 Constraint Table2_PK Primary Key (Field1, Field2, Field3));
Table created.
SQL>
SQL> Create table Table3 (Field1 Number(2), Field2 Number(3), Field3 Number(7),
2 Constraint Table3_PK Primary Key (Field1, Field2, Field3));
Table created.
SQL>
SQL> Create or replace View Table123 as select b.field1 Field1, b.field2 Field2,
b.field3 Field3,
2 a.fieldX FieldX
3 from table1 a, table2 b, table3 c
4 where a.field1 || a.field2 = b.field1 || b.field2
5 and b.field1 || b.field2 || b.field3 = c.field1 || c.field2 || c.field3;
View created.
SQL> SQL> set autotrace on explain SQL> Select * from Table123;
no rows selected
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE 1 0 MERGE JOIN
2 1 SORT (JOIN) 3 2 MERGE JOIN 4 3 SORT (JOIN) 5 4 TABLE ACCESS (FULL) OF 'TABLE3' 6 3 SORT (JOIN) 7 6 TABLE ACCESS (FULL) OF 'TABLE2' 8 1 SORT (JOIN) 9 8 TABLE ACCESS (FULL) OF 'TABLE1'
SQL> Select * from Table123 where field1=5;
no rows selected
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE 1 0 MERGE JOIN
2 1 SORT (JOIN) 3 2 NESTED LOOPS 4 3 TABLE ACCESS (FULL) OF 'TABLE3' 5 3 INDEX (RANGE SCAN) OF 'TABLE2_PK' (UNIQUE) 6 1 SORT (JOIN) 7 6 TABLE ACCESS (FULL) OF 'TABLE1'
SQL> Select * from Table123 where field2=10;
no rows selected
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE 1 0 MERGE JOIN
2 1 SORT (JOIN) 3 2 MERGE JOIN 4 3 SORT (JOIN) 5 4 TABLE ACCESS (FULL) OF 'TABLE3' 6 3 SORT (JOIN) 7 6 TABLE ACCESS (FULL) OF 'TABLE2' 8 1 SORT (JOIN) 9 8 TABLE ACCESS (FULL) OF 'TABLE1' SQL> set autotrace off SQL>
View created.
SQL> SQL> set autotrace on explain SQL> Select * from Table123;
no rows selected
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE 1 0 NESTED LOOPS
2 1 NESTED LOOPS 3 2 TABLE ACCESS (FULL) OF 'TABLE3' 4 2 INDEX (UNIQUE SCAN) OF 'TABLE2_PK' (UNIQUE) 5 1 TABLE ACCESS (BY ROWID) OF 'TABLE1' 6 5 INDEX (UNIQUE SCAN) OF 'TABLE1_PK' (UNIQUE)
SQL> Select * from Table123 where field1=5;
no rows selected
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE 1 0 NESTED LOOPS
2 1 NESTED LOOPS 3 2 INDEX (RANGE SCAN) OF 'TABLE2_PK' (UNIQUE) 4 2 INDEX (UNIQUE SCAN) OF 'TABLE3_PK' (UNIQUE) 5 1 TABLE ACCESS (BY ROWID) OF 'TABLE1' 6 5 INDEX (UNIQUE SCAN) OF 'TABLE1_PK' (UNIQUE)
SQL> Select * from Table123 where field2=10;
no rows selected
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE 1 0 NESTED LOOPS
2 1 NESTED LOOPS 3 2 TABLE ACCESS (FULL) OF 'TABLE3' 4 2 INDEX (UNIQUE SCAN) OF 'TABLE2_PK' (UNIQUE) 5 1 TABLE ACCESS (BY ROWID) OF 'TABLE1' 6 5 INDEX (UNIQUE SCAN) OF 'TABLE1_PK' (UNIQUE)
SQL> set autotrace off
SQL>
--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Aug 04 1999 - 08:38:18 CDT
 |
 |