bad performing query when joining long table to temp table

From: Vincent <v_c_at_yahoo.com>
Date: 25 Nov 2004 17:44:33 -0800
Message-ID: <23edd8ed.0411251744.7815d783@posting.google.com>

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


  ,a.id     

,b.id

  FROM
   tablec c
,tablea a
,tableb b

  WHERE

      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