Search My Oracle Blog

Custom Search

22 February, 2012

Two Partitioned Indexes with different HIGH_VALUEs

Here is a quick demonstration to show how a Partitioned Table can have two different indexes seemingly on the same leading column but with different HIGH_VALUEs.   One index is a LOCALly Partitioned Index that is Equi-Partitioned with the table, the other index is a GLOBALly Partitioned Index where the Partition Keys can be different.


SQL> drop table partitioned_table purge;

Table dropped.

SQL> 
SQL> create table partitioned_table
  2  (sale_date date,
  3   product_code  number,
  4   location_code number,
  5   customer_code number,
  6   sale_qty      number
  7  )
  8  partition by range (sale_date)
  9  (partition P_2009 values less than (to_date('01-JAN-2010','DD-MON-YYYY')),
 10  partition P_2010 values less than (to_date('01-JAN-2011','DD-MON-YYYY')),
 11  partition P_2011 values less than (to_date('01-JAN-2012','DD-MON-YYYY')),
 12  partition P_2012 values less than (to_date('01-JAN-2013','DD-MON-YYYY'))
 13  )
 14  /

Table created.

SQL> 
SQL> -- a LOCALly Partitioned Index
SQL> --  this is automagically Equi-Partitioned with the Table
SQL> create index local_index on partitioned_table (sale_date) local;

Index created.

SQL> 
SQL> -- a GLOBALly Partitioned Index
SQL> -- NOTE : cannot create an index on exactly the same columns as an existing index
SQL> create index gbl_partitioned_index on partitioned_table (sale_date,sale_qty)
  2  global partition by range (sale_date,sale_qty)
  3  (partition DECADE_2010_1_MILL values less than ((to_date('01-JAN-2011','DD-MON-YYYY')),1000001),
  4  partition DECADE_2010_2_MILL values less than ((to_date('01-JAN-2011','DD-MON-YYYY')),2000001),
  5   partition DECADE_2020_1_MILL values less than ((to_date('01-JAN-2021','DD-MON-YYYY')),1000001),
  6   partition DECADE_2020_2_MILL values less than ((to_date('01-JAN-2021','DD-MON-YYYY')),2000001),
  7  -- MUST specify a MAXVALUE partition for a Global Partitioned Index
  8   partition DECADE_MAX values less than (MAXVALUE,MAXVALUE)
  9  )
 10  /

Index created.

SQL> 
SQL> -- remember to SET LONG to a larger value so that HIGH_VALUE can be shown completely !!
SQL> set long 10000
SQL> column high_value format a84
SQL> 
SQL> 
SQL> select partition_name, high_value
  2  from user_tab_partitions
  3  where table_name = 'PARTITIONED_TABLE'
  4  order by partition_position
  5  /

PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------------------------------------------------------------
P_2009                         TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
P_2010                         TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
P_2011                         TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
P_2012                         TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

SQL> 
SQL> 
SQL> select partition_name, high_value
  2  from user_ind_partitions
  3  where index_name = 'LOCAL_INDEX'
  4  order by partition_position
  5  /

PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------------------------------------------------------------
P_2009                         TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
P_2010                         TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
P_2011                         TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
P_2012                         TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

SQL> 
SQL> select partition_name, high_value
  2  from user_ind_partitions
  3  where index_name = 'GBL_PARTITIONED_INDEX'
  4  order by partition_position
  5  /

PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------------------------------------------------------------
DECADE_2010_1_MILL             TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'),
                                1000001

DECADE_2010_2_MILL             TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'),
                                2000001

DECADE_2020_1_MILL             TO_DATE(' 2021-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'),
                                1000001

DECADE_2020_2_MILL             TO_DATE(' 2021-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'),
                                2000001

DECADE_MAX                     MAXVALUE, MAXVALUE

SQL> 
SQL> -- can I create a GBL_PARTITIONED with seemingly the same HIGH_VALUE as the LOCAL ?
SQL> drop index GBL_PARTITIONED_INDEX;

Index dropped.

SQL> 
SQL> create index gbl_partitioned_index on partitioned_table (sale_date,sale_qty)
  2  global partition by range (sale_date,sale_qty)
  3  (partition YEAR_2010_1_MILL values less than ((to_date('01-JAN-2011','DD-MON-YYYY')),1000001),
  4  partition YEAR_2010_2_MILL values less than ((to_date('01-JAN-2011','DD-MON-YYYY')),2000001),
  5  partition YEAR_2011_1_MILL values less than ((to_date('01-JAN-2012','DD-MON-YYYY')),1000001),
  6  partition YEAR_2011_2_MILL values less than ((to_date('01-JAN-2012','DD-MON-YYYY')),2000001),
  7  -- MUST specify a MAXVALUE partition for a Global Partitioned Index
  8   partition DECADE_MAX values less than (MAXVALUE,MAXVALUE)
  9  )
 10  /

Index created.

SQL> 
SQL> select partition_name, high_value
  2  from user_ind_partitions
  3  where index_name = 'GBL_PARTITIONED_INDEX'
  4  order by partition_position
  5  /

PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------------------------------------------------------------
YEAR_2010_1_MILL               TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'),
                                1000001

YEAR_2010_2_MILL               TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'),
                                2000001

YEAR_2011_1_MILL               TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'),
                                1000001

YEAR_2011_2_MILL               TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'),
                                2000001

DECADE_MAX                     MAXVALUE, MAXVALUE

SQL> 
So I can have two different indexes, differently partitioned on the same table. A LOCAL Index is LOCALly Partitioned  (properly known as an "Equi-Partitioned Index") -- meaning that it has Partitions that match the table definition.  A GLOBALly Partitioned Index can have Partition definitions that do NOT match the table.

.
.
.

No comments:

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