I have to add dynamic partitioning/subpartitioning fucntionality to my warehouse application
where i may need to split default subpartition or add new partition.
These partitions may or may not contain data.
Indexes used are non partitioned global.
Is it recommended to call update stats after the dynamically adding /splitting subpartitions?
Thanks in advance
When you split partitions, the new partitions do not have stats, eg
SQL> select partition_name, num_rows from user_tab_partitions
2 where table_name = 'T1';
PARTITION_NAME NUM_ROWS
------------------------------ ----------
P1 19551
SYS_P519 12716
SYS_P520 19982
SYS_P521 20000
SYS_P522 19973
5 rows selected.
SQL> alter table t1 split partition SYS_P521 at ( 50000) into ( partition px, partition py );
Table altered.
SQL> select partition_name, num_rows from user_tab_partitions
2 where table_name = 'T1';
PARTITION_NAME NUM_ROWS
------------------------------ ----------
P1 19551
PX
PY
SYS_P519 12716
SYS_P520 19982
SYS_P522 19973
6 rows selected.
SQL>
So you'll need to take that into account. That does not *always* mean you'd need to regather them. If you *know* the data in them, you can possibly just the appropriate "set_xxx_stats" procedures to quickly put some representative stats in.