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