Chapter 2. Data Dictionary

Table of Contents

2.1. Overview
2.2. Data Dictionary Structure
2.2.1. Base Tables
2.2.2. Static/Dynamic Views
2.3. Referencing Data Dictionary
2.3.1. Referenced by User
2.3.2. Referenced by SQL Parser/Query Optimizer
2.3.3. Referenced by Tibero
2.4. Updating Data Dictionary
2.4.1. DDL Statements

This chapter describes the concept, structure, reference and update procedure for the data dictionary.

2.1. Overview

A data dictionary contains information about data stored in a database for the efficient use of database management system (hereafter DBMS). It is used to maintain the metadata that defines and describes a series of data.

A data dictionary helps to eliminate errors or misinterpretation of data by categorizing and processing information about data.

A data dictionary consists of tables and views, and access privileges for data dictionary is granted by user type.

User TypeDescription
DBADirect access and update is permitted.
General UserOnly read access of views is permitted.

The following is the information stored in a data dictionary.

  • Database schema object information

    Tables, indexes, and views.

  • User information

    Name, permissions, etc.

  • Table and column properties

    Available disk space, column type, default values, etc.

  • Table integrity constraints

    Primary key, foreign key, NOT NULL, etc.

  • Disk structure information

    Tablespace memory, data file images, segments, users, tables, etc.

  • Histogram

    Frequently used column values, data distribution in a column, etc.

2.2. Data Dictionary Structure

A data dictionary in Tibero consists of the following components:

  • Base Tables

  • Views

    • Static View

    • Dynamic View

Using the previous structure helps to maintain the consistency of a database system, provide user convenience, and save storage space. A serious error can occur if a base table is mistakenly modified by a general user.

2.2.1. Base Tables

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.


The SYS user is created automatically in Tibero, and is the parent account of all users including DBA. The SYS user manages Tibero, and its role cannot be replaced by any users regardless of the privileges the user may have.

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:

  1. Object definition

    Insert a schema object into the base table _DD_OBJ.

  2. Table definition

    Insert a row into the base table _DD_TBL.

  3. Column definition

    Insert as many rows as the number of columns into the base table _DD_COL. If indexes or constraints have been declared, also insert each one of them into the base table.

2.2.2. Static/Dynamic Views

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.

2.3. Referencing Data Dictionary

A data dictionary can be referenced in Tibero in the following three ways:

  • Referenced by a user

  • Referenced by SQL parser and query optimizer

  • Referenced by Tibero

2.3.1. Referenced by User

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.


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.

USER_View with objects defined by the current user.
ALL_View with objects that are accessible to the current user.
DBA_View with all objects.


There are three view types and they all contain the same columns. However, USER_ view does not display the OWNER column.

2.3.2. Referenced by SQL Parser/Query Optimizer

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.

  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.

  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.

  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.

  • Use an outer loop for T1.

  • Use an outer loop for 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.

2.3.3. Referenced by Tibero

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.

2.4. Updating Data Dictionary

Updating the data dictionary involves updating its base tables. A user or even DBA cannot update through static views, and dynamic views can only be updated automatically or through another interface. General users cannot directly update the base tables.

A user or SYS user can update the data dictionary in a secure and consistent manner by using DDL. By using DDL, data dictionary can be updated consistently by considering other transactions that are running in the data dictionary module.

The database information can also be updated with the data dictionary within the data dictionary module.


The SYS user or a DBA user can only directly reference or update a base table, but this is not recommended.

2.4.1. DDL Statements

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.

  • Database

  • Table

  • Index, view, and synonym

  • User

  • Integrity constraint

  • Role

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.

DDL CommandDescription
GRANTGrant privileges to users.
REVOKERevoke privileges from users.
SET ROLEEnable or disable privileges for the current session..

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.


For information about DDL statements supported by Tibero, refer to Tibero SQL Reference Guide.