This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
design:partitioning:intro [2009/07/17 22:30] admin |
— (current) | ||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | ====== Partitioning ====== | ||
| - | |||
| - | ==== Partitioning ======= | ||
| - | |||
| - | 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 contaning 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 Jersery, 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. | ||
| - | |||
| - | Multiple-predicate partitioning | ||
| - | |||
| - | - Union View and Grid partitioning can now use multiple predicates | ||
| - | - Predicates must be AND’d | ||
| - | - Addin will determine which partitions to use based on the extent that one or more predicates is met. | ||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | Example of multiple-predicate partitioning | ||
| - | |||
| - | |||
| - | node GRID01 | ||
| - | database LIST filedsn "list01.dsn" local cache | ||
| - | cluster | ||
| - | table LIST partition by "STATE in ('MA','ME','NH','NY','PR','RI','VT') and | ||
| - | ZIP between '00000' and '05999'" | ||
| - | |||
| - | node GRID02 | ||
| - | database LIST filedsn "list02.dsn" local cache | ||
| - | cluster | ||
| - | table LIST partition by "STATE in ('CT','NJ','NY') and | ||
| - | ZIP between '06000' and '10999'" | ||
| - | |||
| - | node GRID03 | ||
| - | database LIST filedsn "list03.dsn" local cache | ||
| - | cluster | ||
| - | table LIST partition by "STATE in ('NY') and | ||
| - | ZIP between '11000' and '14999'" | ||
| - | |||
| - | |||
| - | |||
| - | - Queries with criteria of STATE = ‘VT’ will hit first partition only | ||
| - | - Queries with criteria of ZIP = ‘10000’ will hit second partition only | ||
| - | - Queries with criteria of STATE = ‘NY’ will hit all three partitions. | ||
| - | - Queries with criteria of STATE = ‘NY’ and ZIP = ‘10000’ will hit the second partition only. | ||
| - | |||
| - | |||
| - | |||
| - | ====== Quick Links ====== | ||
| - | ^ [[:start|Quick Links]] ^^^^^ | ||
| - | ^ General ^ Programs ^ Interfaces ^ General Topics ^ Miscellaneous Items^ | ||
| - | | [[overview:intro|Omnidex Introduction]] | [[programs:odxsql:intro|OdxSQL]] |[[development:interfaces:odbc:intro|ODBC]] | [[design:grids:intro|Grids]] | [[releasenotes:intro|Release Notes]] | | ||
| - | | [[odxadmin:intro|Administrating Omnidex (OdxAdmin)]] | [[programs:odxnet:intro|OdxNet]] |[[development:interfaces:jdbc:intro|JDBC]] | [[design:unionview:intro|Union Views]]| [[osinstall:intro|Server Installs]] | | ||
| - | | [[design:intro|Data and Index Design]] | [[programs:odxaim:intro|OdxAim]] |[[development:interfaces:storedproc:intro|Stored Procedures]] | [[design:rolluptable:intro|Rollup Tables]] | [[osinstall:licensing:intro|Licensing]] | | ||
| - | | [[sql:intro|Omnidex Queries & SQL]] | [[programs:dbinstal:intro|DBInstal]] |[[development:interfaces:omniaccess:intro|OmniAccess]] | [[design:partitioning:intro|Partitioning]] | [[osinstall:settings:intro|Settings]] | | ||
| - | | [[activecounts:intro|Active Counts]] | [[programs:oaenv:intro|OaComp/decomp/helper]] |[[development:interfaces:clientoa:intro|Client OA]] | [[development:explainplan:intro|Explain Plans]]| [[glossary:intro|Glossary]] | | ||
| - | | [[powersearch:intro|PowerSearch]] | [[programs:client:intro|DSEdit/OdxQuery]] | [[rdbms:intro|RDBMS Indexing]] | [[development:debugging:intro|Debugging]] | [[appendix:intro|Appendix]] | | ||
| - | (c) Copyright Dynamic Information Systems - This document was last updated July 15, 2009. | ||
| - | |||
| - | ---- | ||