0

I have a requirement to drop multiple partitions for a table every month. The table has been partitioned based on the year and month and has partitioned local indexes 1.since the indexes are local ,I believe they get dropped with the partition and no other action (rebuild index) is required .the table doesnt have any global index 2.Should gather stats be called after every drop partition or is it okay to do that after all the partitions are dropped(4/5 at a time)

user9668693
  • 11
  • 4
  • Your assumption is correct. Local indexes get dropped with their partition. No further action is needed. – Wernfried Domscheit Apr 14 '23 at 20:34
  • Ditto. And I wouldn't bother gathering stats every time. Eventually you'll want to regather global stats but if you have a regular pase of new data coming in and old data being dropped, the global stats don't change much other than the max value of any surrogate keys or date columns that keep increasing. For those synopses should take care of it. – Paul W Apr 15 '23 at 00:27

1 Answers1

0

Local indexes are bound to the table partitions, so if you drop a table partition the corresponding local index partition evaporates along with it.

Similarly the stats at partition level will be fine, because the remaining partitions retain all the metadata associated with them.

Global stats are always worth taking a look at after a maintenance operation. Newer releases of Oracle will automatically try to make a reasoned assessment of the new global stats, eg

SQL>  create  table t ( x int, y int ) partition by list (x )
  2   ( partition p1 values (1), partition p2 values (2));

Table created.

SQL>  insert into t values (1,1);

1 row created.

SQL>  insert into t values (2,2);

1 row created.

SQL> exec dbms_stats.gather_table_stats('','T')

PL/SQL procedure successfully completed.

SQL> @tab
Enter value for table_name: t

TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN  CHAIN_CNT
------------------------------ ---------- ---------- ------------ ----------- ----------
T                                       2       2012            0           6          0

SQL> alter table t drop partition p1;

Table altered.

SQL> @tab
Enter value for table_name: t

TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN  CHAIN_CNT
------------------------------ ---------- ---------- ------------ ----------- ----------
T                                       1       1006            0           6          0

SQL>

but things like distinct keys, column level stats etc depend a lot on what kind of stats you are collecting, whether you have synopses etc

Connor McDonald
  • 10,418
  • 1
  • 11
  • 16