|
|
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
OMNIDEX |
Partitioning |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
By Column into Equal Partitions Generate Environment File Entry
|
ExamplesThese examples demonstrate partitioning a table :
The "By Column" and "Into Equal Partitions" examples are not very efficient approaches to partitioning but are shown to demonstrate exactly how each individual action affects the partitioning. Combining both methods, however, can produce a very effective partitioning scheme, as shown in the "By Column into Equal Partitions" example. The "Using a Validation Column" and "Using Expressions" examples are good demonstrations of flexible and effective partitioning schemes. The orders table in these examples has 200 rows of order details. In each example, notice the command "view partition.rpt". This command simply displays the contents of the partition.rpt text file generated by ODXSQL when the PARTITION command is executed.
Partition by ColumnThe table is partitioned into two partitions, because the status column has only two unique values. This partitioning approach is ineffective because 96% of the records are in the first partition. No performance is achieved in this example. There may be circumstances when this approach is appropriate, for example, partitioning on a YEAR column. This example is shown to demonstrate exactly how this approach affects the partitions. See the third example below for a better, more effective approach. Note that the "in dat" statement instructs ODXSQL to place the partitions into the dat subdirectory of the current working directory. This clause is optional. >partition orders by status in dat >view partition.rpt
Into Equal PartitionsThe next example shows partitioning a table into equal partitions, not by column. By itself, this method of partitioning is not very effective because all of the partitions will be searched for every query. It is shown to demonstrate exactly how this method affects partitions. See the third example below for a better partitioning approach. Note that the "in dat" statement instructs ODXSQL to place the partitions into the dat subdirectory of the current working directory. This clause is optional.
>partition orders into 5 in dat >view partition.rpt
Into Equal Partitions By ColumnThe next example shows partitioning a table into equal partitions by a column. This method combines the first two methods shown above. It is more effective than both previous approaches because it limits the number of partitions, the partitions are divided as evenly as possible, and unique values in the partition qualifier to be stored together in a single partition. This last item causes queries that specify criteria against the partition qualifier to search only one partition. Note that the "in dat" statement instructs ODXSQL to place the partitions into the dat subdirectory of the current working directory. This clause is optional.
>partition orders into 5 by product_no in dat >view partition.rpt
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Environment
Catalog  |
Database
Integration |
Omnidex
Text |
Other
Features |
| Environment |
| Database |
| Table |
| Column |
| Rule |
| Index |
| Message |
| SQL Server |
| Oracle |
| DB2 |
| Flat Files |
| External Documents |
| Configuration |
| Keyword Searches |
| Synonym Searches |
| Proximity Searches |
| Form Searches |
| Misspelling Searches |
| Spell Check Searches |
| Phonetic Searches |
| Exporting Data |
| Pseudo-columns |
| Partitioning |
By
Column