When I tried to run the belong query it showed a spool space issue. However, I am able to run the select part. I have collected all the stats that are required and tried but no use. When gone through the explain plan it shows 15minutes to merge all-AMPs into Target table. Please help me in this.
Are the PI values for the target (looks like EDW_OFFSHORE_BASE.ISA.SVC_ACCT_NUM) highly skewed?
Even if not skewed, the redistribution by target PI to prepare for the MERGE step will require enough spool space to hold two copies of the data. The optimizer is estimating 3.5 million rows will occupy 4.7GB. If the real cardinality is 68 million then the real size of the spool file would be almost 20x that value.
Thanks for replying.
The PI for INV_SVC_ACCT is SVC_ACCT_SK (Surrogate Key). The target table PI was TLFR_CORP_ID but now it is changed to TLFR_NUM which is being populated from TOLL_FREE_LINE table.
The code is still simplified by the use of different temp tables that now holds the data after using common joins (like ACCT_DTL which holds data related to account like address, contact etc.,.). I also wrote two inserts which in turn will try to load the data in smaller chunks (i.e., 25 million on an avg). But still I face this spool issue at one insert (the one which has to load 25 million records).
Now, Overall record count should be 43 million.
as Fred already wrote, you should check if SVC_ACCT_NUM is highly skewed (which will, btw, also result in a skewed target table):
Just remove all other columns from your query and run
SELECT ISA.SVC_ACCT_NUM AS "TOLL FREE CORP ID", count(*)
group by 1
order by 1 desc
i have a query . In that left join is costing more and its showing 8 min in plan.
One of the joining column is highly skewed with a single value.
can anybody suggest how can i make that join work fast.
Sorry, was held up with something else. Yes, there was skew for that. I have dropped the target table and then recreated it with TLFR_NUM column which was near to Unique values. The query ran fine.
Thanks a lot for all the suggestions.