Is it possible to collect column statistics for a specific partition?
Our situation as follows:
The weekly collect stats job (runs on Friday) is working fine for all queries which accesses data inserted/modified before the collect stats job, which is expected.
However, queries access new data inserted in the days after the collect stats job (sat, sun, … ) use FTS which is also expected as the optimizer is not aware of the data distribution for this new data.
The job itself takes fairly long time (30+ min) so it’s not practical for us to run it after COB each day.
DO we have other alternative, can we collect column stats at a single partition level?
There's no stats on a single partition.
But instead of collecting all stats on that table you might just recollect some.
The table is probably partitioned by date, so just collect on the partitioning column and on the pseudo-column PARTITION.
And if you're on TD13 you might also change the date stats to SAMPLE.
The optimizer goes for a stats extrapolation based on the available histograms. So if the table is not Skewed & have a proper data distribution it will help optimizer to generate a good plan. So I fully agree with Deiter that you can collect stats on sample.