Month: February 2009

Creating unique constraints (indexes) for multidimesional datasets


there sometimes exists requirements to enforce unique constraints on datasets that do not have a simple layout of distinct values over one or, if logically concatenated, more columns. for example, imagine the real world scenario of a 1:n, parent-child table relationship, where a parent table represents a number of document records and the child table holds a multitude of typed document attachement information. imagine futher, that only one document attachement type, say 'TYPE_0', should serve as a master or most current attachement per document. other document attachement types, say 'TYPE_1' and 'TYPE_2', may recur per document without any limitation.

the classic approach of a two-column unique constraint, comprising some doc_id and some doc_type columns, will not be appropriate in that case. the constraint will be violated as soon as any recurring of document types, 'TYPE_1' and 'TYPE_2', per document takes place.

a really impressive solution

the following problem solution is not new, nor has it been invented by me at all (thanks to m.s. for pointing in out to me) but i always go crazy about the way this very solution evolves from a merge of, on the first spot, rather different database schema design techniques. that is:

  • the nature of unique constraints, compared to primary key constraints and
  • the world of index definitions, specifically unique and function-based indexes.


A method of add partition for maxvalue range partitions


this article discusses a selection of methods, as well as their prerequisites and consequences, of adding a partition to the high end of an oracle range partitioned table, where the high end is dedicatedly denoted by maxvalue. it also regards questions arising when the partitioned table contains storage optimized lob-colums, a text-domain-index and, for size and failover reasons, every table and several index partitions reside in dedicated tablespaces.

range partitioned tables and add partition

literally seen, adding a partition to the high end of an oracle range partitioned table should easily be possible with the alter table <table> add partition ... statement. the common issue with alter table <table> add partition ... is, however, the fact that the statement will only succeed if the high end is a constant, a fixed value, may it be a number or a date or whatever. if you try to add a partition to a table, that has an upper limit of maxvalue, you may receive the following error:

ORA-14074: partition bound must collate higher than that of the last partition
Cause: Partition bound specified in ALTER TABLE ADD PARTITION statement did not collate higher than that of the table’s last partition, which is illegal.
Action: Ensure that the partition bound of the partition to be added collates higher than that of the table’s last partition.

the point goes without speaking. you cannot specify a newer upper limit that is in fact larger than maxvalue, which is infinite by definition.

real world business does hardly allow to preestimate a meaningful fixed value for the high end of a range partitioned table. business rules may change quite often such that an immediate adjustment of a fixed high end value may pose considerable efforts or may even be impossible.

if, on the other hand, one uses maxvalue to denote the partitioned table high end, what other methods are available to evolve a partitioning scheme, a time windowing technique for example, and what will be the prerequisites and consequences of such a declaration? the following presents a fully functional test case that aims to simulate the execution of a split partition action upon a rather complex table layout having lob-colums, a text-domain-index and partition-related tablespaces. quite a couple of views are provided to follow each and every change in terms of table- and index-storage and object operation validity. just playing around with the test case should smootly introduce a way to some dedicated production site implementation.