I've got a large backlog of tables with partitions that show stale_stats = yes and I observed than if I just try to create incremental global stats on these tables it doesn't automatically go back and collect the stats on the partitions that are stale. So before this I have a loop over the partitions that are stale and I collects stats on them.
However I'm noticing that it's visiting the same partitions each week this job runs so it seems as though stats are not leaving stale state on these tables.
The tables are partitioned by time and the data in older partitions is not changing except for one use case which is that I'm also compressing partitions with another job but does compressing partitions invalidate stats at all?
These are large tables and the thinking was that I'd use incremental global stats to avoid the full scan of all partitions when collecting global stats.
However just collecting incremental stats does not seem to make any effort to update existing stale partition stats first.