User Authorizations and Privileges
Columns and Tables
All data in SAND databases are stored in the form of tables. Tables are defined as having one or more columns (attributes), and may contain zero or more rows (data records). Columns are assigned to tables using either the CREATE TABLE statement or the ALTER TABLE statement. Rows of data values are inserted into tables using the INSERT statement, and can be changed using UPDATE or DELETE statements.
Every column included in a CREATE TABLE statement must have a domain specification. A column is based upon only one domain, and each distinct value that occurs in the column is stored only once in its associated domain. The domain argument for a particular column may be either a named domain (previously created by the user with a CREATE DOMAIN statement) or a valid SAND CDBMS data type.
The DBA, and users with DBA privileges, can access information about all tables in the database by querying the system table SYSTEM.TABLES. Users without DBA privileges may query the system view PUBLIC.TABLES for information about all the tables they own or on which they possess privileges. Refer to the SAND CDBMS System Tables and Public Views section for more information about system tables and views.
Beyond the standard (base) table, there are other possible types of tables in a SAND CDBMS database:
A snapshot table is, essentially, a searchable record of the data in one or more source tables or views from when the snapshot table was created. A snapshot table has characteristics of both a view and a standard table. It is created through a CREATE SNAPSHOT TABLE statement that, like the CREATE VIEW command, specifies a query expression which references one or more tables or views. Unlike a view, however, a snapshot table exists independently of its source tables. Queries executed against a snapshot table are usually processed many times faster than with a comparable view, since joins and other time-consuming computations were already performed at snapshot creation time.
As with a standard table, records in a snapshot table can be updated or removed, and new records can be added. A special type of update (via a REFRESH SNAPSHOT TABLE statement) can refresh the data in the snapshot table if the source tables have been changed. The REFRESH SNAPSHOT TABLE command can also be used to undo all manual changes to the snapshot table, even if the underlying tables have not been modified.
Snapshot tables that are no longer required can be removed through the DROP SNAPSHOT TABLE command.
The user DBA, and users with DBA privileges, can access information about all snapshot tables in the database by querying the system table SYSTEM.TABLES with the following condition in the query’s WHERE clause:
Users without DBA privileges may query the system view PUBLIC.TABLES for information about all the snapshot tables they own or on which they possess privileges.
A linked table provides access to a table contained in a remote database. Users of the local database can query this linked table to fetch data from the remote table. Unlike a standard table, a linked table does not itself contain data records; it only points to a particular table or joined tables in another database. A linked table is created with the CREATE TABLE...WITH CONNECTION command, which specifies either the name and schema location of the linked table or a SELECT statement that will be passed to the remote database, the connection object that will be used to connect to the remote database (previously created with a CREATE CONNECTION statement), and the remote table that will be associated with this linked table. Refer to the Federated Databases section for more information about linked/remote tables.
The user DBA, and users with DBA privileges, can access information about all linked tables in the database by querying the system table SYSTEM.TABLES with the following condition in the query’s WHERE clause:
Users without DBA privileges may query the system view PUBLIC.TABLES for information about all the linked tables they own or on which they possess privileges.
A partitioned table is a special type of table related to SAND CDBMS’s Massively Parallel Processing (MPP) functionality. This table, created on the head node of a multi-node system, does not itself contain data, but rather points to the remote tables that store the partitioned data. From the end-users’ perspective, a partitioned table looks and behaves like any other table in terms of querying. Internally, however, a query on a partitioned table is redirected to each remote node for parallel execution, after which the results are collated and returned to the client transparently.
The primary method of creating a new partitioned table is to execute the CREATE PARTITION TABLE command, specifying the following information:
- the partitioned table name
- (optional) the partitioned table column names (by default, the remote table column names are inherited if they are common to all tables)
- (optional) the partitioning strategy (ROUND ROBIN is the default; HASH and RANGE are the other choices)
- the list of linked tables that point to the remote table partitions
In MPP mode, a partitioned table can also be created using an existing partitioned table as a basis. A CREATE TABLE...SELECT statement that queries a partitioned table will result in a new partitioned table; and if a key field of the referenced partitioned table is projected, the new partitioned table will have that key.
Another way to create a partitioned table in MPP mode involves defining the partitioned table structure with the CREATE PARTITION TABLE command first, and then adding the partitions with an INSERT...SELECT statement that queries another partitioned table. The CREATE PARTITION TABLE command in this case includes standard column definitions in place of a list of linked tables (partitions).
After it is created, a partitioned table's structure and remote contents can be modified using the standard SQL commands DELETE, UPDATE, and ALTER TABLE.
To remove a partitioned table from the head node, simply execute the DROP TABLE command with the partitioned table specified. To delete the partitioned table and associated linked tables and remote tables, add the CASCADE keyword to the end of the DROP TABLE command.
The DBA, and users with DBA privileges, can access information about partitioned tables in the database by querying the system tables SYSTEM.PARTITIONS, SYSTEM.PARTITIONCOLS, and SYSTEM.PARTITIONTABS.
For more information about partitioned tables and MPP functionality, refer to the Massively Parallel Processing (MPP) chapter in the SAND CDBMS Administration Guide.
A dimension table is a distributed table used in conjunction with a partitioned table as part of "star" or "snowflake" schemas (in data warehousing terms). The exact same dimension table is replicated and maintained across nodes, allowing the partitioned table to be used as a central fact table with fast dimension lookups at each node.
A dimension table is created on the head node using either a CREATE TABLE statement (MPP mode only), or by converting an existing base table via an ALTER TABLE command. In MPP mode, the CREATE TABLE or ALTER TABLE...ADD DIMENSION command brings a new dimension table into existence and copies its structure to each node associated with the default partitioned table. In or outside of MPP mode, the ALTER TABLE...DIMENSION OF partitioned-table command converts the base table to a dimension table and copies its structure to each node associated with the specified partitioned table.
Since the table structure but not its contents is copied to the other nodes, a converted table that contains data requires the execution of a subsequent REFRESH DIMENSION TABLE command to replicate the data as well on the nodes.
Once the dimension table is created and identical across the partition nodes, all changes (via INSERT, UPDATE, DELETE, ALTER TABLE) to this table on the head node are propagated to the other nodes, whether or not in MPP mode. Removing the dimension table via the DROP TABLE command on the head node will cause the same table to be removed from the remote nodes also.
If a dimension table is no longer required as a dimension, it can be converted back to a standard table using the ALTER TABLE...DROP DIMENSION command on the head node. The corresponding tables on the remote nodes are not removed or changed by this command.
The DBA, and users with DBA privileges, can access information about dimension tables in the database by querying the system tables SYSTEM.DIMENSIONS.
User Authorizations and Privilegesx