PartitioningOracle 8.0 first introduced partitioning, an ability to break a large table or index into many manageable
smaller objects. The main reasons for using partitioning are higher availability, ease of administration and
DML & query performance.
Partitioning Tables:
The availabe methods for table are:
1. Range Partitioning
2. Hash Partitioning
3. List Partitioning
4. Composite Partitioning.
Partitioning Indexes
There are 2 methods to partition indexes. They are:
1. Equi-partition the index with the table - also know as a Local Index
2. Partition the index by range - also know as a Global Index
Data Warehousing and Global Indexes
One of the main reasons why global index was not used was after adding/dropping/exchange partition the
global indexes become unusable, that warrants rebuild/recreate of global indexes. Obviously, this caused
problem to availability of the system. However, Oracle 9i introduced "UPDATE GLOBAL INDEXES" clause to
overcome this issue.
This article demonstrates the real power of partitioning the index and how Oracle automatically maintains the
global indexes.
Getting started
It's always a good idea to start with an example. I would start with one range partitioned table.
The following test was done on 10gR2.
SQL> create table sales
2 PCTFREE 50
3 partition by range (sale_date)
4 (partition SALES2006JAN values less than (to_date('01-FEB-2006','DD-MON-YYYY')),
5 partition SALES2006FEB values less than (to_date('01-MAR-2006','DD-MON-YYYY')),
6 partition SALES2006MAR values less than (to_date('01-APR-2006','DD-MON-YYYY')),
7 partition future values less than (MAXVALUE)
8 )
9 enable row movement
10 AS
11 with generator as (
12 select --+ Materialize
13 rownum as RN
14 from dba_objects
15 where rownum <= 100 )
16 select /*+ ORDERED USE_NL(V2) */
17 trunc(sysdate,'YEAR')+(rownum-1) as sale_date,
18 trunc(10*mod(rownum,10)+1) as cust_id,
19 trunc(5*mod(rownum,30)+1) as prod_id ,
20 10+mod(rownum,20) as tota_sale
21 from generator v1 ,
22 generator v2
23 where rownum <= 90
24 /
Table created.
Here, I used subquery factoring to create a range partitioned table, that 3 partitions. Each partition has
equal number of rows as per the calendar months January, February and March.
Next I creat a global partitioned index.
SQL> create index sales_global_index
2 on sales (sale_date)
3 GLOBAL
4 partition by range( sale_date)
5 (partition SALES2006JAN values less than (to_date('01-FEB-2006','DD-MON-YYYY')),
6 partition SALES2006FEB values less than (to_date('01-MAR-2006','DD-MON-YYYY')),
7 partition SALES2006MAR values less than (to_date('01-APR-2006','DD-MON-YYYY')),
8 partition future values less than (MAXVALUE)
9 )
10 /
Index created.
Analyzed the table and index using dbms_stats package.
SQL> begin
2 dbms_stats.gather_table_stats(
3 user,'SALES',
4 estimate_percent => NULL,
5 granularity => 'DEFAULT',
6 method_opt => 'for all indexed columns size 1' ,
7 cascade => TRUE,
8 DEGREE => 4
9 );
10 end;
11 /
PL/SQL procedure successfully completed.
SQL> select table_name, partition_name, blocks, num_rows, last_analyzed
2 from user_tab_partitions
3 where table_name = 'SALES'
4 /
TABLE_NAME PARTITION_NAM BLOCKS NUM_ROWS LAST_ANALYZED
------------- ------------- ---------- ---------- --------------------
SALES FUTURE 0 0 15-NOV-2006 11:51:41
SALES SALES2006FEB 23 28 15-NOV-2006 11:51:41
SALES SALES2006JAN 23 31 15-NOV-2006 11:51:41
SALES SALES2006MAR 23 31 15-NOV-2006 11:51:41
4 rows selected.
SQL> select index_name, partition_name , status, leaf_blocks, last_analyzed
2 from user_ind_partitions
3 where index_name = 'SALES_GLOBAL_INDEX'
4 /
INDEX_NAME PARTITION_NAM STATUS LEAF_BLOCKS LAST_ANALYZED
-------------------- ------------- -------- ----------- --------------------
SALES_GLOBAL_INDEX FUTURE USABLE 0 15-NOV-2006 11:51:41
SALES_GLOBAL_INDEX SALES2006FEB USABLE 1 15-NOV-2006 11:51:41
SALES_GLOBAL_INDEX SALES2006JAN USABLE 1 15-NOV-2006 11:51:41
SALES_GLOBAL_INDEX SALES2006MAR USABLE 1 15-NOV-2006 11:51:41
4 rows selected.
SQL> select count(*) from sales partition(sales2006jan)
2 /
COUNT(*)
----------
31
1 row selected.
SQL> select count(*) from sales partition(sales2006feb)
2 /
COUNT(*)
----------
28
1 row selected.
SQL> select count(*) from sales partition(sales2006mar)
2 /
COUNT(*)
----------
31
1 row selected.
As you see the number of rows in each partition matches with the count of each partition and all the index
partitions' status are USABLE .
Splitting a Partition
Before you exchange a partition with table data, you need to create a partition for the table and index.
SQL> rem split future partition to accomodate apr data
SQL> rem
SQL>
SQL> alter table sales
2 split partition future
3 at (to_date('05-01-2006','MM-DD-YYYY'))
4 into (partition sales2006apr, partition future)
5 update global indexes
6 /
Table altered.
I have included "UPDATE GLOBAL INDEXES" clause in the "ALTER TABLE ..SPLIT PARTITION" command.
SQL>
SQL> alter index sales_global_index
2 split partition future
3 at (to_date('05-01-2006','MM-DD-YYYY'))
4 into (partition sales2006apr, partition future)
5 /
Index altered.
SQL> rem check the partitions in table and indexes
SQL> rem
SQL> rem ----------------------------------------------------
>
SQL> select table_name, partition_name, blocks, num_rows, last_analyzed
2 from user_tab_partitions
3 where table_name = 'SALES'
4 /
TABLE_NAME PARTITION_NAM BLOCKS NUM_ROWS LAST_ANALYZED
------------- ------------- ---------- ---------- --------------------
SALES FUTURE
SALES SALES2006APR
SALES SALES2006FEB 23 28 15-NOV-2006 11:51:41
SALES SALES2006JAN 23 31 15-NOV-2006 11:51:41
SALES SALES2006MAR 23 31 15-NOV-2006 11:51:41
5 rows selected.
SQL>SQL> select index_name, partition_name , status, leaf_blocks, last_analyzed
2 from user_ind_partitions
3 where index_name = 'SALES_GLOBAL_INDEX'
4 /
INDEX_NAME PARTITION_NAM STATUS LEAF_BLOCKS LAST_ANALYZED
-------------------- ------------- -------- ----------- --------------------
SALES_GLOBAL_INDEX FUTURE USABLE
SALES_GLOBAL_INDEX SALES2006APR USABLE
SALES_GLOBAL_INDEX SALES2006FEB USABLE 1 15-NOV-2006 11:51:41
SALES_GLOBAL_INDEX SALES2006JAN USABLE 1 15-NOV-2006 11:51:41
SALES_GLOBAL_INDEX SALES2006MAR USABLE 1 15-NOV-2006 11:51:41
5 rows selected.
The result clearly shows the global index does not become UNUSABLE after adding a partition.
Exchange Nonpartitioned Table with Partition
First I create a table and an index that holds data, and later that can be exchanged with the partition,
SALES2006APR.
SQL> create table table_sales2006apr
2 AS
3 with generator as (
4 select --+ Materialize
5 rownum as RN
6 from dba_objects
7 where rownum <= 100 )
8 select /*+ ORDERED USE_NL(V2) */
9 to_date('01-APR-2006','DD-MON-YYYY')+(rownum-1) as sale_date,
10 trunc(10*mod(rownum,10)+1) as cust_id,
11 trunc(5*mod(rownum,30)+1) as prod_id ,
12 10+mod(rownum,20) as tota_sale
13 from generator v1 ,
14 generator v2
15 where rownum <= 30
16 /
Table created.
SQL> create index index_sales2006apr on table_sales2006apr(sale_date)
2 pctfree 95
3 /
Index created.
SQL> rem analyze tahle
SQL> begin
2 dbms_stats.gather_table_stats(
3 user,'TABLE_SALES2006APR',
4 estimate_percent => NULL,
5 granularity => 'DEFAULT',
6 method_opt => 'for all indexed columns size 1' ,
7 cascade => TRUE,
8 DEGREE => 4
9 );
10 end;
11 /
PL/SQL procedure successfully completed.
SQL>
SQL> select count(*), min(sale_date), max(sale_date)
2 from table_sales2006apr
3 /
COUNT(*) MIN(SALE_DATE) MAX(SALE_DATE)
---------- -------------------- --------------------
30 01-APR-2006 00:00:00 30-APR-2006 00:00:00
1 row selected.
SQL> select table_name, blocks, num_rows, last_analyzed
2 from user_tables
3 where table_name = 'TABLE_SALES2006APR'
4 /
TABLE_NAME BLOCKS NUM_ROWS LAST_ANALYZED
----------------------- ---------- ---------- --------------------
TABLE_SALES2006APR 23 30 15-NOV-2006 11:51:46
1 row selected.
SQL> select index_name, status, leaf_blocks, last_analyzed
2 from user_indexes
3 where index_name = 'INDEX_SALES2006APR'
4 /
INDEX_NAME STATUS LEAF_BLOCKS LAST_ANALYZED
-------------------- -------- ----------- --------------------
INDEX_SALES2006APR VALID 3 15-NOV-2006 11:51:46
1 row selected.
Before exchange the table, I checked the global index status.
SQL> select index_name, partition_name , status, leaf_blocks, last_analyzed
2 from user_ind_partitions
3 where index_name = 'SALES_GLOBAL_INDEX'
4 /
INDEX_NAME PARTITION_NAM STATUS LEAF_BLOCKS LAST_ANALYZED
-------------------- ------------- -------- ----------- --------------------
SALES_GLOBAL_INDEX FUTURE USABLE
SALES_GLOBAL_INDEX SALES2006APR USABLE
SALES_GLOBAL_INDEX SALES2006FEB USABLE 1 15-NOV-2006 11:51:41
SALES_GLOBAL_INDEX SALES2006JAN USABLE 1 15-NOV-2006 11:51:41
SALES_GLOBAL_INDEX SALES2006MAR USABLE 1 15-NOV-2006 11:51:41
5 rows selected.
SQL> rem exchange nonpartitioend table with partition
SQL>
SQL> alter table sales
2 exchange partition sales2006APR
3 with table table_sales2006APR
4 --including indexes
5 without validation
6 update global indexes
7 /
Table altered.
Note that I put a comment in the line 4. "Including indexes" clause is not accepted while exchanging
nonpartitoned table with a partiton that has global index.
I analyzed the partiitoned table using dbms_stats package.
SQL> begin
2 dbms_stats.gather_table_stats(
3 user,'SALES',
4 estimate_percent => NULL,
5 granularity => 'DEFAULT',
6 method_opt => 'for all indexed columns size 1' ,
7 cascade => TRUE,
8 DEGREE => 4
9 );
10 end;
11 /
PL/SQL procedure successfully completed.
SQL>
SQL> select table_name, partition_name, blocks, num_rows, last_analyzed
2 from user_tab_partitions
3 where table_name = 'SALES'
4 /
TABLE_NAME PARTITION_NAM BLOCKS NUM_ROWS LAST_ANALYZED
----------------------- ------------- ---------- ---------- --------------------
SALES FUTURE 0 0 15-NOV-2006 11:51:52
SALES SALES2006APR 23 30 15-NOV-2006 11:51:52
SALES SALES2006FEB 23 28 15-NOV-2006 11:51:52
SALES SALES2006JAN 23 31 15-NOV-2006 11:51:52
SALES SALES2006MAR 23 31 15-NOV-2006 11:51:52
5 rows selected.
SQL> select index_name, partition_name , status, leaf_blocks, last_analyzed
2 from user_ind_partitions
3 where index_name = 'SALES_GLOBAL_INDEX'
4 /
INDEX_NAME PARTITION_NAM STATUS LEAF_BLOCKS LAST_ANALYZED
-------------------- ------------- -------- ----------- --------------------
SALES_GLOBAL_INDEX FUTURE USABLE 0 15-NOV-2006 11:51:52
SALES_GLOBAL_INDEX SALES2006APR USABLE 1 15-NOV-2006 11:51:52
SALES_GLOBAL_INDEX SALES2006FEB USABLE 1 15-NOV-2006 11:51:52
SALES_GLOBAL_INDEX SALES2006JAN USABLE 1 15-NOV-2006 11:51:52
SALES_GLOBAL_INDEX SALES2006MAR USABLE 1 15-NOV-2006 11:51:52
5 rows selected.
You see the global index does not become UNUSABLE.
SQL> select count(*), min(sale_date), max(sale_date)
2 from sales partition(SALES2006APR)
3 /
COUNT(*) MIN(SALE_DATE) MAX(SALE_DATE)
---------- -------------------- --------------------
30 01-APR-2006 00:00:00 30-APR-2006 00:00:00
1 row selected.
SQL> select count(*) from table_sales2006apr
2 /
COUNT(*)
----------
0
1 row selected.
The non-partitioned table has 0 rows after successfully moved data into partition.
Exchange Partition with Nonpartitioned Table
The next demo shows how to move partitioned object into non-partitioned table.
Assume the table is designed to hold 3 months data, and you want to move out of JAN partition into
non-partitioned table.
SQL>
SQL> create table table_sales2006jan as select * from sales where 1=0
2 /
Table created.
First I created a empty table with equal structure of the partitioned table.
SQL> create index idx_sales2006jan on table_sales2006jan (sale_date) ;
Index created.
Here, I created an index on the nonpartitioned table. Let us see what will happen to the index,
idx_sales2006jan .
SQL> select count(*) from table_sales2006jan
2 /
COUNT(*)
----------
0
1 row selected.
Run the "ALTER TABLE " command to move data from partition to nonpartitioned table.
SQL> alter table sales
2 exchange partition sales2006jan
3 with table table_sales2006jan
4 without validation
5 update global indexes
6 /
Table altered.
SQL>
SQL> select count(*) from table_sales2006jan
2 /
COUNT(*)
----------
31 --- Non partitioned table got the data.
1 row selected.
SQL>
SQL> select count(*) from sales partition(sales2006jan)
2 /
COUNT(*)
----------
0 --- Partition has 0 rows.
1 row selected.
We have moved the data and verified them in 2 places. So far good.
SQL>
SQL> rem check global index status
SQL> select index_name, partition_name , status, leaf_blocks, last_analyzed
2 from user_ind_partitions
3 where index_name = 'SALES_GLOBAL_INDEX'
4 /
INDEX_NAME PARTITION_NAM STATUS LEAF_BLOCKS LAST_ANALYZED
-------------------- ------------- -------- ----------- --------------------
SALES_GLOBAL_INDEX FUTURE USABLE 0 15-NOV-2006 11:51:52
SALES_GLOBAL_INDEX SALES2006APR USABLE 1 15-NOV-2006 11:51:52
SALES_GLOBAL_INDEX SALES2006FEB USABLE 1 15-NOV-2006 11:51:52
SALES_GLOBAL_INDEX SALES2006JAN USABLE 1 15-NOV-2006 11:51:52
SALES_GLOBAL_INDEX SALES2006MAR USABLE 1 15-NOV-2006 11:51:52
5 rows selected.
The global index is in USABLE state. No problem. The index partition SALES2006JAN still exists in the
structure.
Let us verify the nonpartitioned index status.
SQL> select table_name, index_name, status from user_indexes
2 where table_name = 'TABLE_SALES2006JAN'
3 /
TABLE_NAME INDEX_NAME STATUS
------------- -------------------- --------
TABLE_SALES2006JAN IDX_SALES2006JAN UNUSABLE
1 row selected.
The index, IDX_SALES2006JAN became UNUSABLE. This's the drawback of using GLOBAL INDEXES. You
cannot exchange index partition with nonpartitioned index. You have to manually rebuild the nonpartitioned
index.
Dropping Partition
Since January data has been moved to a nonpartitioned table, we can drop that partition now.
Remember you have to drop corresponding index partition alos.
SQL> alter table sales drop partition sales2006jan update global indexes
2 /
Table altered.
SQL> select table_name, partition_name, blocks, num_rows, last_analyzed
2 from user_tab_partitions
3 where table_name = 'SALES'
4 /
TABLE_NAME PARTITION_NAM BLOCKS NUM_ROWS LAST_ANALYZED
------------- ------------- ---------- ---------- --------------------
SALES FUTURE 0 0 15-NOV-2006 11:51:52
SALES SALES2006APR 23 30 15-NOV-2006 11:51:52
SALES SALES2006FEB 23 28 15-NOV-2006 11:51:52
SALES SALES2006MAR 23 31 15-NOV-2006 11:51:52
4 rows selected.
SQL>SQL> rem the index partiton SALES2006JAN still exists
SQL> select index_name, partition_name , status, leaf_blocks, last_analyzed
2 from user_ind_partitions
3 where index_name = 'SALES_GLOBAL_INDEX'
4 /
INDEX_NAME PARTITION_NAM STATUS LEAF_BLOCKS LAST_ANALYZED
-------------------- ------------- -------- ----------- --------------------
SALES_GLOBAL_INDEX FUTURE USABLE 0 15-NOV-2006 11:51:52
SALES_GLOBAL_INDEX SALES2006APR USABLE 1 15-NOV-2006 11:51:52
SALES_GLOBAL_INDEX SALES2006FEB USABLE 1 15-NOV-2006 11:51:52
SALES_GLOBAL_INDEX SALES2006JAN USABLE 1 15-NOV-2006 11:51:52
SALES_GLOBAL_INDEX SALES2006MAR USABLE 1 15-NOV-2006 11:51:52
5 rows selected.SQL> rem drop the index partition
SQL> alter index SALES_GLOBAL_INDEX drop partition sales2006jan
2 /
Index altered.
SQL>
SQL> select index_name, partition_name , status, leaf_blocks, last_analyzed
2 from user_ind_partitions
3 where index_name = 'SALES_GLOBAL_INDEX'
4 /
INDEX_NAME PARTITION_NAM STATUS LEAF_BLOCKS LAST_ANALYZED
-------------------- ------------- -------- ----------- --------------------
SALES_GLOBAL_INDEX FUTURE USABLE 0 15-NOV-2006 11:51:52
SALES_GLOBAL_INDEX SALES2006APR USABLE 1 15-NOV-2006 11:51:52
SALES_GLOBAL_INDEX SALES2006FEB USABLE 1 15-NOV-2006 11:51:52
SALES_GLOBAL_INDEX SALES2006MAR USABLE 1 15-NOV-2006 11:51:52
4 rows selected.
Dropping a partition or partitioned index does not invalidate the global index status.
With the "UPDATE GLOBAL INDEXES" clause, you may now think of partitioning a very large table or index in
an OLTP system.
Cheers, Tamil
smaller objects. The main reasons for using partitioning are higher availability, ease of administration and
DML & query performance.
Partitioning Tables:
The availabe methods for table are:
1. Range Partitioning
2. Hash Partitioning
3. List Partitioning
4. Composite Partitioning.
Partitioning Indexes
There are 2 methods to partition indexes. They are:
1. Equi-partition the index with the table - also know as a Local Index
2. Partition the index by range - also know as a Global Index
Data Warehousing and Global Indexes
One of the main reasons why global index was not used was after adding/dropping/exchange partition the
global indexes become unusable, that warrants rebuild/recreate of global indexes. Obviously, this caused
problem to availability of the system. However, Oracle 9i introduced "UPDATE GLOBAL INDEXES" clause to
overcome this issue.
This article demonstrates the real power of partitioning the index and how Oracle automatically maintains the
global indexes.
Getting started
It's always a good idea to start with an example. I would start with one range partitioned table.
The following test was done on 10gR2.
SQL> create table sales
2 PCTFREE 50
3 partition by range (sale_date)
4 (partition SALES2006JAN values less than (to_date('01-FEB-2006','DD-MON-YYYY')),
5 partition SALES2006FEB values less than (to_date('01-MAR-2006','DD-MON-YYYY')),
6 partition SALES2006MAR values less than (to_date('01-APR-2006','DD-MON-YYYY')),
7 partition future values less than (MAXVALUE)
8 )
9 enable row movement
10 AS
11 with generator as (
12 select --+ Materialize
13 rownum as RN
14 from dba_objects
15 where rownum <= 100 )
16 select /*+ ORDERED USE_NL(V2) */
17 trunc(sysdate,'YEAR')+(rownum-1) as sale_date,
18 trunc(10*mod(rownum,10)+1) as cust_id,
19 trunc(5*mod(rownum,30)+1) as prod_id ,
20 10+mod(rownum,20) as tota_sale
21 from generator v1 ,
22 generator v2
23 where rownum <= 90
24 /
Table created.
Here, I used subquery factoring to create a range partitioned table, that 3 partitions. Each partition has
equal number of rows as per the calendar months January, February and March.
Next I creat a global partitioned index.
SQL> create index sales_global_index
2 on sales (sale_date)
3 GLOBAL
4 partition by range( sale_date)
5 (partition SALES2006JAN values less than (to_date('01-FEB-2006','DD-MON-YYYY')),
6 partition SALES2006FEB values less than (to_date('01-MAR-2006','DD-MON-YYYY')),
7 partition SALES2006MAR values less than (to_date('01-APR-2006','DD-MON-YYYY')),
8 partition future values less than (MAXVALUE)
9 )
10 /
Index created.
Analyzed the table and index using dbms_stats package.
SQL> begin
2 dbms_stats.gather_table_stats(
3 user,'SALES',
4 estimate_percent => NULL,
5 granularity => 'DEFAULT',
6 method_opt => 'for all indexed columns size 1' ,
7 cascade => TRUE,
8 DEGREE => 4
9 );
10 end;
11 /
PL/SQL procedure successfully completed.
SQL> select table_name, partition_name, blocks, num_rows, last_analyzed
2 from user_tab_partitions
3 where table_name = 'SALES'
4 /
TABLE_NAME PARTITION_NAM BLOCKS NUM_ROWS LAST_ANALYZED
------------- ------------- ---------- ---------- --------------------
SALES FUTURE 0 0 15-NOV-2006 11:51:41
SALES SALES2006FEB 23 28 15-NOV-2006 11:51:41
SALES SALES2006JAN 23 31 15-NOV-2006 11:51:41
SALES SALES2006MAR 23 31 15-NOV-2006 11:51:41
4 rows selected.
SQL> select index_name, partition_name , status, leaf_blocks, last_analyzed
2 from user_ind_partitions
3 where index_name = 'SALES_GLOBAL_INDEX'
4 /
INDEX_NAME PARTITION_NAM STATUS LEAF_BLOCKS LAST_ANALYZED
-------------------- ------------- -------- ----------- --------------------
SALES_GLOBAL_INDEX FUTURE USABLE 0 15-NOV-2006 11:51:41
SALES_GLOBAL_INDEX SALES2006FEB USABLE 1 15-NOV-2006 11:51:41
SALES_GLOBAL_INDEX SALES2006JAN USABLE 1 15-NOV-2006 11:51:41
SALES_GLOBAL_INDEX SALES2006MAR USABLE 1 15-NOV-2006 11:51:41
4 rows selected.
SQL> select count(*) from sales partition(sales2006jan)
2 /
COUNT(*)
----------
31
1 row selected.
SQL> select count(*) from sales partition(sales2006feb)
2 /
COUNT(*)
----------
28
1 row selected.
SQL> select count(*) from sales partition(sales2006mar)
2 /
COUNT(*)
----------
31
1 row selected.
As you see the number of rows in each partition matches with the count of each partition and all the index
partitions' status are USABLE .
Splitting a Partition
Before you exchange a partition with table data, you need to create a partition for the table and index.
SQL> rem split future partition to accomodate apr data
SQL> rem
SQL>
SQL> alter table sales
2 split partition future
3 at (to_date('05-01-2006','MM-DD-YYYY'))
4 into (partition sales2006apr, partition future)
5 update global indexes
6 /
Table altered.
I have included "UPDATE GLOBAL INDEXES" clause in the "ALTER TABLE ..SPLIT PARTITION" command.
SQL>
SQL> alter index sales_global_index
2 split partition future
3 at (to_date('05-01-2006','MM-DD-YYYY'))
4 into (partition sales2006apr, partition future)
5 /
Index altered.
SQL> rem check the partitions in table and indexes
SQL> rem
SQL> rem ----------------------------------------------------
>
SQL> select table_name, partition_name, blocks, num_rows, last_analyzed
2 from user_tab_partitions
3 where table_name = 'SALES'
4 /
TABLE_NAME PARTITION_NAM BLOCKS NUM_ROWS LAST_ANALYZED
------------- ------------- ---------- ---------- --------------------
SALES FUTURE
SALES SALES2006APR
SALES SALES2006FEB 23 28 15-NOV-2006 11:51:41
SALES SALES2006JAN 23 31 15-NOV-2006 11:51:41
SALES SALES2006MAR 23 31 15-NOV-2006 11:51:41
5 rows selected.
SQL>SQL> select index_name, partition_name , status, leaf_blocks, last_analyzed
2 from user_ind_partitions
3 where index_name = 'SALES_GLOBAL_INDEX'
4 /
INDEX_NAME PARTITION_NAM STATUS LEAF_BLOCKS LAST_ANALYZED
-------------------- ------------- -------- ----------- --------------------
SALES_GLOBAL_INDEX FUTURE USABLE
SALES_GLOBAL_INDEX SALES2006APR USABLE
SALES_GLOBAL_INDEX SALES2006FEB USABLE 1 15-NOV-2006 11:51:41
SALES_GLOBAL_INDEX SALES2006JAN USABLE 1 15-NOV-2006 11:51:41
SALES_GLOBAL_INDEX SALES2006MAR USABLE 1 15-NOV-2006 11:51:41
5 rows selected.
The result clearly shows the global index does not become UNUSABLE after adding a partition.
Exchange Nonpartitioned Table with Partition
First I create a table and an index that holds data, and later that can be exchanged with the partition,
SALES2006APR.
SQL> create table table_sales2006apr
2 AS
3 with generator as (
4 select --+ Materialize
5 rownum as RN
6 from dba_objects
7 where rownum <= 100 )
8 select /*+ ORDERED USE_NL(V2) */
9 to_date('01-APR-2006','DD-MON-YYYY')+(rownum-1) as sale_date,
10 trunc(10*mod(rownum,10)+1) as cust_id,
11 trunc(5*mod(rownum,30)+1) as prod_id ,
12 10+mod(rownum,20) as tota_sale
13 from generator v1 ,
14 generator v2
15 where rownum <= 30
16 /
Table created.
SQL> create index index_sales2006apr on table_sales2006apr(sale_date)
2 pctfree 95
3 /
Index created.
SQL> rem analyze tahle
SQL> begin
2 dbms_stats.gather_table_stats(
3 user,'TABLE_SALES2006APR',
4 estimate_percent => NULL,
5 granularity => 'DEFAULT',
6 method_opt => 'for all indexed columns size 1' ,
7 cascade => TRUE,
8 DEGREE => 4
9 );
10 end;
11 /
PL/SQL procedure successfully completed.
SQL>
SQL> select count(*), min(sale_date), max(sale_date)
2 from table_sales2006apr
3 /
COUNT(*) MIN(SALE_DATE) MAX(SALE_DATE)
---------- -------------------- --------------------
30 01-APR-2006 00:00:00 30-APR-2006 00:00:00
1 row selected.
SQL> select table_name, blocks, num_rows, last_analyzed
2 from user_tables
3 where table_name = 'TABLE_SALES2006APR'
4 /
TABLE_NAME BLOCKS NUM_ROWS LAST_ANALYZED
----------------------- ---------- ---------- --------------------
TABLE_SALES2006APR 23 30 15-NOV-2006 11:51:46
1 row selected.
SQL> select index_name, status, leaf_blocks, last_analyzed
2 from user_indexes
3 where index_name = 'INDEX_SALES2006APR'
4 /
INDEX_NAME STATUS LEAF_BLOCKS LAST_ANALYZED
-------------------- -------- ----------- --------------------
INDEX_SALES2006APR VALID 3 15-NOV-2006 11:51:46
1 row selected.
Before exchange the table, I checked the global index status.
SQL> select index_name, partition_name , status, leaf_blocks, last_analyzed
2 from user_ind_partitions
3 where index_name = 'SALES_GLOBAL_INDEX'
4 /
INDEX_NAME PARTITION_NAM STATUS LEAF_BLOCKS LAST_ANALYZED
-------------------- ------------- -------- ----------- --------------------
SALES_GLOBAL_INDEX FUTURE USABLE
SALES_GLOBAL_INDEX SALES2006APR USABLE
SALES_GLOBAL_INDEX SALES2006FEB USABLE 1 15-NOV-2006 11:51:41
SALES_GLOBAL_INDEX SALES2006JAN USABLE 1 15-NOV-2006 11:51:41
SALES_GLOBAL_INDEX SALES2006MAR USABLE 1 15-NOV-2006 11:51:41
5 rows selected.
SQL> rem exchange nonpartitioend table with partition
SQL>
SQL> alter table sales
2 exchange partition sales2006APR
3 with table table_sales2006APR
4 --including indexes
5 without validation
6 update global indexes
7 /
Table altered.
Note that I put a comment in the line 4. "Including indexes" clause is not accepted while exchanging
nonpartitoned table with a partiton that has global index.
I analyzed the partiitoned table using dbms_stats package.
SQL> begin
2 dbms_stats.gather_table_stats(
3 user,'SALES',
4 estimate_percent => NULL,
5 granularity => 'DEFAULT',
6 method_opt => 'for all indexed columns size 1' ,
7 cascade => TRUE,
8 DEGREE => 4
9 );
10 end;
11 /
PL/SQL procedure successfully completed.
SQL>
SQL> select table_name, partition_name, blocks, num_rows, last_analyzed
2 from user_tab_partitions
3 where table_name = 'SALES'
4 /
TABLE_NAME PARTITION_NAM BLOCKS NUM_ROWS LAST_ANALYZED
----------------------- ------------- ---------- ---------- --------------------
SALES FUTURE 0 0 15-NOV-2006 11:51:52
SALES SALES2006APR 23 30 15-NOV-2006 11:51:52
SALES SALES2006FEB 23 28 15-NOV-2006 11:51:52
SALES SALES2006JAN 23 31 15-NOV-2006 11:51:52
SALES SALES2006MAR 23 31 15-NOV-2006 11:51:52
5 rows selected.
SQL> select index_name, partition_name , status, leaf_blocks, last_analyzed
2 from user_ind_partitions
3 where index_name = 'SALES_GLOBAL_INDEX'
4 /
INDEX_NAME PARTITION_NAM STATUS LEAF_BLOCKS LAST_ANALYZED
-------------------- ------------- -------- ----------- --------------------
SALES_GLOBAL_INDEX FUTURE USABLE 0 15-NOV-2006 11:51:52
SALES_GLOBAL_INDEX SALES2006APR USABLE 1 15-NOV-2006 11:51:52
SALES_GLOBAL_INDEX SALES2006FEB USABLE 1 15-NOV-2006 11:51:52
SALES_GLOBAL_INDEX SALES2006JAN USABLE 1 15-NOV-2006 11:51:52
SALES_GLOBAL_INDEX SALES2006MAR USABLE 1 15-NOV-2006 11:51:52
5 rows selected.
You see the global index does not become UNUSABLE.
SQL> select count(*), min(sale_date), max(sale_date)
2 from sales partition(SALES2006APR)
3 /
COUNT(*) MIN(SALE_DATE) MAX(SALE_DATE)
---------- -------------------- --------------------
30 01-APR-2006 00:00:00 30-APR-2006 00:00:00
1 row selected.
SQL> select count(*) from table_sales2006apr
2 /
COUNT(*)
----------
0
1 row selected.
The non-partitioned table has 0 rows after successfully moved data into partition.
Exchange Partition with Nonpartitioned Table
The next demo shows how to move partitioned object into non-partitioned table.
Assume the table is designed to hold 3 months data, and you want to move out of JAN partition into
non-partitioned table.
SQL>
SQL> create table table_sales2006jan as select * from sales where 1=0
2 /
Table created.
First I created a empty table with equal structure of the partitioned table.
SQL> create index idx_sales2006jan on table_sales2006jan (sale_date) ;
Index created.
Here, I created an index on the nonpartitioned table. Let us see what will happen to the index,
idx_sales2006jan .
SQL> select count(*) from table_sales2006jan
2 /
COUNT(*)
----------
0
1 row selected.
Run the "ALTER TABLE " command to move data from partition to nonpartitioned table.
SQL> alter table sales
2 exchange partition sales2006jan
3 with table table_sales2006jan
4 without validation
5 update global indexes
6 /
Table altered.
SQL>
SQL> select count(*) from table_sales2006jan
2 /
COUNT(*)
----------
31 --- Non partitioned table got the data.
1 row selected.
SQL>
SQL> select count(*) from sales partition(sales2006jan)
2 /
COUNT(*)
----------
0 --- Partition has 0 rows.
1 row selected.
We have moved the data and verified them in 2 places. So far good.
SQL>
SQL> rem check global index status
SQL> select index_name, partition_name , status, leaf_blocks, last_analyzed
2 from user_ind_partitions
3 where index_name = 'SALES_GLOBAL_INDEX'
4 /
INDEX_NAME PARTITION_NAM STATUS LEAF_BLOCKS LAST_ANALYZED
-------------------- ------------- -------- ----------- --------------------
SALES_GLOBAL_INDEX FUTURE USABLE 0 15-NOV-2006 11:51:52
SALES_GLOBAL_INDEX SALES2006APR USABLE 1 15-NOV-2006 11:51:52
SALES_GLOBAL_INDEX SALES2006FEB USABLE 1 15-NOV-2006 11:51:52
SALES_GLOBAL_INDEX SALES2006JAN USABLE 1 15-NOV-2006 11:51:52
SALES_GLOBAL_INDEX SALES2006MAR USABLE 1 15-NOV-2006 11:51:52
5 rows selected.
The global index is in USABLE state. No problem. The index partition SALES2006JAN still exists in the
structure.
Let us verify the nonpartitioned index status.
SQL> select table_name, index_name, status from user_indexes
2 where table_name = 'TABLE_SALES2006JAN'
3 /
TABLE_NAME INDEX_NAME STATUS
------------- -------------------- --------
TABLE_SALES2006JAN IDX_SALES2006JAN UNUSABLE
1 row selected.
The index, IDX_SALES2006JAN became UNUSABLE. This's the drawback of using GLOBAL INDEXES. You
cannot exchange index partition with nonpartitioned index. You have to manually rebuild the nonpartitioned
index.
Dropping Partition
Since January data has been moved to a nonpartitioned table, we can drop that partition now.
Remember you have to drop corresponding index partition alos.
SQL> alter table sales drop partition sales2006jan update global indexes
2 /
Table altered.
SQL> select table_name, partition_name, blocks, num_rows, last_analyzed
2 from user_tab_partitions
3 where table_name = 'SALES'
4 /
TABLE_NAME PARTITION_NAM BLOCKS NUM_ROWS LAST_ANALYZED
------------- ------------- ---------- ---------- --------------------
SALES FUTURE 0 0 15-NOV-2006 11:51:52
SALES SALES2006APR 23 30 15-NOV-2006 11:51:52
SALES SALES2006FEB 23 28 15-NOV-2006 11:51:52
SALES SALES2006MAR 23 31 15-NOV-2006 11:51:52
4 rows selected.
SQL>SQL> rem the index partiton SALES2006JAN still exists
SQL> select index_name, partition_name , status, leaf_blocks, last_analyzed
2 from user_ind_partitions
3 where index_name = 'SALES_GLOBAL_INDEX'
4 /
INDEX_NAME PARTITION_NAM STATUS LEAF_BLOCKS LAST_ANALYZED
-------------------- ------------- -------- ----------- --------------------
SALES_GLOBAL_INDEX FUTURE USABLE 0 15-NOV-2006 11:51:52
SALES_GLOBAL_INDEX SALES2006APR USABLE 1 15-NOV-2006 11:51:52
SALES_GLOBAL_INDEX SALES2006FEB USABLE 1 15-NOV-2006 11:51:52
SALES_GLOBAL_INDEX SALES2006JAN USABLE 1 15-NOV-2006 11:51:52
SALES_GLOBAL_INDEX SALES2006MAR USABLE 1 15-NOV-2006 11:51:52
5 rows selected.SQL> rem drop the index partition
SQL> alter index SALES_GLOBAL_INDEX drop partition sales2006jan
2 /
Index altered.
SQL>
SQL> select index_name, partition_name , status, leaf_blocks, last_analyzed
2 from user_ind_partitions
3 where index_name = 'SALES_GLOBAL_INDEX'
4 /
INDEX_NAME PARTITION_NAM STATUS LEAF_BLOCKS LAST_ANALYZED
-------------------- ------------- -------- ----------- --------------------
SALES_GLOBAL_INDEX FUTURE USABLE 0 15-NOV-2006 11:51:52
SALES_GLOBAL_INDEX SALES2006APR USABLE 1 15-NOV-2006 11:51:52
SALES_GLOBAL_INDEX SALES2006FEB USABLE 1 15-NOV-2006 11:51:52
SALES_GLOBAL_INDEX SALES2006MAR USABLE 1 15-NOV-2006 11:51:52
4 rows selected.
Dropping a partition or partitioned index does not invalidate the global index status.
With the "UPDATE GLOBAL INDEXES" clause, you may now think of partitioning a very large table or index in
an OLTP system.
Cheers, Tamil
No comments:
Post a Comment