Step 3 - Environment File Entry
Defining a partitioned table in an environment file must be done correctly
in order for Omnidex to understand the partitioning scheme. This will
enable Omnidex to search only those partitions relevant to the query,
ignoring the other partitions.
ODXSQL can automatically generate the environment file entries for a
partitioned table, if the PARTITION command is used to partition the table.
The WITH options, ENVIRONMENTFILE=filename and ENVIRONMENTSNIPPET=filename,
will generate an environment source file for the entire database including
the partitioned table or a "snippet" for just the partitioned
table, in the specified output file.
The new environment file can be modified for use in place of the existing
environment file. The environment snippet can be cut and pasted into the
existing environment source file. In both cases, the environment file
must be recompiled, indexes reinstalled for the existing tables, and indexes
installed and built for the partitioned table.
If
the order of the tables defined in the environment file changed, the indexes
will have to be rebuilt for the entire database. If the table order did
not change, meaning the partitioned table was added to the end of the
database definition, the indexes must be built only for the partitioned
table.
Entry
The environment file entry consists of a table definition that UNIONs
all of the partitions together, and a separate table definition for each
partition.
The UNION table definition:
- is named the same as the original unpartitioned table. This name will
be used in SQL statements.
- references all of the partitions in a UNION ALL statement.
- has the detailed column definitions. All of the partitions will use
these same column definitions, either by referencing them with the "columns
from table" clause or by physically copying the column definitions
into each partition definition.
- will NOT be indexed. The indexes are installed on
the partitions only.
There will be a separate partition table definition for each partition.
Each partition table definition:
- will have a physical clause that points to the physical partition
file.
- will have partitioned by clause containing the partition qualifier
for that partition.
- will have foreign key declarations referencing parent table keys
- will have either a columns from clause referencing the UNION table
or a duplicate copy of the column definitions from the UNION table.
Top
|