For some Tables I am using Analytical functions extensively. For small data queries looks good. But when data will be big I will have to give serious thoughts for stats collection. Are there any guidelines for columns to consider for stat collection which will help in analytical SQL?
Good question! In general I found the analytical function approach to be faster for rolling sums. But with the statistics collection? Maybe collect stats on the group by columns. But that is from a theoretical approach, not seen in reality. Dnouth writes analytical functions as if he is a novel writer, so let's see what he is going to say...