SAND CDBMS SQL Reference Guide
SAND CDBMS SQL Database Objects and Language Elements

 

Previous Topic:
Columns and Tables
Chapter Index
Next Topic:
Schemas

 

Domains


Domains are created with a CREATE DOMAIN statement that specifies the name of the domain and its data type; they can subsequently be used as the domain argument for table columns in CREATE TABLE and ALTER TABLE statements.
Each database also contains built-in domains (called system domains) that encompass the basic data types. A system domain is used whenever a column is defined with reference to a data type, instead of an explicit domain, in CREATE/ALTER TABLE statements.

Whenever a value is entered into a column — using, for example, an INSERT or UPDATE statement — SAND CDBMS stores the value in the domain specified for that column (unless it already exists in the domain), and creates a reference associating the domain value with the particular column location where it is used. If the value already exists in the domain, only the reference to the column location is created. All values inserted into a particular column must conform to the data type of the domain.

Two columns defined on the same domain will store their respective values together in that domain. For example, suppose that two tables, state and president, have columns containing two-character abbreviations for state: the president table has a column for birth-state, bstateab, and the state table has a column called ab. Both columns store their values in the same domain, which might have the name abbr and the data type char(2). Because the domain stores each distinct value only once, a value that is used by both columns is referenced by both columns, but does not occur more than once in the domain.


To optimize performance, columns that may be paired in join predicates should be based upon a common domain.

A special type of domain, called a distributed domain, is associated with data partitioning and MPP. Because a dimension table is replicated across nodes, any explicit domains used in the field definitions of the table on the head node must also exist on the remote nodes, or else the dimension table creation will fail. While explicit domains for dimension tables created in MPP mode using the CREATE TABLE command are automatically replicated on the remote nodes, this is not the case when standard tables are converted to dimension tables via the ALTER TABLE command. Distributed domains are one solution to this problem, as they are automatically replicated across the remote nodes associated with a partitioned table. It is therefore suggested that distributed domains be used when potential dimension tables are defined, if explicit domains are required.

Distributed domains are created with the CREATE DOMAIN command. In MPP mode, the domain will be copied automatically to the remote nodes associated with the default partitioned table. Outside of MPP mode, or to specify a partitioned table other than the default, the DIMENSION OF partitioned-table clause must be included in the CREATE DOMAIN statement. The CREATE LOCAL DOMAIN syntax is used to create a non-distributed domain while in MPP mode.

The DBA, and users with DBA privileges, can access information about all domains in the database by querying the system table SYSTEM.DOMAINS. Users without DBA privileges may query the system view PUBLIC.DOMAINS for information about all the domains they own or on which they possess privileges. Refer to Appendix C: SAND CDBMS System Tables/Views for more information about system tables and views.

 

Previous Topic:
Columns and Tables
Chapter Index
Next Topic:
Schemas