Partitioning

Step 1 - Analysis

The first and most important step in partitioning a table is analysis to determine if the table should be partitioned, and if so, how. Analyze the queries that will run against the partitioned table to check for SQL limitations and to determine the best partitioning approach.

• Choosing which field to partition the data on is critical. At least half of all queries against the partitioned table should apply criteria to one particular field, the partition qualifier. This might be a state, a year or a department field, or some other "group" field.
  Running queries against a partitioned table that do not apply criteria to the partition qualifier field, will search every partition, negating the performance benefit.
• The size of each partition should be fairly consistent. For example, if an orders table is partitioned on the status field with the values PENDING, SHIPPED, CANCELED, BACKORDERED, the "SHIPPED" partition will be considerably larger than the other three partitions, causing a potentially severe fluctuation in performance depending on which partition is hit.
  To keep size consistent, consider grouping unique values together. For example, when partitioning on a state field, the states of California and New York will likely have more records that the states of Nebraska, Wyoming, and Montana. While California and New York can each have their own partitions, Nebraska, Wyoming and Montana can be grouped into a single partition.
• In the Omnidex environment file, the partition qualifier must be specified with a PARTITIONED BY clause (see Step 3). If omitted, every query against the partitioned table will search every partition, negating any performance gains that may have been achieved.
  Likewise, all queries searching against the partitioned table must include the partition qualifier in a WHERE clause predicate, or every partition will be searched, again negating the performance gains. However, multiple partition qualifiers can be included in the same WHERE clause predicate, without degrading performance.
  SELECT ... FROM table WHERE state IN ('CO','CA','FL','TX)
  This statement will search only in partitions where the PARTITIONED BY clause includes one or more of these states. Other partitions will be ignored.
• Although the number of partitions is not explicitly limited, DISC recommends the number of partitions be limited to between 8 and 16 partitions, depending on the amount of data. Too many partitions could hinder performance.

The Omnidex utility, ODXSQL, can assist in determining the best partitioning approach by simulating the partition in a variety of ways and generating a report detailing exactly how the data would be partitioned. When you've completed your analysis, ODXSQL can then physically partition the table for you and generate the necessary environment file entries to represent the new partitions. See Step 2 - Partition the Table, for more information on using the ODXSQL PARTITION command.

Partition Qualifier

A partition qualifier is the value on which the partition will be based. For example, if the table is partitioned by state, the partition qualifier for each partition will be the state or states stored in that particular partition.

The partition qualifier field should be a reasonably low-cardinality field, meaning the number of unique values in that field for each partition should be low.

The field used for the partition qualifier must be a reasonably low cardinality field. When the partitions are created, each individual partition should have no more than 256 unique values in the partition qualifier field. This is important for performance.

For example, partitioning a customers table based on social security number is not very efficient because every social security number is unique. A social security number field has very high cardinality. However, partitioning the same table based on state makes more sense since there are only 50 states.

Top