Getting Started






Quick Links





Data Updates



1. Analysis

2. Partition the Table

3. Environment File Entry

4. Indexing



Partitioning is a common RDBMS strategy that turns a large set of data into smaller, logical subsets. The most common objectives for partitioning databases are to make an application perform better and to make the data easier to administrate.

Omnidex supports partitioning in three ways:

  • physical partitioning of data into two or more separate partitions
  • partitioning of indexes using composite MDK indexes
  • partitioning of indexes using composite ASK indexes

All three partitioning concepts can be used together or separately and are discussed in detail in this section.

What if my data is already partitioned?



Partitioned data can return extraordinary performance on extremely large databases. A partitioned table is broken up into smaller pieces, making the individual table sizes smaller. For example, a table containing 10 billion rows partitioned into 10 partitions, creates 10 one billion row tables. Searching one billion rows is considerably faster than searching 10 billion rows, and even faster when installed with Omnidex indexes.

Physical index files are smaller, narrowing the number of index key values Omnidex must look through to qualify data.

Index build time can be shorter. For example, a partition can be made up of updates for a given period of time. The new partition can be appended to the end of the existing partitions. The indexes for this new partition can be built without the need to rebuild the indexes on the other partitions.


Advance analysis is required to determine the best partitioning strategy, and indeed if the data should be partitioned at all.

Continual analysis may be required to determine if the current partitioning strategy continues to be the best strategy.

Since the data is physically separated into different tables, updates to the data must be made to the respective partition. At this time, Omnidex does not handle this automatically.



  • Only child tables can be partitioned.
  • Only one partitioned child table per query is allowed.
  • Select items containing multiple aggregations in a single expression are not supported.
  • Left outer joins TO a partitioned table are not allowed. However, left outer joins FROM a partitioned table are allowed.
  • Updates to partitioned data must be made to the individual partition. Omnidex does not handle this automatically. See Updating Partitioned Tables (below) for more information.
  • MDK Composite keys are limited to 240 bytes.
  • Aggregation indexes are limited to 32 values in an IN clause.
  • Since each partition will have its own set of indexes and the number of index files is limited to 255 physical files, the number of partitions can be limited.


Updating Partitioned Tables

Applications that update partitioned tables must take some extra steps to make sure the updates are made to the correct partition. Unlike select statements, insert, update and delete statements must reference the individual partition, not the unioned table.

For example, if the prospects table is partitioned into 5 partitions, where the first partition contains only prospects from the state of California, the second partition contains only prospects from the states of New York and New Jersey, and so one, the application would have to determine which partition to perform the update against according to the state.

If a prospect from the state of California is inserted into the second partition, which contains only prospects from New York and New Jersey, the new record would never be qualified by any select statement.

Therefore, the update application must determine which partition should be updated.


What if my data is already partitioned?

If the data is already partitioned, analysis is still the first and most important step.

  • Is there a partition qualifier?
  • Does the partition structure meet the needs of all of my queries?
  • Are the partitions relatively close in size (number of rows)?

The Analysis topic will help to answer the questions and help you determine how to proceed.