Partitioning

Step 2 - Partition the Table

After determining the best approach to partitioning the table, the table must be physically partitioned. The ODXSQL utility's PARTITION command can be used to perform this task.

Omnidex does not require that the actual partitioning of the data be performed using the ODXSQL utility. It is provided as a convenience and is recommended because it can perform the partitioning and at the same time, generate the environment file entries necessary to use and index the partitions.

This topic focuses solely on partitioning with ODXSQL.

ODXSQL PARTITION Command

The ODXSQL PARTITION command can physically partition a table according to the specified criteria, generating reports detailing exactly how the data was partitioned. At the same time, it can generate the environment file entries necessary to index and search the partitioned table.

No matter what the underlying database type, the partitions will be created in flat files. These files will be created in either the current working directory or the specified location, if the "in location" clause is passed with the PARTITION command.

The PARTITION command can divide the table in several different ways:

Partition by column

Partition into equal partitions

Partition by column into equal partitions

Partition by valid values

Partition by an expression

To assist in the analysis step (Step 1 - Analysis), the REPORT_ONLY option is available for use with the PARTITION command. This will cause ODXSQL to simulate the partition, generating the reports without physically partitioning the table.

Partition by Column

Partitioning by a column will divide a table into partitions by unique column values. For example, when partitioning by a state column, the table will be split into 50 different partitions, assuming at least one record for each state exists in the table.

In some instances, this type of partition may be appropriate. However, it does not provide any assurance that the data will be divided evenly or efficiently. For example, partitioning an orders table by the product number without specifying the number of partitions to divided the table into will cause ODXSQL to create a separate partition for each distinct product number.

The example will demonstrate how this partition type is performed.

Partition into Equal Partitions

Partitioning into equal partitions will divide a table into the specified number of partitions. However, since no column is specified to be used as the partition qualifier, there is no particular partitioning logic other than an equal or very similar number of rows in each partition.

In some cases, this may be valid. But without a partition qualifier, queries against this partition will result in a search against all partitions, which negates the benefit of partitioning the data.

The example will demonstrate how this partition type is performed.

Partition by Column into Equal Partitions

Partitioning by column into equal partitions will group table data by the unique column values of the specified column. It then combines the groups together into the specified number of partitions in such a way that the total number of rows in each partition is uniform, as close as possible.

This ensures that all rows for a particular column value are kept together in a single partition. When a query is processed against the partitioned table and criteria is specified for the partition column, only the table containing that criteria value will be searched.

The example will demonstrate how this partition type is performed.

Partition by Valid Values

Partitioning a table using valid values places invalid values into a partition of their own. First export all valid values for the partition qualifier to an OST, attach the OST, then use the validation_column='osttable.column' WITH option in the partition statement.

The partition should be designed in the same manner as described above, partitioning by column and specifying the number of partitions to generate. Validating the data, however, helps to clean up bad data, which can further improve performance.

The example will demonstrate how this partition type is performed.

Partition by an Expression

Partitioning a table using an expression provides a little more flexibility in determining how the table will be partitioned. The partition must still occur on a single column, but an expression such as "substring" can be used to determine how the data will be divided.

Once the expression is evaluated, the data is grouped together into either the specified number of partitions or individual partitions for each expression result.

The example will demonstrate how this partition type is performed.

Top