Partitioning made easy - Part 2

In one of my previous post (http://mfzahirdba.blogspot.com/2014/12/partitioning-made-easy.html)  , I have mentioned about few of the partioning enhancements in Oracle 12c. 


In Oracle 12c , creation of partial indexes have made easy to maintain and administer.  Partial indexes helps us in saving space , index maintenance operations such as rebuilding / gathering statistics , so on. 

One way is to do that is , to define the partitioned table as indexing OFF / ON at the table level and then define indivdual partions to set indexing OFF  / ON. The second step is to define the partioned index as PARTIAL. 

Here is the example . 

For my business case , only the courses that were offered for the last three years are actively queried on ... In this case ,  I am setting these three partitions ( course_part_2013 , course_part_2014 , course_part_2015) to have index on the dataset. 


SQL> Create table course
  2  ( course_year int ,
  3    course_term varchar2(1) ,
  4    course_index varchar2(6) ,
  5    course_code varchar2(12)
  6    )
  7    indexing off
  8    PARTITION by range ( course_year)
  9    (
 10    partition course_part_2010 values less than ( 2011) indexing off,
 11    partition course_part_2011 values less than ( 2012) indexing off,
 12    partition course_part_2012 values less than ( 2013) indexing off,
 13    partition course_part_2013 values less than ( 2014) indexing on,
 14    partition course_part_2014 values less than ( 2015) indexing on,
 15    partition course_part_2015 values less than ( 2016) indexing on
 16    );

Table created.

Now , I am creating global PARTIAL index as below .

SQL>    Create index idx_course on course(course_index)  global indexing partial ;

Index created.


SQL>   Select
  2    table_name , partition_name ,  indexing
  3    from user_tab_partitions
  4    where table_name ='COURSE'  ;

TABLE_NAME           PARTITION_NAME                 INDE
-------------------- ------------------------------ ----
COURSE               COURSE_PART_2010               OFF
COURSE               COURSE_PART_2011               OFF
COURSE               COURSE_PART_2012               OFF
COURSE               COURSE_PART_2013               ON
COURSE               COURSE_PART_2014               ON
COURSE               COURSE_PART_2015               ON

6 rows selected.


SQL>  Select
  2    table_name , index_name , indexing
  3    from user_indexes
  4    where table_name ='COURSE' ;

TABLE_NAME           INDEX_NAME           INDEXIN
-------------------- -------------------- -------
COURSE               IDX_COURSE           PARTIAL



This is one of the good ones in Oracle 12c.