In my recent research into Oracle's CBO, I had read a snippet from Jeff Moss' blog about constraints being particularly extra helpful to the CBO. Tom Kyte had two consecutive blog posts about this topic in the beginning of the year. The first applies to general purpose and OLTP databases, whereas the second is geared specifically towards data warehouses using materialized views.

Tom even breaks out the RELY hint to enable the use of constraints on large warehouse tables so they won't kill bulk load times. I'll see if I can try to implement this on our warehouse side. Unfortunately, now that we have a handle on the previous load/CBO problems, I have a priority java app that I need to get done before I can get too crazy with Oracle.