Wrong results

One of our users raised a support call with my team – a query returned the wrong results. My guys check it out and capture the explain plan… yes wrong results, and because the lower bound of a BETWEEN prediacte was being omitted. So a TAR is raised with support – the captured plan is filed with the TAR, support can see there is a problem

Back comes the phrase most DW managers hate to hear from support: “Can you prepare a simple test case?”

Test cases are not trivial on a 2.5TB DW. This query joins to a half a dozen dimension tables, the fact table has 116 partitions and tens of millions of rows. Looking at the query plan we are using star transforms into temporary tables, bitmap indexes and parallel query. To further complicate the set-up the fact table is also a materialized view and registered for query re-write. It’ll take a while to reduce that to something portable and still showing the problem.

UPDATE Following Tom Kyte's suggestion to create an empty test case + exported stats I got a working test case out to support in just a few hours, I included the minimum data to show the problem (three rows!) If you do export stats watch out for the user id in the exported table - the DBMS_STATS.IMPORTxx procedure tries to use the same user as the original export.

By the way, the problem is not a dropped predicate but a missing bound in the partition iterator