"Dropping" an Index Partition

Here is a simple way to "drop" an Index Partition  using the 11.2 behaviour of DEFERRED_SEGMENT_CREATION  by dropping the segment for the Index Partition.

CORRECTION : Although I posted this as DEFERRED_SEGMENT_CREATION=TRUE behaviour, Marko has pointed out that it applies even when D_S_C is set to FALSE.  So I am posting two test cases, one with D_S_C set to TRUE, and one with it set to FALSE.

This is the first case with DEFERRED_SEGMENT_CREATION=TRUE

HEMANT>show parameter deferre

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     TRUE
HEMANT>
HHEMANT>l
  1  create table sales_history
  2      (sale_date date, product_id number, customer_id number, quantity number, price number, remarks varchar2(125))
  3      partition by range (sale_date)
  4      (partition P_2010 values less than (to_date('01-JAN-2011','DD-MON-YYYY')),
  5       partition P_2011 values less than (to_date('01-JAN-2012','DD-MON-YYYY')),
  6       partition P_2012 values less than (to_date('01-JAN-2013','DD-MON-YYYY')),
  7       partition P_2013 values less than (to_date('01-JAN-2014','DD-MON-YYYY')),
  8       partition P_2014 values less than (to_date('01-JAN-2015','DD-MON-YYYY')),
  9       partition P_2015 values less than (to_date('01-JAN-2016','DD-MON-YYYY')),
 10       partition P_MAX values less than (MAXVALUE)
 11* )
HEMANT>/

Table created.

HEMANT>
HEMANT>l
  1  create bitmap index sales_history_prdct_ndx
  2  on sales_history(product_id)
  3* local
HEMANT>/

Index created.

HEMANT>
HEMANT>select segment_name, partition_name    
  2  from user_segments
  3  where segment_type = 'INDEX PARTITION'
  4  and segment_name = 'SALES_HISTORY_PRDCT_NDX'
  5  /

no rows selected

HEMANT>

Since deferred_segment_creation is set to TRUE, no index partition segments exist till data is loaded.

HEMANT>l
  1  insert into sales_history
  2       select to_date('01-JUL-2010','DD-MON-RR')+ (rownum*365), mod(rownum,5)+1,mod(rownum,1000)+1, 100, 12,'Sale Done'
  3       from dual
  4*      connect by level < 5
HEMANT>/

4 rows created.

HEMANT>
HEMANT>l
  1  select segment_name, partition_name
  2  from user_segments
  3  where segment_type = 'INDEX PARTITION'
  4* and segment_name = 'SALES_HISTORY_PRDCT_NDX'
HEMANT>/

SEGMENT_NAME                   PARTITION_NAME
------------------------------ ------------------------------
SALES_HISTORY_PRDCT_NDX        P_2011
SALES_HISTORY_PRDCT_NDX        P_2012
SALES_HISTORY_PRDCT_NDX        P_2013
SALES_HISTORY_PRDCT_NDX        P_2014

HEMANT>

So, now 4 index partitions are populated for the 4 rows in the 4 table partitions.

What happens when I make an Index Partition UNUSABLE ?

HEMANT>commit;

Commit complete.

HEMANT>alter index sales_history_prdct_ndx modify partition P_2011 unusable;

Index altered.

HEMANT>
HEMANT>l
  1  select segment_name, partition_name
  2  from user_segments
  3  where segment_type = 'INDEX PARTITION'
  4* and segment_name = 'SALES_HISTORY_PRDCT_NDX'
HEMANT>/

SEGMENT_NAME                   PARTITION_NAME
------------------------------ ------------------------------
SALES_HISTORY_PRDCT_NDX        P_2012
SALES_HISTORY_PRDCT_NDX        P_2013
SALES_HISTORY_PRDCT_NDX        P_2014

HEMANT>

The corresponding Index Partition Segment has also "disappeared".  I have released the space that was used by the Index Partition without actually deleting rows from the table.
This is possible with deferred_segment_creation set to TRUE.

Next is the test case with DEFERRED_SEGMENT_CREATION set to FALSE.
Here you will notice that Partitions with zero rows (i.e. those that are empty) still have Segments -- thus all the Partitions are created upfront even in the absence of rows in the table (and index).

