We have an integrated banking system running on Oracle 9i (22.214.171.124 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.