A base table contains normalized data with duplicate rows removed. Some data in a base table are represented as a flag and encoded. A base table also stores its own data.

Since a base table contains normalized data, it contains hardly any duplicate rows. A base table helps reduce space needed for data dictionary and prevents duplicate rows from being updated inconsistently.

Only DBAs can directly reference a base table data. General users must use a static/dynamic view to access the data because the SYS user is the owner of all base tables of the data dictionary.

Only DBA can update the base tables. General users must use DDL to update base tables, and must possess the permissions to execute DDL on the target object. For example, a user must possess update permission on the EMP table in order to update its foreign key column.

Since the data dictionary manages its base tables, a user can update the data dictionary using DDL without any knowledge about the base tables.

For example, a user can create a base table by defining the following items:

A static view is defined using one or more base tables and static/dynamic views, which is created by joining the two.

A dynamic view contains dynamic data changes in Tibero. Examples of dynamic changes include current server process state, transaction execution state, user privileges, etc. Dynamic changes are not saved to disc, but are created and displayed each time a user queries a dynamic view.

A general user can reference the data dictionary by using a static/dynamic view by executing a SQL statement that queries the view.

The following example queries the names of all user-defined tables from the static view, USER_TABLES, by executing the following SQL.

SELECT TABLE_NAME FROM USER_TABLES;

A user can reference the data dictionary by using views as in the previous example.

Schema object related static views are divided into the following types and prefixes according to user access levels.

SQL Parse

Tibero uses the data dictionary to parse user's SQL. This is called the semantic analysis process.

The semantic analysis checks if the schema objects of a SQL exist, if the current user can access them, the columns of each table, and if the column types are valid for the relevant operator.

The following example SQL is used to describe the semantic analysis process.

SELECT C11
  FROM T1, T2
  WHERE C11 = C21 AND C22 LIKE '20%';

① Check that the schema objects T1 and T2 in the SELECT statement exist, and that the current user can access them.

② If they are accessible, check that the columns C11, C21, and C22 are defined in T1 or T2, or are all defined in both T1 and T2 (or if column names are ambiguous).

③ Check if C11 and C12 columns can be compared, and that C22 is a character string type that can be used with the LIKE operator.

After going through semantic analysis, the previous SQL is converted to the following SQL that explicitly specifies the table names of each column to remove ambiguity.

SELECT T1.C11
  FROM T1, T2
  WHERE T1.C11 = T2.C21 AND T2.C22 LIKE '20%';

Query Optimizer

Tibero uses query optimizer to generate optimal execution plans for SQL by referencing the stats in the data dictionary.

The stats are saved in the base tables of the schema object and histogram.

For example, the base table for table definitions contains information such as the number of rows, disk blocks, and the average number of rows in a disk block within each table. The histogram base table contains information such as the data distribution and frequently used values of specific columns.

The query optimizer references index information, such as which columns are indexed and the average number of records in terminal nodes of indexes.

The following SQL checks for the data dictionary information used by the query optimizer.

SELECT *
  FROM T1, T2
  WHERE T1.C1 = T2.C2;

As in the previous example, the SELECT statement performs a join using the WHERE condition. In general, a join operation requires a lot of system memory usage and CPU time. Hence, it is important to find an optimal join operation execution plan that minimizes memory usage.

The following describes the execution plan for performing a nested-loop join of T1 and T2.

In general, using a table with fewer number of rows as the outer loop table reduces the operation cost. The query optimizer references the data dictionary to determine which table between T1 and T2 has fewer number of rows and uses it as the outer loop table for the SQL execution.

If the query optimizer references the data dictionary and determines that indexes exist on both T1.C1 and T2.C2, it selects the sort-merge join, which requires less memory and CUP usage, instead of the nested-loop join.

In addition to for semantic analysis and sql optimization, data dictionary is also used when executing SQL statements.

The results of semantic analysis and query optimization processes are not related to the actual data that are saved in the tables. However, there may be further operations to perform while executing the SQL depending on the actual data.

For example, if foreign key of T2 is defined in T1, the data dictionary must be referenced to check if the foreign key exists on the column in T2 when deleting a row from T2.

If there is a row in T1 that references a column in T2, Tibero must perform an action such as preventing the deletion of the row in T2, or also delete all rows in T1 that references the row in T2. The data dictionary is used to determine which action to perform.

DDL statement is a data definition language, which is used to define the data structures in a database, is a SQL statement that defines the relationship between data items or objects. DDL can be used to create, alter, or delete a schema object, grant or revoke privileges, add comments, or truncate a table.

Each statements begins with CREATE, ALTER, DROP, TRUNCATE, GRANT, REVOKE, COMMENT, or others. There are many more DDL commands.

The following objects can be used in a CREATE, ALTER, or DROP DDL statement in Tibero.

Use CREATE TABLE, ALTER TABLE, and DROP TABLE commands on tables, while other objects may be limited for use with only some of these commands. For example, synonyms can only be used with CREATE SYNONYM and DROP SYNONYM, but not with ALTER SYNONYM.

The following are task-specific DDL commands that are not related to creating, altering, or dropping objects.

The data dictionary updates performed through these commands are saved in the base tables. The results can be checked in a static view, and some results in a dynamic view.