I am facing spool space issue while executing select from a view in QA environment. But same view have no performance issue in Production environment, also having same count in both QA and Prod. I have executed the diagnostic help stats on the view in both environments but having huge difference in the Explain plan. But the structure of both view are same, and only difference I noticed is that few of the underlying objects of this view is have extra foreign key constraint in Production. Does the constraints help to increase the performance? As per my knowledge it will degrade the insert and delete from the object. Please help me to fix this and also if there is any other reason for the different explain plan.
You say that you're having 'spool space issue'.
I assume by this that your query is failing '(2646) No more spool space in **bleep**' error. is that correct?
The first thing to do is to find out if you are truly 'out of spool space' or if this is skewing.
If you can, run your test query from 1 session and ** whilst your test query is running ** run the following quer from a different session.
SELECT SUM(maxspool) AS spool_limit
,SUM(currentspool) AS current_spool
,100.00 * AVG(currentspool) / NULLIF(MAX(currentspool),0) AS spool_pareff
WHERE databasename = 'username';
Note change this query to specify the name of the user logged on in session#1 for the value in 'username' at the end. If both sessions are logged on as the same username then instead of 'username' you can simply use USER (there are no quotes around this.
This will tell you what your space limit is, how much you're using and what the spool space distribution is like.
As to why you might have a difference between QA and Prod (even if row counts are the same).
1) If these are on different Teradata systems then you might have a different spool space limit.
2) You say that the prod environment has some extra FK's defined. That may well mean that the optimiser can use 'join elimination' which could significantly affect the plan.
Start with that and let's take it from there.