Table Partitioning in Oracle

What is table partitioning?

Table partitioning in Oracle is a feature given by the database system to store and manage a table's record in physically partitioned spaces in the storage but transparent to all who are accessing the table.

There are different kind of strategies to partition a table:
- Range partition: partition the table's record in a range, for example, of number or Date
- List partition: this strategy is used when it is required to organized table's record using some values e.g. State, City, Country etc.
- Hash partition: a hash function is used to distribute the records across the partitions.

The partition can have sub partitions as well. In Oracle 10g, the valid sub partitions type within partition are:
- Range partition ->List sub partition
- Range partition -> Hash sub partition

The benefit of using partitions are:
- The access to table's record are limited to partition so search would be much faster
- Partitions can be independently backed up and restored without hampering the table's availability
- Partitions can contain the indexes within itself (Local index)
- Partitions data can be moved to a table with a single command (Exchange partition)

1. Create a partitioned table

CREATE TABLE PARTITIONED_TABLE
( ID INTEGER NOT NULL,
AS_OF_DATE DATE NOT NULL,
BALANCE NUMBER(25,7),
DEBIT NUMBER(20,5),
CREDIT NUMBER(20,5)
)
PARTITION BY RANGE (AS_OF_DATE)
(
PARTITION YEAR_JAN_2006 VALUES LESS THAN (TO_DATE('01-FEB-2006', 'DD-MON-YYYY')),
PARTITION YEAR_FEB_2006 VALUES LESS THAN (TO_DATE('01-MAR-2006', 'DD-MON-YYYY')),
PARTITION YEAR_MAR_2006 VALUES LESS THAN (TO_DATE('01-APR-2006', 'DD-MON-YYYY'))
)

2. Populate data into the partitioned table, if available

insert into partitioned_table (id, as_of_date, balance, credit, debit)
(select id, as_of_date, balance, credit, debit from regular_table);

insert into bsl_rdr.rdr_erec_partitioned (id, as_of_date, balance, credit, debit)
values (1, '30-JAN-2006', 100.0, 20.0, 100);

Commit;

3. Analyze the table to gather statistics data

analyze table partitioned_table compute statistics

analyze table partitioned_table PARTITION(YEAR_JAN_2006) compute statistics;

4. Check the statistics of the partitioned table

select t.table_name, t.partition_name, t.num_rows, t.high_value from user_tab_partitions t where t.table_name = 'PARTITIONED_TABLE'

5. Exchange partition's data into a new table

ALTER TABLE PARTITIONED_TABLE
EXCHANGE PARTITION YEAR_JAN_2006 WITH TABLE

6. Split a partition into two

ALTER TABLE PARTITIONED_TABLE
SPLIT PARTITION YEAR_JAN_2006 AT (TO_DATE('01-Jan-2006', 'DD-MON-YYYY'))
INTO(
Partition YEAR_DEC_2005,
Partition YEAR_JAN_2006
);

Note: Be careful about the sequence when a partition needs to be splitted to actually create a new partition with the lowest range value, you might want to keep the original partition, for example, to hold, for example, 1 month records and the new partition would hold the records below that date. Keep the new partition name first in the INTO(....) part and then keep the original partition name to achieve that objective. In above example statement, I wanted to keep YEAR_JAN_2006 partition to hold the January, 2006 records and YEAR_DEC_2005 would contain records from December, 2005 and backwards.

7. Add a new partition

alter table PARTITIONED_TABLE
add partition YEAR_MAR_2010 VALUES LESS THAN (TO_DATE('01-APR-2010', 'DD-MON-YYYY'))

8. Renaming a partition

alter table PARTITIONED_TABLE rename partition YEAR_MAR_2006 to YEAR_FEB_2006

9. Drop a partition

alter table partitioned_table
drop partition YEAR_JAN_2006_1

Resources:
http://www.dbazine.com/oracle/or-articles/nanda6 - Methods to migrate from regular table to partitioned table

Data Dictionary: a thought

Data Dictionary (DD) is usually created by the development lead or the database analyst during or after the creation of the Data Model. The most popular format of a DD is using a spreadsheet or in rare case text document. Some people also uses the database's Data Dictionary infrastructure (Oracle has a Data Dictionary infrastructure where you can enter Definitions for Tables and Columns as comments in a system table. The second most important thing after the creation of the definition is the change log. Most of the time I've seen this has been ignored or given less importance in a project. But to me this is a single most important thing the Data Model designer should emphasize apart from the Data Dictionary definitions. Because even though we think of a initial Data Model as invincible for next application changes but, in practical life, it is not.

I've seen and used another spreadsheet to maintain the change logs which is asynchronous and not integrated with the Data Model designing activities. Now the question is how we can enforce the change logs with the Data Model activities. Here is a proposed solution:

We can have a Data Model for the Data Dictionary. Below is a pseudo script of the DDL

TABLE DD_TABLE (
TABLE_NAME -- may be prefixed with schema to uniquely identify
TABLE_DEFINITION
TABLE_STATUS -- Active, Inactive, Decommissioned, Future_Use etc.
CREATE_COMMENT
CREATED_BY
CREATED_DATE
LAST_UPDATED_BY
LAST_UPDATED_DATE
)

TABLE DD_TABLE_HISTORY (
TABLE_NAME (FK from DD_TABLE)
OLD_VALUE
NEW_VALUE -- redundant information for reporting purpose
CHANGE_COMMENT
CHANGED_BY
CHANGED_DATE
)


TABLE DD_COLUMN (
TABLE_NAME (FK of DATA_DICTIONARY_TABLE)
COLUMN_NAME
TABLE_STATUS -- Active, Inactive, Decommissioned, Future_Use etc.
CREATED_BY
CRATED_DATE
LAST_UPDATED_BY
LAST_UPDATED_DATE
LAST_UPDATE
)

TABLE DD_COLUMN_HISTORY (
TABLE_NAME (FK from DD_TABLE)
COLUMN_NAME (FK from DD_COLUMN)
OLD_VALUE
NEW_VALUE -- redundant information for reporting purpose
CHANGE_COMMENT
CHANGED_BY
CHANGED_DATE
)

Triggers can be used to enforce to have default log entry into the history tables unless otherwise entered by the Data Model designer. What do you think about it?

A Dictionary of unusual Java Exceptions

Exception # 1:
The transaction service encountered an error on an xa_recover operation. The resource was com.ibm.ws.rsadapter.spi.WSRdbXaResourceImpl@510051. The error code was XAER_RMERR. The exception stack trace follows: javax.transaction.xa.XAException

Description:
The XA transaction manager tries to recover for an early server crash either in application server or database server side or could be both but the user id that has been used to connect to the database doesn't have the permission to access certain tables.

Solution:
provide access to the BA_PENDING_TRANSACTIONS table for the particular database user in question

Exception # 2:
Exception in thread "CompilerThread0" java.lang.OutOfMemoryError: requested 32776 bytes for Chunk::new. Out of swap space?

Description:
Websphere Application Server 6.1.17 goes OutOfMemory with the above error trace on the console

Solution:
This is bug in JVM JIT compilation and the fix is not confirmed yet. The work around is to use below configuration:

-XX:+UseDefaultStackSize -Xss256K

Resources:
http://www.ibm.com/developerworks/websphere/library/techarticles/0407_woolf/0407_woolf.html- Distributed transaction in WAS (Exception # 1)
http://bugs.sun.com/bugdatabase/view_bug.do?bug_id=4916142 - OutOfMemory Error (Exception # 2)