Hi TOM

I have a table; lots of records get inserted every minute. There is a column on the table that we called ProcessFlag. On insert, the value is set to N. We also have another column that is just a sequence number; the value gets incremented for each new record as we use a sequence to populate it.
And then we have a script that reads this table looking for records with ProcessFlag equals to N (order by sequence column).
For each of these records, our plsql script will do some processing and update ProcessFlag to Y.
Records with ProcessFlag set to Y are no longer of interest and I would like to be able to easily purge them out.
This table grows rapidly and now it is at over 200G in size. (we purge every few months ... currently, have to stop application).
I am thinking of using interval partitioning on a date/time column .. maybe by month. I think I am ok on the table partitioning.
However, I am not sure what kind of index (local/global) to choose for the column ProcessFlag.

If I choose global index, then I will not be able to quickly drop the table partition.

If I choose local index, my table will have a few partitions, and I am worried the sql statement to get records with ProcessFlag equals to N will take longer as the indexes for all partitions will have to be scanned.
Hope to get some advice from the experts here.
Thanks.

create table t ( c1 primary key, c2, c3, c4, c5 ) 
partition by range ( c1 ) 
interval ( 100000 ) (
  partition p0 values less than ( 100001 ),
  partition p1 values less than ( 200001 )
) as 
  select level c1, 
         date'2020-01-01' + level, 
         timestamp'2020-01-01 00:00:00' + level, 
         rpad ( 'stuff', 50, 'f' ) || level,
         rpad ( ' more stuff', 50, 'f' ) || level
  from   dual
  connect by level <= 1000000;
  
create index i2
  on t ( c2 );
create index i3
  on t ( c3 );
create index i4
  on t ( c4 );
create index i5
  on t ( c5 );

set timing on
alter table t
  drop partition p0 
  update indexes;

Table T altered.

Elapsed: 00:00:12.011

set timing on
alter table t
  drop partition p0 
  update indexes;

Table T altered.

Elapsed: 00:00:00.07