Thanks for the question, Rajeshwaran.

Asked: October 23, 2017 - 8:10 am UTC

Last updated: October 24, 2017 - 7:51 am UTC

Version: 12.2

Viewed 10K+ times! This question is

You Asked

Team,

Was reading through this documentation.
http://docs.oracle.com/database/122/SQLRF/CREATE-INDEX.htm#GUID-1F89BBC0-825F-4215-AF71-7588E31D8BFE__BABCHJDH
<quote>
When an index, or some partitions or subpartitions of an index, are created UNUSABLE, no segment is allocated for the unusable object. The unusable index or index partition consumes no space in the database.
</quote>

How ever the quote holds good only for a LOCALLY partitioned index and not for a Globally partitioned index and non-partitioned index.

demo@ORA12C> create table t
  2    partition by range( created_dt )
  3    ( partition p2013 values less than
  4       ( to_date('01-jan-2014','dd-mon-yyyy') ) ,
  5     partition p2014 values less than
  6       ( to_date('01-jan-2015','dd-mon-yyyy') ) ,
  7     partition pmax values less than (maxvalue)         )
  8    as
  9    select a.* ,
 10       to_date('01-Jan-2013','dd-mon-yyyy') +
 11       mod(rownum,1825) created_dt
 12    from all_objects a ;

Table created.

demo@ORA12C>   create index t_nonpart_idx on t(object_id);

Index created.

demo@ORA12C> create index t_globalpart_idx on t(object_id,owner)
  2    global partition by hash(object_id) (
  3       partition p1,
  4       partition p2 ) ;

Index created.

demo@ORA12C> create index t_local_idx on t(object_name) local;

Index created.

demo@ORA12C> column index_name format a20
demo@ORA12C> column partition_name format a10
demo@ORA12C> column segment_name format a20
demo@ORA12C>
demo@ORA12C> select index_name,status
  2   from user_indexes
  3   where index_name = 'T_NONPART_IDX';

INDEX_NAME           STATUS
-------------------- --------
T_NONPART_IDX        VALID

demo@ORA12C>
demo@ORA12C> select index_name,partition_name,status
  2  from user_ind_partitions
  3  where index_name in ('T_GLOBALPART_IDX',
  4    'T_LOCAL_IDX');

INDEX_NAME           PARTITION_ STATUS
-------------------- ---------- --------
T_GLOBALPART_IDX     P1         USABLE
T_GLOBALPART_IDX     P2         USABLE
T_LOCAL_IDX          P2013      USABLE
T_LOCAL_IDX          P2014      USABLE
T_LOCAL_IDX          PMAX       USABLE

demo@ORA12C>
demo@ORA12C> select segment_name,partition_name,extents
  2    from user_segments
  3    where segment_name in ( 'T_NONPART_IDX' ,
  4      'T_GLOBALPART_IDX' , 'T_LOCAL_IDX' )
  5    order by segment_name,partition_name nulls first;

SEGMENT_NAME         PARTITION_    EXTENTS
-------------------- ---------- ----------
T_GLOBALPART_IDX     P1                 23
T_GLOBALPART_IDX     P2                 23
T_LOCAL_IDX          P2013              20
T_LOCAL_IDX          P2014              20
T_LOCAL_IDX          PMAX               23
T_NONPART_IDX                           17

6 rows selected.

demo@ORA12C> alter table t
  2  split partition pmax at
  3  ( to_date('01-jan-2016','dd-mon-yyyy') )
  4  into ( partition p2016, partition pmax);

Table altered.

demo@ORA12C> select index_name,status
  2   from user_indexes
  3   where index_name = 'T_NONPART_IDX';

INDEX_NAME           STATUS
-------------------- --------
T_NONPART_IDX        UNUSABLE

demo@ORA12C>
demo@ORA12C> select index_name,partition_name,status
  2  from user_ind_partitions
  3  where index_name in ('T_GLOBALPART_IDX',
  4    'T_LOCAL_IDX');

INDEX_NAME           PARTITION_ STATUS
-------------------- ---------- --------
T_GLOBALPART_IDX     P1         UNUSABLE
T_GLOBALPART_IDX     P2         UNUSABLE
T_LOCAL_IDX          P2013      USABLE
T_LOCAL_IDX          P2014      USABLE
T_LOCAL_IDX          P2016      UNUSABLE
T_LOCAL_IDX          PMAX       UNUSABLE

6 rows selected.

demo@ORA12C> select segment_name,partition_name,extents
  2    from user_segments
  3    where segment_name in ( 'T_NONPART_IDX' ,
  4      'T_GLOBALPART_IDX' , 'T_LOCAL_IDX' )
  5    order by segment_name,partition_name nulls first;

