How to Create Index Partitions To Ranged Partitioned Table
Starting with Oracle 8, Oracle allows for tables to be divided into different storage areas called partitions. Each partition of a table will contain the same constraints, columns and datatypes as all other partitions of that table, but can have different storage attributes i.e. each partition can be stored in separate tablespaces on different disks.
Example for range partitioned table:
create table orders (
order_no number,
part_no varchar2(40),
ord_date date
)
partition by range (ord_date)
(partition Q1 values less than (TO_DATE('01-APR-1999','DD-MON-YYYY'))
tablespace Q1_DATA,
partition Q2 values less than (TO_DATE('01-JUL-1999','DD-MON-YYYY'))
tablespace Q2_DATA,
partition Q3 values less than (TO_DATE('01-OCT-1999','DD-MON-YYYY'))
tablespace Q3_DATA,
partition Q4 values less than (TO_DATE('01-JAN-2000','DD-MON-YYYY'))
tablespace Q4_DATA);
As you can see, no STORAGE values have been specified, in this example the partitions will inherit the default storage attributes of the tablespaces. If you wish physical attributes and the storage clause can be specified for the entire table :-
i.e.
create table orders (
order_no number,
part_no varchar2(40),
ord_date date
)
storage (initial 1M next 1M pctincrease 0)
partition by range (ord_date)
(partition Q1 values less than (TO_DATE('01-APR-1999','DD-MON-YYYY'))
tablespace Q1_DATA,
partition Q2 values less than (TO_DATE('01-JUL-1999','DD-MON-YYYY'))
tablespace Q2_DATA,
partition Q3 values less than (TO_DATE('01-OCT-1999','DD-MON-YYYY'))
tablespace Q3_DATA,
partition Q4 values less than (TO_DATE('01-JAN-2000','DD-MON-YYYY'))
tablespace Q4_DATA);
or the attributes can be specified at the partition level:
create table orders (
order_no number,
part_no varchar2(40),
ord_date date
)
partition by range (ord_date)
(partition Q1 values less than (TO_DATE('01-APR-1999','DD-MON-YYYY'))
storage (initial 1M next 1M pctincrease 0)
tablespace Q1_DATA,
partition Q2 values less than (TO_DATE('01-JUL-1999','DD-MON-YYYY'))
storage (initial 2M next 1M pctincrease 0)
tablespace Q2_DATA,
partition Q3 values less than (TO_DATE('01-OCT-1999','DD-MON-YYYY'))
storage (initial 5M next 2M pctincrease 0)
tablespace Q3_DATA,
partition Q4 values less than (TO_DATE('01-JAN-2000','DD-MON-YYYY'))
storage (initial 2M next 5M pctincrease 0)
tablespace Q4_DATA);
As well as having the table partitioned, the associated indexes can also be partitioned. Like the tables, the index partitions must have the same logical attributes i.e. index columns, but can have different storage attributes i.e. each partition can again be stored in different locations on the server.
There are two main partitioned index types, global and local.
GLOBAL Index
The global index type allows the index partitions to be different from the underlying partitioned table or if you wish they can be the same. Using global indexes means that the index does not have a direct relationship with the table, unlike the local index type which is discussed below. Using the above partitioned table example we can create the global index with or without the same partition attributes.
With the same attributes:
create index orders_global_1_idx
on orders(ord_date)
global partition by range (ord_date)
(partition GLOBAL1 values less than (TO_DATE('01-APR-1999','DD-MON-YYYY'))
tablespace Q1_INDEX,
partition GLOBAL2 values less than (TO_DATE('01-JUL-1999','DD-MON-YYYY'))
tablespace Q2_INDEX,
partition GLOBAL3 values less than (TO_DATE('01-OCT-1999','DD-MON-YYYY'))
tablespace Q3_INDEX,
partition GLOBAL4 values less than (MAXVALUE)
tablespace Q4_INDEX);
With different partition attributes:
create index orders_global_2_idx
on orders(ord_date)
global partition by range (ord_date)
(partition INDEX1 values less than (TO_DATE('01-JUL-1999','DD-MON-YYYY'))
tablespace INDEX1,
partition INDEX2 values less than (MAXVALUE)
tablespace INDEX2);
With different partition attributes and key:
create index orders_global_3_idx
on orders(part_no)
global partition by range (part_no)
(partition INDEX1 values less than (555555)
tablespace INDEX1,
partition INDEX2 values less than (MAXVALUE)
tablespace INDEX2);
As can be seen, as the indexes are Global and do not have a direct relationship with the table, the option of MAXVALUE must be specified. The index will not have a new partition added automatically if the table has a new partition added.
With Global indexes the index key must be the same as the partition key, this is known as a prefixed index (where the leftmost column in the index matches the leftmost column in the index’s partition key). If you wish the index key to be different to the partition key i.e. a non prefixed index, then the index must be created a Local index or you will receive an ‘ORA-14038 Global partitioned index must be prefixed’.
LOCAL
With local indexes the partition ranges are not specified as the index is local to the table. Therefore when a Local index is created Oracle will actually create a separate index for each partition in the table.
create index orders_local_1_idx
on orders(ord_date)
local
(partition LOCAL1
tablespace Q1_INDEX,
partition LOCAL2
tablespace Q2_INDEX,
partition LOCAL3
tablespace Q3_INDEX,
partition LOCAL4
tablespace Q4_INDEX);
The option of MAXVALUE doesn’t need to be specified because if a new table partition is added, then an new index partition will be added automatically. We will discuss the attributes and location of the new index partitions shortly. The above index creation is a prefixed index as with the Global examples, as was mentioned previously Local indexes can also be non prefixed.
create index orders_local_2_idx
on orders(part_no)
local
(partition LOCAL1
tablespace Q1_INDEX,
partition LOCAL2
tablespace Q2_INDEX,
partition LOCAL3
tablespace Q3_INDEX,
partition LOCAL4
tablespace Q4_INDEX);
Here our index key is part_no but our partition key is the same as the table i.e. ord_date. If we now add a new partition to our orders table:-
alter table orders
add partition NEW_PART values less than (TO_DATE('01-APR-2000','DD-MON-YYYY'))
tablespace new_tab_part;
there will automatically be an index partition created for our local index
select substr(index_name,1,20) index_name, substr(tablespace_name,1,20)
tablespace_name, substr(partition_name,1,20) part_name
from dba_ind_partitions
where index_name = 'ORDERS_LOCAL_2_IDX'
order by partition_name;
INDEX_NAME TABLESPACE_NAME PART_NAME
-------------------- -------------------- --------------------
ORDERS_LOCAL_2_IDX Q1_INDEX LOCAL1
ORDERS_LOCAL_2_IDX Q2_INDEX LOCAL2
ORDERS_LOCAL_2_IDX Q3_INDEX LOCAL3
ORDERS_LOCAL_2_IDX Q4_INDEX LOCAL4
ORDERS_LOCAL_2_IDX NEW_TAB_PART NEW_PART
When we created the index initially we could specify where the index partitions are located and what they are called. The same goes for creating the table partitions and adding the new partitions. But, as a new local index partition is added automatically we cannot specify where this is located or what it is called. As the above select shows the new local index partition has to be put in the same tablespace as the new table partition and has the same name.
There is a way of getting around this problem, as having the index and table partition located in the same place is not ideal. What we need to do to change the location of the new index partition is to change the location of the default tablespace for the index. If a default tablespace isn’t set then the index partition will be located in the same tablespace as the table partition, as in the example above. But if we modify the default tablespace:
alter index orders_local_2_idx
modify default attributes
tablespace new_ind_part;
then once the table partition is added the new index partition will go into the new_ind_part tablespace.
INDEX_NAME TABLESPACE_NAME PART_NAME
-------------------- -------------------- --------------------
ORDERS_LOCAL_2_IDX Q1_INDEX LOCAL1
ORDERS_LOCAL_2_IDX Q2_INDEX LOCAL2
ORDERS_LOCAL_2_IDX Q3_INDEX LOCAL3
ORDERS_LOCAL_2_IDX Q4_INDEX LOCAL4
ORDERS_LOCAL_2_IDX NEW_IND_PART NEW_PART
now we need to change the name of the partition, this can be done by :-
alter index orders_local_2_idx rename partition NEW_PART to LOCAL5;
INDEX_NAME TABLESPACE_NAME PART_NAME
-------------------- -------------------- --------------------
ORDERS_LOCAL_2_IDX Q1_INDEX LOCAL1
ORDERS_LOCAL_2_IDX Q2_INDEX LOCAL2
ORDERS_LOCAL_2_IDX Q3_INDEX LOCAL3
ORDERS_LOCAL_2_IDX Q4_INDEX LOCAL4
ORDERS_LOCAL_2_IDX NEW_IND_PART LOCAL5