Q
Problem solve Get help with specific problems with your technologies, process and projects.

Partitioning tables by range

We have an integrated banking system running on Oracle 9i. Each table has only two columns, a combination of either VARCHAR2 and BLOB datatypes or VARCHAR2 and XMLTYPE datatypes. My question is how can we partition the tables using the transaction date within the XMLTYPE column?

We have an integrated banking system running on Oracle 9i (9.2.0.6 to be exact). Each table has only two columns, a combination of either VARCHAR2 and BLOB datatypes or VARCHAR2 and XMLTYPE datatypes. A number of tables, especially those with XMLTYPE datatype columns, are populated daily with a large number of records (over 1,000). With the pace of growth these tables are going at and for ease of table maintenance, we are looking at the possibility of using table partitioning by range. We have identified the transaction date to use as the range condition. The transaction date is part of the XMLTYPE datatype column. In order to access the date, we have to manipulate the XMLTYPE column using the xmlrecord.EXTRACT command, and we're quite successful. However, we are not successful in using this command in the range condition for partitioning the table.

My question is how can we possibly partition the tables using the transaction date within the XMLTYPE column? If this is not possible, can you please recommend one for us.

You will not be able to parition on an XML column or any part of the XML column. Since the data you want to partition on is contained in that XML column, I would recommend pulling that data out and storing it in another column. For instance, your table has a VARCHAR2 column, and an XMLTYPE column. I'd add a third column to the table and make it a DATE datatype. Then I'd add a trigger to the column to populate the DATE column with the data you parsed out of the XMLTYPE column. The table can then be partitioned on this DATE column.

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close