Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> bad performing query when joining long table to temp table
Hello Everyone,
Maybe you can help me with this. This is Oracle 8.1.4
I use temp tables to filter data in queries. This is fine for tables that are several thousand rows long.
However, when I use temp tables to filter down tables that are hundreds of thousands of rows long, it takes a long time and produces a bad plan. This is true even if the temp table is empty. If I swap the temp table for a real empty table, I get the no-row result very quickly.
TABLE DESCRIPTION:
tmp is the empty global temporary table (on commit delete rows)
tmp has a compound primary key leading with id
a is 357k rows
b is 502k rows
QUERY:
SELECT
tmp.id
,a.id
,b.id
FROM
temptable tmp
,tablea a
,tableb b
WHERE
tmp.id = a.id1
AND a.id2 = b.id
AND b.field1 = 'a'
PLAN:
Operation Object Name Rows Bytes Cost SELECT STATEMENT Optimizer Mode=CHOOSE 4 M 6389 HASH JOIN 4 M 131 M 6389 INDEX FAST FULL SCAN tmp 2 K 25 K 1 MERGE JOIN 219 K 3 M 4123 SORT JOIN 357 K 3 M TABLE ACCESS FULL a 357 K 3 M 83 SORT JOIN 167 K 1 M 3486 TABLE ACCESS FULL b 167 K 1 M 554
Now SWAP the TMP with a real empty table
TABLE DESCRIPTION:
c is 0 rows
a is 357k rows
b is 502k rows
idx_a is nonunique index on id1
pk_c is primary key on c.id
pk_b is primary key on b.id
QUERY:
SELECT
c.id,b.id
,a.id
c.id = a.id1 AND a.id2 = b.id AND b.field1 = 'a' PLAN: Operation Object Name Rows Bytes Cost SELECT STATEMENT Optimizer Mode=CHOOSE 20 6 NESTED LOOPS 20 620 6 NESTED LOOPS 32 736 2 INDEX FULL SCAN pk_c 1 13 TABLE ACCESS BY INDEX ROWID a 357 K 3 M 1 INDEX RANGE SCAN idx_a 357 K TABLE ACCESS BY INDEX ROWID b 167 K 1 M 1 INDEX UNIQUE SCAN pk_b 167 K
Thanks for any responses
Vincent
Received on Thu Nov 25 2004 - 19:44:33 CST