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:
Post a Comment