I am facing a curious problem at a customer I am currently at.
They have tables for which they have collected summary statistics only (using COLLECT SUMMARY STATISTICS ON <tablename> ). No column specific statistics, no secundary indexes etc.
When we run a simple query on those 2 tables we get an out of spool error. When I remove the summary statistics the query runs normally without giving the out of spool error.
If I look at the dbqlog_tbl the data shows that the query run on the tables without summary statistics uses around 18Gb of spool, which is fairly ok. The out of spool query runs up to 1 TB of spool before giving the out of spool error.
How can this happen? I thought doing a simple COLLECT SUMMARY STATISTICS would not really even have such a big impact on performance. And certainly not a very negative impact on the query plan. IF all I would expect it would be positive...
Can someone fill me in?