| Thanks for your suggestions.
Some answers to your questions:
How many rows are in the shipment table in total?
171742
How many are in the specific domain you are testing the query in?
169177
Do your shipments all contain one line item each or does it vary from 1 - 20?
Varies a lot, mostly in range between 1 and 50, but some with even more (I assume you mean s_ship_unit_line counts; most or all s_ship_units have only one line each, I believe).
Will the report be domain specific or will it be installed in the public domain to be run by any domain that wants it? The report is specific to the domain at present, but new domains will probably be created soon that will need this report.
The timings that I did were probably crude--I just used sqlplus "set timing on" and ran the query and then executed vpd.set_user('TMS.ADMIN') and ran the query again.
I do not see a better way of tying shipments to orders (I agree that the view is a resource pig). I guess it would be too difficult for GC3 to maintain a simple order-shipment crossref table, since the relationships can be so complex. Too bad. But the fact is, even with the view, the query returns pretty quickly as long as a (non-DBA.ADMIN) VPD profile is not applied.
Basically. the saved query as shown represents the information that the users need (including datetime constraints), and apparently the VPD profile logic does not work well with the view (or even the inline view query, which I also tried).
Do you know exactly how the VPD profiles change the behavior and/or contents of the query sql? Also, I am not much of a SQL guru. For the query shown above, what hint(s) would you suggest?
Thanks again.  |