HEMANT> show parameter deferred

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     FALSE
HEMANT>
HEMANT> create table transactions_history
  2  (txn_id number, txn_date date, txn_product_id number, txn_value number, remarks varchar2(50))
  3  partition by range (txn_date)
  4  (partition P_2010 values less than (to_date('01-JAN-2011','DD-MON-YYYY')),
  5           partition P_2011 values less than (to_date('01-JAN-2012','DD-MON-YYYY')),
  6           partition P_2012 values less than (to_date('01-JAN-2013','DD-MON-YYYY')),
  7           partition P_2013 values less than (to_date('01-JAN-2014','DD-MON-YYYY')),
  8           partition P_2014 values less than (to_date('01-JAN-2015','DD-MON-YYYY')),
  9           partition P_2015 values less than (to_date('01-JAN-2016','DD-MON-YYYY')),
 10          partition P_MAX values less than (MAXVALUE)
 11  )
 12  /

Table created.

HEMANT>
HEMANT> create bitmap index txn_hist_prdct_id on
  2  transactions_history(txn_product_id)
  3  local
  4  /

Index created.

HEMANT>
HEMANT> select segment_name, partition_name
  2  from user_segments
  3  where segment_name = 'TXN_HIST_PRDCT_ID'
  4  and segment_type = 'INDEX PARTITION'
  5  order by 2
  6  /

SEGMENT_NAME                   PARTITION_NAME
------------------------------ ------------------------------
TXN_HIST_PRDCT_ID              P_2010
TXN_HIST_PRDCT_ID              P_2011
TXN_HIST_PRDCT_ID              P_2012
TXN_HIST_PRDCT_ID              P_2013
TXN_HIST_PRDCT_ID              P_2014
TXN_HIST_PRDCT_ID              P_2015
TXN_HIST_PRDCT_ID              P_MAX

7 rows selected.

HEMANT>
HEMANT> insert into transactions_history
  2  select rownum, to_date('01-JUL-2010','DD-MON-RR')+ (rownum*365), mod(rownum,5)+1,rownum*100,'Txn Done'
  3  from dual
  4  connect by level < 5
  5  /

4 rows created.

HEMANT>
HEMANT> select segment_name, partition_name
  2  from user_segments
  3  where segment_name = 'TXN_HIST_PRDCT_ID'
  4  and segment_type = 'INDEX PARTITION'
  5  order by 2
  6  /

SEGMENT_NAME                   PARTITION_NAME
------------------------------ ------------------------------
TXN_HIST_PRDCT_ID              P_2010
TXN_HIST_PRDCT_ID              P_2011
TXN_HIST_PRDCT_ID              P_2012
TXN_HIST_PRDCT_ID              P_2013
TXN_HIST_PRDCT_ID              P_2014
TXN_HIST_PRDCT_ID              P_2015
TXN_HIST_PRDCT_ID              P_MAX

7 rows selected.

HEMANT>
HEMANT> alter index txn_hist_prdct_id modify partition P_2011 unusable;

Index altered.

HEMANT>
HEMANT> select segment_name, partition_name
  2  from user_segments
  3  where segment_name = 'TXN_HIST_PRDCT_ID'
  4  and segment_type = 'INDEX PARTITION'
  5  order by 2
  6  /

SEGMENT_NAME                   PARTITION_NAME
------------------------------ ------------------------------
TXN_HIST_PRDCT_ID              P_2010
TXN_HIST_PRDCT_ID              P_2012
TXN_HIST_PRDCT_ID              P_2013
TXN_HIST_PRDCT_ID              P_2014
TXN_HIST_PRDCT_ID              P_2015
TXN_HIST_PRDCT_ID              P_MAX

6 rows selected.

HEMANT>
HEMANT> select *
  2  from transactions_history
  3  order by txn_date
  4  /

    TXN_ID TXN_DATE  TXN_PRODUCT_ID  TXN_VALUE
---------- --------- -------------- ----------
REMARKS
--------------------------------------------------
         1 01-JUL-11              2        100
Txn Done

         2 30-JUN-12              3        200
Txn Done

         3 30-JUN-13              4        300
Txn Done

         4 30-JUN-14              5        400
Txn Done


HEMANT>

Here we see that setting the P_2011 Partition UNUSABLE resulting it in being dropped. The segments for Partitions P_2010, P_2015 and P_MAX persist even though they are empty. .
.
.
.