SEGMENT_NAME         PARTITION_    EXTENTS
-------------------- ---------- ----------
T_GLOBALPART_IDX     P1                 23
T_GLOBALPART_IDX     P2                 23
T_LOCAL_IDX          P2013              20
T_LOCAL_IDX          P2014              20
T_NONPART_IDX                           17

demo@ORA12C>

Could you help me to understand why these UNUSED segments are not getting released in case of a Globally partitioned index and non-partitioned index ?

with LiveSQL Test Case:

https://livesql.oracle.com

and Chris said...

The key point here is it's when you create the indexes they need to be UNUSABLE.

The script creates USABLE indexes. Splitting the partition doesn't re-create the global indexes. So they keep their extents. Many of these will relate to rows unaffected by the split.

If you add the unusable when creating the index, you'll see the database creates no extents:

create table t
  partition by range( created_dt )
  ( partition p2013 values less than
     ( to_date('01-jan-2014','dd-mon-yyyy') ) ,
   partition p2014 values less than
     ( to_date('01-jan-2015','dd-mon-yyyy') ) ,
   partition pmax values less than (maxvalue)         )
  as
  select a.* ,
     to_date('01-Jan-2013','dd-mon-yyyy') +
     mod(rownum,1825) created_dt
  from all_objects a ;

create index t_nonpart_idx on t(object_id) unusable;

create index t_globalpart_idx on t(object_id,owner)
  global partition by hash(object_id) (
     partition p1,
     partition p2 ) unusable;

create index t_local_idx on t(object_name) local unusable;

select segment_name,partition_name,extents
from user_segments
where segment_name in ( 'T_NONPART_IDX' , 'T_GLOBALPART_IDX' , 'T_LOCAL_IDX' )
order by segment_name,partition_name nulls first;

no rows selected

alter table t
split partition pmax at
( to_date('01-jan-2016','dd-mon-yyyy') )
into ( partition p2016, partition pmax);

select segment_name,partition_name,extents
from user_segments
where segment_name in ( 'T_NONPART_IDX' , 'T_GLOBALPART_IDX' , 'T_LOCAL_IDX' )
order by segment_name,partition_name nulls first;

no rows selected

The indexes are UNUSABLE, not UNUSED.

PS - the LiveSQL link is supposed to point to your script. Not its homepage!

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

UNUSABLE Indexes

Rajeshwaran Jeyabal, October 24, 2017 - 2:29 am UTC

Chris,

let me put my question in a different way.

a) After the Split partition command Global/non-partition/few local partitions were set to UNUSABLE.
But only the few local partitions (which are in UNUSABLE status) has released the extents,
however those extents mapped to UNUSABLE Global/non-partition index are not released why ?

b) Please see the Test case below, when we set an index to "UNUSABLE" explicitly its allocated block got released, However those blocks are not freed up when those index set to "UNUSABLE" by the Partition maintanance operations why?

demo@ORA12C> create table t as select * from all_objects;

Table created.

demo@ORA12C> create index t_idx on t(owner,object_id);

Index created.

demo@ORA12C> select blocks
  2  from user_segments
  3  where segment_name ='T_IDX';

    BLOCKS
----------
       256

demo@ORA12C> alter index t_idx unusable;

Index altered.

demo@ORA12C> select blocks
  2  from user_segments
  3  where segment_name ='T_IDX';

no rows selected

demo@ORA12C>

October 24, 2017 - 7:29 am UTC

a) Remember, the key phrase here is "are created UNUSABLE".

The indexes existed before you ran the partition split. The only new index partitions created at this point are those for the local index T_LOCAL_IDX, p2016 and pmax. And those are the only ones that don't have a segment allocated!

b) You're explicitly asking to make the index unusable here, which deallocates the space.

But you don't want this to happen implicitly when you're doing partition maintenance.

Say you have a global index on a partitioned table. That table has 1 billion rows. You split partition that had 2 million rows into two with 1 million each. The index is UNUSABLE because those 2 million entries may point to the wrong location. You need to update those entries to point to the right location to use it again.

That's a lot less work than deallocating the whole 1 billion entries. Do this and you're rebuilding the whole thing from scratch, instead of just fixing 0.2% of the entries affected by the partition split.

Speculation

Racer I., October 24, 2017 - 7:20 am UTC

Hi,

I just searched for this and found :
https://richardfoote.wordpress.com/category/unusable-indexes/
which states :
We note the index has eventually been made Unusable, however the segment has not now been dropped (as it was in the 11g R2 demo) due to the use of the ONLINE clause.

maybe the implicit unusable setting of the partition maintenance uses the online syntax internally with this effect?

regards,

October 24, 2017 - 7:51 am UTC

Possibly - I guess that could be exposing how the unusable setting of partition maintenance works.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

UNUSABLE Indexes

Speculation

More to Explore

VLDB

VLDB

UNUSABLE index after Partition maintenance operations

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

UNUSABLE Indexes

Speculation

More to Explore

VLDB

VLDB