Partitioned TEMP tables

I have an issue regarding partitioned TEMP tables.

I have a database with a number of families of tables partitioned by day as
described in section 5.10 of the User's Manual. I have an empty parent
tables each with a number of child tables containing data partitioned by
date. Each child has a CHECK condition on the date of the data. This works
fine when I execute a SELECT statement against the parent table and specify
a value for the date.

However, I am currently working on a system that requires me to create a
family of TEMP tables with the same setup. So, I have an empty TEMP parent
with each day’s data in a TEMP child.

The thing is that if I try try to run SELECT against the parent table with
the date specified in the WHERE clause, I get terrible performance despite
the fact that an EXPLAIN of the query looks fine. (The query can run for an
hour and I finally give up and kill it.) However, if I specify the child
table directly in the FROM clause, the query runs fine. (It only takes a
few minutes.)

Does partitioning of TEMP tables not work like non-TEMP tables? In the same
query, I access the parent table of a non-TEMP family and that doesn’t
cause problems.

Any idea what’s going on here?
-Ed

*Ed Behn */ Staff Engineer / Airline and Network Services
Information Management Services
2551 Riva Road, Annapolis, MD 21401 USA
Phone: (410)266-4426 / Cell: (240)696-7443
ed(dot)behn(at)rockwellcollins(dot)com

www.rockwellcollins.com