Join performance with temporary table

Is there some way to improve the performance of queries that include a join with a temporary table?

Let A be a traditional table with about 40K rows. Let B be a traditional table with about 10K rows. Let C be a temporary table (session-specific) with about 10K rows. Let D be a result table.

B and C have have the same schema and data.

The query is:
  insert into D
  select A.key, MIN(A.data)
  from A, X
  where A.key = X.key
  group by A.key

X is either B or C.

When the query is run with X as B, the plan is

INSERT STATEMENT                 1158
  SORT GROUP BY                  1158
    NESTED LOOPS                  122
      TABLE ACCESS FULL    A      122
      INDEX UNIQUE SCAN    B_pk

It executes in 8.43 seconds.

When the query is run with X as C, the plan is

INSERT STATEMENT                 2928
  SORT GROUP BY NOSORT           2928
    MERGE JOIN                   2928
      INDEX FULL SCAN      C_pk    26
      SORT JOIN                  2780
        TABLE ACCESS FULL  A      122

It executes in 33.87 seconds.

After the data was inserted into both B and C, I ran an 'ANALYZE TABLE X COMPUTE STATISTICS' for X being B, C before executing either query above.

As far as I can tell, the only difference between B and C is that B is a normal table and C is a temporary one; they have the same schema and the same data. Yet the optimizer seems to choose a much better plan for the normal
table than the temporary one.

Any suggestions?

• Posted via NewsOne.Net: Free (anonymous) Usenet News via the Web ----- http://newsone.net/ -- Free reading and anonymous posting to 60,000+ groups NewsOne.Net prohibits users from posting spam. If this or other posts made through NewsOne.Net violate posting guidelines, email abuse_at_newsone.net