This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
admin:features:grids:creation:sql [2011/01/11 02:31] els |
admin:features:grids:creation:sql [2012/10/26 15:00] (current) |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| + | ~~NOTOC~~ | ||
| {{page>:top_add&nofooter&noeditbtn}} | {{page>:top_add&nofooter&noeditbtn}} | ||
| Line 5: | Line 6: | ||
| ===== Omnidex Grids ===== | ===== Omnidex Grids ===== | ||
| - | **[[admin:features:grid:creation|Back to Grid Creation]]** | + | **[[admin:features:grids:creation|Back to Grid Creation]]** |
| ---- | ---- | ||
| Line 25: | Line 26: | ||
| === 2. Partition the database === | === 2. Partition the database === | ||
| - | If the database will be logically partitioned, then create the relational views that correlate with the partitioning strategy. If the database will be physically partitioned using an Omnidex Snapshot, then partition the database using the PARTITION command. | + | If the database will be logically partitioned, then create the relational views that correlate with the partitioning strategy. If the database will be physically partitioned using an Omnidex Snapshot, then partition the database using the PARTITION statement. |
| - | The PARTITION command is available in the Omnidex SQL language, and is callable from the OdxSQL application. Since the command is in the Omnidex SQL language, it may also be called directly from ODBC and JDBC. | + | The PARTITION statement is available in the Omnidex SQL language, and is callable from the OdxSQL application. Since the statement is part of the Omnidex SQL language, it may also be called directly from ODBC and JDBC. |
| - | The PARTITION command allows the table to be partitioned one of three ways, 1) into a specific number of equally sized partitions, 2) into separate partitions based on a partition qualifier consisting of a column or a SQL expression, or 3) a combination of these two steps. In the third case, Omnidex will evaluate the cardinality of the partition qualifier and group values together to comprise the specific number of partitions in as equal size as possible. The syntax of the PARTITION command is: | + | The PARTITION statement allows the table to be partitioned one of three ways, 1) into a specific number of equally sized partitions, 2) into separate partitions based on a partition qualifier consisting of a column or a SQL expression, or 3) a combination of these two steps. In the third case, Omnidex will evaluate the cardinality of the partition qualifier and group values together to comprise the specific number of partitions in as equal size as possible. The syntax of the PARTITION statement is: |
| PARTITION table [INTO n [PARTITIONS]] [BY column] | PARTITION table [INTO n [PARTITIONS]] [BY column] | ||
| Line 35: | Line 36: | ||
| [WITH options] | [WITH options] | ||
| - | An example of a command to partition a table is: | + | An example of a statement to partition a table is: |
| PARTITION LIST BY “STATE || SUBSTRING(ZIP from 1 for 2)” | PARTITION LIST BY “STATE || SUBSTRING(ZIP from 1 for 2)” | ||
| IN D:\grid\data | IN D:\grid\data | ||
| - | === 3. Distribute both the partitioned and partitioned data across the grid. === | + | === 3. Distribute the partitioned data across the grid. === |
| If the database will be logically partitioned, then make sure each grid server has access to the database. If the database will be physically partitioned, then distribute both the partitioned and unpartitioned data to the grid nodes. If each node will be in its own directory, then the data files can be copied or symbolically linked to the correct locations. It is also possible for multiple nodes to share the same physical directories as long as the partitioned data can be differentiated. | If the database will be logically partitioned, then make sure each grid server has access to the database. If the database will be physically partitioned, then distribute both the partitioned and unpartitioned data to the grid nodes. If each node will be in its own directory, then the data files can be copied or symbolically linked to the correct locations. It is also possible for multiple nodes to share the same physical directories as long as the partitioned data can be differentiated. | ||
| Line 50: | Line 51: | ||
| == The CREATE ENVIRONMENT statement == | == The CREATE ENVIRONMENT statement == | ||
| - | The CREATE ENVIRONMENT statement is used to declare the grid nodes and their connection information. The NODE declarations in the CREATE ENVIRONMENT statement are show below. Consult the documentation on the [[admin:environments:create:sql:syntax#creat_environment | CREATE ENVIRONMENT]] statement for the complete syntax. | + | The CREATE ENVIRONMENT statement is used to declare the grid nodes and their connection information. The NODE declarations in the CREATE ENVIRONMENT statement are show below. Consult the documentation on the [[dev:sql:statements:create_environment:home | CREATE ENVIRONMENT]] statement for the complete syntax. |
| <code> | <code> | ||
| Line 64: | Line 65: | ||
| Here is an example of a CREATE ENVIRONMENT statement for an Omnidex Grid. | Here is an example of a CREATE ENVIRONMENT statement for an Omnidex Grid. | ||
| - | <code> | + | <code sql> |
| create environment "list_env" | create environment "list_env" | ||
| maxthreads 4 | maxthreads 4 | ||
| Line 76: | Line 77: | ||
| == The CREATE DATABASE statement == | == The CREATE DATABASE statement == | ||
| - | The CREATE DATABASE statement is used to declare the database characteristics for each node. The NODE declarations in the CREATE DATABASE statement are show below. Consult the documentation on the [[admin:environments:create:sql:syntax#create_database | CREATE DATABASE]] statement for the complete syntax. | + | The CREATE DATABASE statement is used to declare the database characteristics for each node. The NODE declarations in the CREATE DATABASE statement are show below. Consult the documentation on the [[dev:sql:statements:create_database:home | CREATE DATABASE]] statement for the complete syntax. |
| <code> | <code> | ||
| Line 93: | Line 94: | ||
| Here is an example of a CREATE DATABASE statement for an Omnidex Grid. | Here is an example of a CREATE DATABASE statement for an Omnidex Grid. | ||
| - | <code> | + | <code sql> |
| create database "list" | create database "list" | ||
| node NODE01 | node NODE01 | ||
| Line 111: | Line 112: | ||
| == The CREATE TABLE statement == | == The CREATE TABLE statement == | ||
| - | The CREATE TABLE statement is used to declare the table characteristics for each node for each partitioned table in the grid. The NODE declarations in the CREATE TABLE statement are show below. Consult the documentation on the [[admin:environments:create:sql:syntax#create_table | CREATE TABLE]] statement for the complete syntax. | + | The CREATE TABLE statement is used to declare the table characteristics for each node for each partitioned table in the grid. The NODE declarations in the CREATE TABLE statement are show below. Consult the documentation on the [[dev:sql:statements:create_table:home | CREATE TABLE]] statement for the complete syntax. |
| <code> | <code> | ||
| Line 127: | Line 128: | ||
| Here is an example of a CREATE TABLE statements for an Omnidex Grid. | Here is an example of a CREATE TABLE statements for an Omnidex Grid. | ||
| - | <code> | + | <code sql> |
| create table "HOUSEHOLDS" | create table "HOUSEHOLDS" | ||
| node NODE01 | node NODE01 | ||
| Line 157: | Line 158: | ||
| As discussed in the [[admin:features:grids:partitions#how_are_referential_constraints_handled_such_as_primary_and_foreign_keys| partition scheme]], it is necessary to partition tables so that referential integrity is maintained. Typically, the parent is partitioned using criteria, as shown for HOUSEHOLDS above, and the child is partitioned along the foreign key. The PARTITION BY clause for a child shows the join criteria, as shown below: | As discussed in the [[admin:features:grids:partitions#how_are_referential_constraints_handled_such_as_primary_and_foreign_keys| partition scheme]], it is necessary to partition tables so that referential integrity is maintained. Typically, the parent is partitioned using criteria, as shown for HOUSEHOLDS above, and the child is partitioned along the foreign key. The PARTITION BY clause for a child shows the join criteria, as shown below: | ||
| - | <code> | + | <code sql> |
| create table "INDIVIDUALS" | create table "INDIVIDUALS" | ||
| node NODE01 | node NODE01 | ||
| Line 177: | Line 178: | ||
| - | ==== 5. Install and build Omnidex indexes on each node. ==== | + | === 5. Install and build Omnidex indexes on each node. === |
| It is recommended, though not required, that each node be indexed the same. This reduces the likelihood of queries taking too long because of a lack of indexing on one node. | It is recommended, though not required, that each node be indexed the same. This reduces the likelihood of queries taking too long because of a lack of indexing on one node. | ||
| Line 196: | Line 197: | ||
| + | ==== ==== | ||
| **[[admin:features:grids:creation|Prev]]** | **[[admin:features:grids:creation|Prev]]** | ||