what happens to partitioned local indexes after a table partition is dropped oracle

Question

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)

Answer 1

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