Search My Oracle Blog

Custom Search

01 March, 2014

"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. .
.
.
.

2 comments:

Marko Sutic said...

Hello Hemant,

I think this behavior is expected even with "deferred_segment_creation"=FALSE (if my tests are correct).

From documentation: "Beginning with Oracle Database 11g Release 2, when you make an existing index unusable, its index segment is dropped."
http://docs.oracle.com/cd/E18283_01/server.112/e17120/indexes002.htm#CIHJIDJG

But I might be wrong :)
I've performed tests on 11.2.0.1 version.

Best regards,
Marko

Hemant K Chitale said...

Marko,
You are right. I stand corrected. The behaviour of dropping the segment for the index partition marked UNUSED applies even when deferred_segment_creation=FALSE.

I am updating my post.

Hemant K Chitale

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016