|
|
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
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.
Using a Validation ColumnIn this example, the a list of "valid" product numbers is retrieved from the database and exported to an OST (Omnidex Stand-alone Table) using the ODXSQL EXPORT command. This OST is then "ATTACHed" and used with the PARTITION command in a WITH options clause. When the data is partitioned, all records with "invalid" product numbers will be stored in a partition together, while records with valid product numbers will be stored in partitions according to the settings in the PARTITION command. The partition with invalid product numbers can be removed, updated with correct data, or ignored. The partition.rpt file is now slightly different from other example. the "Valid" and "Valid %" columns have more meaningful data in them, as shown below. 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. First, valid product numbers are selected and exported to an ost named x. The physical export file name will be x.ost. > export (select product_no) from products where product_no
>= 'A') to x with ost, delete Next, the ost is attached and named "VALID" so it can be referenced in the PARTITION command's WITH clause. > attach ost x as VALID Finally, the PARTITION command is executed using the WITH option VALIDATION_COLUMN='VALID.product_no' >partition orders into 5 by product_no in dat with validation_column='valid.product_no' The records with valid product numbers are partitioned between the first 4 partitions. The records with invalid product numbers are grouped together in the 5th partition. >view partition.rpt
Using ExpressionsIn this example, an expression is used to partition the table according to the first letter in the product number. This is useful if the product numbers or in some way codified. For example, all computers and computer accessories begin with the letter C and furniture begins with the letter F. This expression keeps these items grouped together in the partitions. 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 "substring(product_no
from 1 for 1)" 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