Overview
Partitioning is a common RDBMS strategy that turns a large set of data
into smaller, logical subsets. The most common objectives for partitioning
databases are to make an application perform better and to make the data
easier to administrate.
Omnidex supports partitioning in three ways:
- physical partitioning of data into two or more separate partitions
- partitioning of indexes using composite MDK indexes
- partitioning of indexes using composite ASK indexes
All three partitioning concepts can be used together or separately and
are discussed in detail in this section.
What if my data is already partitioned? 
Advantages
Partitioned data can return extraordinary performance on extremely large
databases. A partitioned table is broken up into smaller pieces, making
the individual table sizes smaller. For example, a table containing 10
billion rows partitioned into 10 partitions, creates 10 one billion row
tables. Searching one billion rows is considerably faster than searching
10 billion rows, and even faster when installed with Omnidex indexes.
Physical index files are smaller, narrowing the number of index key values
Omnidex must look through to qualify data.
Index build time can be shorter. For example, a partition can be made
up of updates for a given period of time. The new partition can be appended
to the end of the existing partitions. The indexes for this new partition
can be built without the need to rebuild the indexes on the other partitions.
Disadvantages
Advance analysis is required to determine the best partitioning strategy,
and indeed if the data should be partitioned at all.
Continual analysis may be required to determine if the current partitioning
strategy continues to be the best strategy.
Since the data is physically separated into different tables, updates
to the data must be made to the respective partition. At this time, Omnidex
does not handle this automatically.
Limitations
- Only child tables can be partitioned.
- Only one partitioned child table per query is allowed.
- Select items containing multiple aggregations in a single expression
are not supported.
- Left outer joins TO a partitioned table are not allowed. However,
left outer joins FROM a partitioned table are allowed.
- Updates to partitioned data must be made to the individual partition.
Omnidex does not handle this automatically. See Updating
Partitioned Tables (below) for more information.
- MDK Composite keys are limited to 240 bytes.
- Aggregation indexes are limited to 32 values in an IN clause.
- Since each partition will have its own set of indexes and the number
of index files is limited to 255 physical files, the number of partitions
can be limited.
Updating Partitioned Tables
Applications that update partitioned tables must take some extra steps
to make sure the updates are made to the correct partition. Unlike select
statements, insert, update and delete statements must reference the individual
partition, not the unioned table.
For example, if the prospects table is partitioned into 5 partitions,
where the first partition contains only prospects from the state of California,
the second partition contains only prospects from the states of New York
and New Jersey, and so one, the application would have to determine which
partition to perform the update against according to the state.
If a prospect from the state of California is inserted into the second
partition, which contains only prospects from New York and New Jersey,
the new record would never be qualified by any select statement.
Therefore, the update application must determine which partition should
be updated.
What if my data is already partitioned?
If the data is already partitioned, analysis is still the first and most
important step.
- Is there a partition qualifier?
- Does the partition structure meet the needs of all of my queries?
- Are the partitions relatively close in size (number of rows)?
The Analysis topic will help to answer the
questions and help you determine how to proceed.
Top
|
