Probably the quickest (and easiest) way to improve performance is to cut out any of the UNIONS that you actually aren't using. The easiest way to identify these is to edit the source code of the view and;
- Add a column called "block_number" to each block with the number of that block,
- Remove the GROUP BY clauses (this will just improve performance for what we are about to do),
- Wrap the modified view SQL with the query "SELECT DISTINCT block_number FROM ( ... )"
- Execute the query, and note the time it takes
|Result of SQL|
So now we have two query blocks 3,5 and that we think we can remove.
As a sanity check I commented out blocks 3 and 5 in the SQL we had modified with the block numbers and re-ran it to see if we got the same results (and performance improved). Sure enough the results were the same and the query returned in 1:26 (down from 5:29) - of course some of this will be due to the intelligent optimiser having kept results of the previous run but still it's an encouraging sign.
The next step is to identify the Noetix query blocks that we can "comment out", to do this we need to look in the N_View_Query_Templates table and see how the view is currently configured;
from n_view_query_templates n
where n.view_label = 'GMF_SLA_Cost_Subledger'
If you look at the VIEW_COMMENT column there is some useful information (from Noetix) regarding what each union is used for. Looking at blocks 3 and 5 the comments are RMA Transactions, and Drop Shipment Transactions - which makes sense as we use neither.
Now to remove the queries from the view we need to create a simple script we can call from WNOETXU2.sql such as;
UPDATE N_View_Query_Templates N
SET N.product_version = '9',
N.last_updated_by = 'A Pellew',
N.last_update_date = TO_DATE('28-FEB-2012')
WHERE 1 = 1
AND N.view_label = 'GMF_SLA_Cost_Subledger'
AND N.query_position IN (3, 5)
Maybe future versions of Noetix will do this sort of performance turning automatically.