McCormick
09-30-2005, 12:48 PM
I am using Report Builder 3.0.5 under Oracle 8.1.7
I have a Multi Query data model that I wish to execute in a certain
manner.
The Parent Query creates a sorted list of customer IDs based on the
account's status and last activity date. The single table involved is
huge, 22 million records, and has no index that covers all the columns
in the query. Running this query in SQL*Plus creates a list of ~144,000
records in about 20 minutes, which is acceptable.
The Child Query takes the customer ID and sums a small number of
records. It also references a single huge table, 200 million records,
but has a good index and given the customer ID is very fast.
I have a Group to Group link between these queries with the customer ID
returned in the Parent Query referenced in the WHERE clause of the
Child Query.
What I would like the report to do is build the initial list of 144,000
customer ID's in the Parent Query before it touches the Child Query. I
"saw" the report do this once. A large initial delay followed by rapid
page creation. Now, though, it drags through page by page as if Reports
has "helpfully" combined the two queries into a giant resource sucking
statement.
How can I encourage the Parent Query to completely execute before the
Child Query?
Thanks!
Mike McCormick
I have a Multi Query data model that I wish to execute in a certain
manner.
The Parent Query creates a sorted list of customer IDs based on the
account's status and last activity date. The single table involved is
huge, 22 million records, and has no index that covers all the columns
in the query. Running this query in SQL*Plus creates a list of ~144,000
records in about 20 minutes, which is acceptable.
The Child Query takes the customer ID and sums a small number of
records. It also references a single huge table, 200 million records,
but has a good index and given the customer ID is very fast.
I have a Group to Group link between these queries with the customer ID
returned in the Parent Query referenced in the WHERE clause of the
Child Query.
What I would like the report to do is build the initial list of 144,000
customer ID's in the Parent Query before it touches the Child Query. I
"saw" the report do this once. A large initial delay followed by rapid
page creation. Now, though, it drags through page by page as if Reports
has "helpfully" combined the two queries into a giant resource sucking
statement.
How can I encourage the Parent Query to completely execute before the
Child Query?
Thanks!
Mike McCormick