Chapter 4. Schema Object Management

Table of Contents

4.1. Overview
4.2. Tables
4.2.1. Creating, Altering, and Dropping Tables
4.2.2. Efficient Table Management
4.2.3. Viewing Table Data
4.2.4. Compressing Tables
4.2.5. INDEX ORGANIZED TABLE
4.3. Constraints
4.3.1. Declaring, Changing, and Removing Constraints
4.3.2. Constraint States
4.3.3. Viewing Constraint Data
4.4. Disk Blocks
4.4.1. PCTFREE Parameter
4.4.2. INITRANS Parameter
4.4.3. Parameter Settings
4.5. Indexes
4.5.1. Creating and Deleting Indexes
4.5.2. Efficient Index Management
4.5.3. Viewing Index Data
4.5.4. Monitoring Index Usage
4.6. Views
4.6.1. Creating, Changing, and Deleting Views
4.6.2. Viewing View Data
4.7. Sequences
4.7.1. Creating, Changing, and Deleting Sequences
4.7.2. Viewing Sequence Data
4.8. Synonyms
4.8.1. Creating and Deleting Synonyms
4.8.2. Creating and Deleting Public Synonyms
4.8.3. Viewing Synonym Data
4.9. Triggers
4.9.1. Creating and Deleting Triggers
4.10. Partitions
4.10.1. Creating Partitions
4.10.2. Creating Composite Partitions
4.10.3. Creating Index Partitions
4.10.4. Viewing Partition Data

This chapter describes how to manage schema objects, which are the logical storage area needed to configure a database system with Tibero and explains the disk block, the minimum storage unit of the database.

The following are the major database schema objects:

A schema object is generated by a schema and is included in the schema. Objects that use physical space, like tables and indexes, are called segments.

The table is the basic schema object in a relational database.

Most database processing is done with tables, even if some data is described in different forms of schema objects. Therefore, the core of designing a relational database is the design of tables, and database performance is greatly affected by efficient table management.

The table is composed of the following two components:

ComponentDescription
ColumnCharacteristic of data to be saved in a table.
RowConstitutes a table. Multiple types of data are saved in a row.

To efficiently manage a database, tables should be accurately designed, with consideration for the arrangement and storage settings of the tables. Tibero allows the design of tables to be modified to some extent after they are created. However, it becomes expensive to process a table design modification, so avoid frequent changes.

To design tables correctly, users need to go through the normalization process and specify proper integrity constraints. For example, if duplicate data will be allowed in multiple tables to avoid using the JOIN operator, integrity constraints should be considered and made in the design process.

This section describes how to create, alter, and drop (delete) a table.

Creating Tables

A different system privilege is needed to create a table for the following two cases:

  • When the user wants to create a table in the user's own schema, the system privilege for the CREATE TABLE statement is required.

  • When the user wants to create a table in another user's schema, the system privilege for the CREATE ANY TABLE statement is required.

Use the CREATE TABLE statement to create a table.

The following is a table of components that are included when creating a table:

ComponentDescription
Table Name
  • Specifies the table's name. Required.

  • The maximum length of a table name is 128 characters.

  • The name should be unique within the user's schema, and should be different from the names of all other schema objects.

  • Different users can have a table with the same name.

  • Can have the same name as any Index, Trigger, or Large Object Type.

  • Can have the same name as any public synonym. If a public synonym's name is used in a SQL statement, it becomes the name of a table which is owned by the user.

Table Column Structure
  • Specifies the attributes (column name, data type, default value, etc.) of the data to be filled in a table. Required.

  • A table must have at least one column, and each column must have a declared data type.

  • A table can have a maximum of 1,000 columns.

  • The maximum length of a column name is 30 characters.

  • A column's default value and constraints can optionally be declared.

Integrity Constraints
  • Specifies integrity constraints to avoid unwanted data insertion, modification or deletion within a column. Optional. For detailed information, refer to “4.3. Constraints”.

  • Examples of constraints are: PRIMARY KEY, UNIQUE KEY, referential integrity, NOT NULL, CHECK.

  • A constraint's name must be unique within the table.

  • One or more constraints can be specified for a column or a table.

  • If two more compound columns are used, the constraints must be specified separately.

  • Using the ALTER TABLE statement, constraints can be added or their status can be modified or deleted.

Tablespace
  • Specifies a tablespace where a table is stored. Optional.

  • If a tablespace is not specified, the user's default tablespace is used.

  • Because proper arrangement of tables greatly affects database performance, it is recommended that the user who owns tables specify a separate tablespace to store the tables.

Disk Block Parameter

Specifies the size of free space in preparation for table updates of each disk block. For detailed information, refer to “4.4. Disk Blocks”.

  • PCTFREE

  • INITRANS

Partition
  • Defines partitions.

Creating a table is shown below:


As shown in the example above, a table named EMP has five columns (EMPNO, ENAME, ADDR, SALARY, DEPNO), and has four constraints declared. The table is stored in a tablespace named my_space and the disk block parameters (PCTFREE, INITRANS) are all specified.

Altering Tables

A different system privilege is needed for the following two cases:

  • When the user alters a table in the user's own schema, the system privilege for the ALTER TABLE statement is required.

  • When the user alters a table in another user's schema, the system privilege for the ALTER ANY TABLE statement is required.

Use the ALTER TABLE statement to alter a table.

The following is a table of components that are included when altering a table:

ComponentDescription
Table Name
  • Changes the table's name.

  • The maximum length of a table name is 128 characters.

Column Definition Change
  • Changes the attributes (default value, constraints, etc.) which are defined for a column.

  • Use the MODIFY clause to change a column's default value and constraints. For detailed information, refer to [Example 4.2].

Column Name
  • Changes the name of a column and attributes of the defined column.

  • The maximum length of a column name is 30 characters. Use the RENAME COLUMN clause to change it. For detailed information, refer to [Example 4.3].

Disk Block Parameters
  • Changes a parameter's names and values. For detailed information, refer to [Example 4.4].

Constraints
  • Changes the names of constraints.

  • Adds or deletes constraints.

  • Changes the status of constraints.

Tablespace
  • Changing the tablespace that is allocated to a table is not allowed.

Partition
  • Adds or deletes a partition.

Changing the attributes of the EMP table created in [Example 4.1]: is shown below:

Dropping Tables

A different system privilege is needed for the following two cases:

  • When the user removes a table that belongs to the user's own schema, the system privilege for the DROP TABLE statement is required.

  • When the user removes a table that belongs to another user's schema, the system privilege for the DROP ANY TABLE statement is required.

Use the DROP TABLE command to remove a table.

Dropping a table is shown below:


In order to remove a table owned by another user, the user's name must be specified when executing the DROP TABLE command.

For example:

DROP TABLE John.EMP;

If the primary key of a table to remove is defined in another table as a referential integrity constraint, the referred table cannot be removed. To remove it, either remove the referring table or remove the referential integrity constraint defined in the referring table. To remove referential integrity constraints defined in a referring table, use the CASCADE CONSTRAINTS clause with the DROP TABLE command.

For example:

DROP TABLE EMP CASCADE CONSTRAINTS; 

To efficiently manage tables, different methods need to be used in different cases.

Tibero provides a function to compress duplicate column values to save storage space. Duplicate column values that exist in a block are saved in a single space called a symbol table. Because the symbol table is saved in the corresponding block, refer to the block to find the original value of a compressed column.

The same DML is supported for a compressed table as for a general table. That is, DML such as insert, update, and delete is supported. Because a row that is added with a non-bulk insert statement cannot be compressed, inserting a row in a compressed table has the same performance as inserting a row in an uncompressed table. Deleting a row from a compressed table has the same performance as deleting a row from an uncompressed table. However, updating a row in a compressed table may have slower performance than updating a row in an uncompressed table as compression is sometimes needed.

Compressing a table saves disk space, but requires more CPU for compression. If there is a lot of DML in a table, the compression rate is lowered. Therefore, compressing a table is more efficient in an OLAP environment than for an OLTP environment.

Compression Targets

Tables, partitions, and sub-partitions can be compressed. It is possible to specify whether to compress each partition and each sub-partition. That is, a table can be created by compressing one partition and not compressing another partition.



Checking the Compression Status of Tables

The compression status of a table can be checked by querying the static views *_TABLES and *__TBL_PARTITIONS. When the value of the compression column is 'YES', additional DML is compressed.


Table Compression Methods

Data in a table is compressed using the following methods:

  • Direct Path Loader

  • Direct Path Insert (Bulk INSERT performed through parallel INSERT or append hint)

  • CREATE TABLE AS SELECT statement

Because an EXCLUSIVE LOCK is applied to a table for bulk inserts, other DML cannot be performed. After a table is compressed, row values resulting from non-bulk insert and update statements are not compressed.

Compressing or Decompressing Tables

The compression status of a table can be changed using the ALTER TABLE MOVE statement. That is, a compressed table can be decompressed and an uncompressed table can be compressed. While the ALTER TABLE MOVE statement is being performed, an EXCLUSIVE LOCK is applied to a table, so another DML cannot be performed. If a table is partitioned, MOVE needs to be performed for each partition, not for the table itself.

To change the compression status while DML is being performed, use the Exchange DDL feature to compress or decompress an online table.


Performing compression for additional DML can be specified using the ALTER TABLE statement. If a compressed table is specified to not compress using an ALTER TABLE statement, compression is not performed for Direct Path Loader, parallel Insert, and other statements. However, the status of existing data does not change.


Constraints for Compressing Tables

If a table has been compressed already, a column with a default value cannot be added or deleted and an add column ddl cannot be executed on a compressed table with a long type column.

An INDEX ORGANIZED TABLE saves data using an indexed B-TREE structure. While data is usually randomly saved in blocks in rows for tables, rows are sorted based on a primary key in the same format as indexes and are saved in index leaf blocks for an INDEX ORGANIZED TABLE.

For rows that are too large or to improve space efficiency, columns from a specific position can be saved in another data area. The area is called the overflow data area.

An INDEX ORGANIZED TABLE has the following advantages:

  • Randomly accessing a primary key is faster as rows are sorted based on the primary key.

    Even if a table has indexes, a primary needs to be found in the indexes and then the corresponding row needs to be found with the row ID. However, for an INDEX ORGANIZED TABLE, a row can be found directly in the index area, so additional searching is unnecessary.

  • Storage usage is reduced as the primary key is not duplicated in the index and data areas.

However, indexes need to be restructured whenever modification occurs. Therefore, an INDEX ORGANIZED TABLE is not appropriate if DML occurs often.

Because all rows are sorted based on a primary key in an INDEX ORGANIZED TABLE, a SECONDARY INDEX can be created to generate indexes with another key.

Creating INDEX ORGANIZED TABLES

An INDEX ORGANIZED TABLE can be created with the CREATE TABLE statement and the ORGANIZATION INDEX clause. A primary key must be declared.

The following parameters can be used:

ParameterDescription
OVERFLOWColumns that exceed the maximum row size of an INDEX ORGANIZED TABLE or columns after the column specified with INCLUDING are saved in the overflow data area. A user-defined tablespace can also be used here.
INCLUDINGColumns after the column specified with INCLUDING are saved in the overflow data area. This parameter can specify the last column of a primary key or a column which is not a primary key.
PCTTHRESHOLD

Based on the block size, this is the maximum ratio of rows that can be included in an index area of an INDEX ORGANIZED TABLE.

If INCLUDING is not specified, columns exceeding the range of PCTTHRESHOLD are saved in the overflow data area.

Even if INCLUDING is specified, columns for which the total size of the previous columns exceeds the range of PCTTHRESHOLD are saved in the overflow data area.


Constraints for Creating INDEX ORGANIZED TABLEs

Note the following constraints when creating an INDEX ORGANIZED TABLE:

  • It cannot include LOB or LONG data.

  • The maximum number of columns is 1,000.

  • The maximum number of columns in an index area is 255. If the number of columns in the area exceeds this value or columns cannot be saved in the index area, specify OVERFLOW.

  • The value of PCTTHRESHOLD is between 1 and 50. However, the maximum size of rows that can be saved in an index area is less than 50% of a block due to structural issues.

  • All columns should be less than PCTTHRESHOLD.

Deleting INDEX ORGANIZED TABLEs

An INDEX ORGANIZED TABLE can be deleted with the DROP TABLE statement.


Constraints are a method to prevent unwanted data from being inserted, modified, or removed from a column.

This section explains how to declare, change, or remove constraints.

The following is a list of constraints declared when creating a table:

ConstraintDescription
Primary Key

Characteristics of the Unique Key and NOT NULL constraints.

A column set as the primary key cannot have a NULL value.

Unique KeyRows in the table cannot have the same value for the column, unless the value is NULL.
Referential IntegrityRefers to the primary key or a unique key of a table.
NOT NULL

Rows in the table cannot have a NULL value for this column.

Table-level constraints cannot be set.

CHECK

Constraint where a certain condition must be met before a value can be inserted or modified.

A column can have multiple constraints.

Declaring Constraints

Constraints can be declared in various ways that determine the number of columns and if the constraint has a name. Regardless of how a constraint is declared, its effect is the same for the table.

A constraint name must be specified when creating a constraint. The name is used when modifying the declaration or status of the constraint.

In order to set a name for a constraint, CONSTRAINT NAME must be defined when declaring a CONSTRAINT (a reserved word).

Specifying a name for the constraint declared in [Example 4.1] is shown below:


Different syntax is used for the following cases:

Changing Constraints

In Tibero, constraints that have been declared can be changed using the ALTER TABLE command. However, not all constraints can be modified.

Removing Constraints

To remove a constraint, use the DROP clause with the ALTER TABLE command.

With the exception of the primary key and unique keys, all constraints must have a name. If a name is not specified when declaring a constraint, a name is automatically created by Tibero.

The list of names can be found with the USER_CONSTRAINTS view. Then, the constraints of the names can be changed or modified.

Removing constraints is shown below:


There are two constraint states.

  • ENABLE

    The declared constraint is applied to all rows which are inserted or updated in the table.

    ENABLE has two additional options:

    OptionDescription
    VALIDATE

    When the constraint state is ENABLE, all rows which are already in the table should satisfy the constraint whenever a row is inserted or updated.

    Checking all rows at the same time can improve database performance.

    NOVALIDATE

    All rows in the table are not checked to determine if they satisfy the constraint. The rows should either be checked later or they should already satisfy the constraint.

    This helps improve database performance because the rows in the table do not need to be checked frequently.

    However, PRIMARY KEY and UNIQUE KEY constraints, due to the characteristics of their indexes, are always checked as if they were using VALIDATE even when the NOVALIDATE option is used.

  • DISABLE

    The declared constraint is not applied to rows. When inserting or updating a large number of rows at the same time, it is helpful to use DISABLE until the work is finished, and then use ENABLE. This helps improve database performance.

    Users can insert or update many rows at once using tbLoader, the tbImport utility, or other batch programs. Because the rows in the table do not need to be checked to determine if they satisfy the constraint, this helps improve database performance.

    DISABLE can be used with the following additional options.

    OptionDescription
    VALIDATEDrops the index on the constraint and does not allow any modification of the constrained column.
    NOVALIDATESame as not specifying any option.

Modifying Constraint States

Use the ALTER TABLE command to modify a constraint state.

Modifying a constraint state is shown below:

A disk block is the minimum physical unit used to store data, and it has a fixed size. Tibero provides parameters for each schema object in order to use disk blocks efficiently. Depending on the characteristics of the schema objects, the parameters can be specified to improve database performance and to effectively use the storage area.

PCTFREE is a parameter which sets the percentage of free space for updates of schema objects stored in a disk block.

The parameter can have any integer value between 1 and 99. New rows can be inserted until the free space of the disk block falls below the value of the PCTFREE parameter. When this occurs, new rows are no longer inserted and the remaining free space is used to update existing rows.

If the empty space within a disk block is smaller than the value of the PCTFREE parameter, the disk block's objects are deleted. Even if the empty space becomes larger than the PCTFREE value, new objects will not be inserted immediately. They are inserted to the disk block when there is sufficient space.

If the size of an object stored in the current disk block is likely to increase, the PCTFREE parameter should be specified as sufficiently large. When the parameter is large, the number of objects that can be stored in a disk block is less than when the value of PCTFREE is small, so more disk blocks are required to store the same number of objects. This can result in a drop in database performance.

However, specifying the PCTFREE parameter to a large value can improve performance because it becomes less likely that an object will be stored in several disk blocks. Therefore, the PCTFREE parameter must be appropriately specified in order to efficiently operate the database. In particular, when there will be frequent updates of objects, it is recommended to set the PCTFREE parameter to a large value. The default value for the parameter is 10%.

If update operations within transactions that access the disk block do not increase the size of any object or the transactions only consists of read operations, the PCTFREE parameter can be set very small (for example, to 5).

One disk block can be accessed by many transactions at the same time, and each row included in the disk block has information about the last transaction that created, updated, or removed it. This transaction data is gathered in a space within the disk block. This space is called the Transaction Entry List.

The transaction entry list is included in the header of the disk block, and its initial size when the disk block is created becomes the value set in the INITRANS parameter. After the transaction is committed, the transaction entries within the list can be reused by other transactions.

For example, if more transaction entries are needed, increase the size of the total list (the number of transaction entries) by one. The maximum number of transaction entries is determined by the disk block size. In other words, as the disk block size increases, the number of transaction entries increases as well. However, the number should not exceed 255.

If the transaction entry number reaches the maximum number, the list's size is no longer increased and the transactions stops executing and enters a wait state. The pending transactions continue to execute and the existing transaction entries are again available for use after other transactions complete.

Because the task of increasing the transaction entry list requires processing, it is recommended not to do it frequently. Therefore, if a disk block is likely to be accessed by many transactions, it is important to specify the INITRANS parameter as sufficiently large.

The default value for the INITRANS parameter is 2.

An index is a data structure used to more efficiently search data in tables. Because the index is a different schema object, users can create, change, delete, and save it independently.

The index types are as follows:

Tibero allows users to create and delete indexes for any column except those columns that are set with Primary Key, Unique Key, or Foreign Key constraints. If an index's name is not specified by the user, the name will be the same as the name of a default constraint.

Creating Indexes

A different system privilege is needed to create an index for the following two cases:

  • When creating an index in a table that belongs to the current user's own schema, the system privilege for the CREATE INDEX statement is required.

  • When creating an index in a table that belongs to another user's schema, the system privilege for the CREATE ANY INDEX statement is required.

To create an index, use the CREATE INDEX statement.

The following is a list of parameters that can be specified when creating an index:

ComponentDescription
Index Name

Name of the index to create.

This parameter is required.

Table Name

Name of the table to which the column belongs.

This parameter is required.

Unique

Unique index.

This parameter is optional.

Column Definition, Justification

Defines the column and specifies the justification (direction).

This parameter is optional.

Tablespace

Tablespace to store the index in.

This parameter is optional.

Disk Block Parameter

INITRANS parameter.

This parameter is optional.

Partition Index

Partitioned index.

This parameter is optional.

Creating an index named 'EMP_FK' in the column DEPTNO of the table EMP is shown below:


Deleting Indexes

To delete indexes, use the DROP INDEX command.

Because indexes are independent schema objects, like tables, deleting indexes does not affect table data. However, if an index is deleted, query speeds might become slower.

If an index is not necessary, deleting it can improve database performance.

Deleting the index 'EMP_FK' created in [Example 4.22] is shown below:


Tibero provides a B-tree as the basic structure of an index, which is used for single key searches, range key searches, and composite key searches.

Index Search Types

There are three ways to search a table's rows using an index:

Guidelines for Efficient Index Management

Below are guidelines for efficiently managing indexes:

  • Create indexes only for the tables and columns that need them.

    While the index enables more efficient data searching, it also affects data storage capacity and database performance. Extra space is needed to save the index, and whenever a row in the table is inserted, updated, or deleted, the index should also be updated.

  • Create an index for the column used in a JOIN operation together with a primary key column.

    Creating an index causes the system to save the sorting result of the column. Tibero performs JOIN operations better for sorted columns. This is especially effective when the size of a table used in a JOIN is large.

  • Create indexes for columns that are likely to be included in the WHERE clause and that have search results that contain less than 10% of the entire table.

    For single key search, if no index is defined, the entire table is accessed. However, if there are indexes defined, only a single index is accessed to search data, which improves system performance.

  • Consider the sequence of column values when creating composite key indexes.

    In general, the more frequently searched columns are placed first.

    For example, when creating a composite key index for the columns C1 and C2, there are two ways to create it: (C1, C2) and (C2, C1).

    If C1 is searched more often than C2, it is better to create a composite key index using (C1, C2), because keys that have the same C1 value are together, the number of disk blocks that need to be accessed to perform the search is small.

    If the index is created as (C1, C2), it is rare to search using the C2 column.

  • Remove indexes that are unnecessary or infrequently used.

  • Do not create too many indexes for one table.

  • Store indexes and tables on different disks for better system performance.

A view is a virtual table defined by assigning a name to a query expressed as a SELECT statement. It is used in the same way as a table within any SQL statement. However, the view is not a schema object that contains actual data, and is instead defined by another schema object.

This section explains how to create, change, and delete views.

Creating Views

A different system privilege is needed to create a view for the following two cases:

  • When creating a view that belongs to the current user's own schema, the system privilege for the CREATE VIEW statement is required.

  • When creating a view that belongs to another user's schema, the system privilege for the CREATE ANY VIEW statement is required.

To create a view, use the CREATE VIEW statement. Access privilege to all base objects is required regardless of the number of base tables.

Creating a view is shown below:


Users can perform only the operations for a view that they can perform for all the base tables. For example, if the user who defined the EMP_DEPT view can perform INSERT and DROP operations for the EMP table and can perform INSERT and UPDATE operations for the DEPT table, the user can perform the operation they have in common, INSERT, for the EMP_DEPT view.

Other users can be granted access privileges views, just like tables. However, the access privileges should be given with GRANT OPTION or ADMIT OPTION for the base objects that define the view.

If a user is granted access privilege for a view, the user can access the base object through the view it defines even though the user does not have direct access privilege. The view definer should have privileges for the operations to be performed.

Creating a view is shown below:

CREATE VIEW V_PROD AS
       SELECT PROD_ID, PROD_NAME,PROD_COST
       FROM PRODUCT
       WHERE PROD_ID= 100001;

Changing Views

To reuse a view that cannot be used due to a change of the view or its base object, use the CREATE OR REPLACE VIEW statement. The privilege to create and remove the view is required.

Changing a view is shown below:


After the SQL statement above is executed, all privileges for VIEW MANAGER that were given to users remain unchanged. If the DROP VIEW and CREATE VIEW commands were instead executed, the privileges for VIEW MANAGER would have been removed.

Deleting Views

A different system privilege is needed to delete a view for the following two cases:

  • When deleting a view that belongs the current user's own schema, the system privilege for the DROP VIEW statement is required.

  • When deleting a view that belongs to another user's schema, the system privilege for the DROP ANY VIEW statement is required.

To delete a view, use the DROP VIEW command.

Deleting a view is shown below:


A sequence is a series of unique numbers which are used to sequentially number new data.

This section explains how to create, change, and delete sequences.

Sequences are used by transactions to create unique numbers.

If sequences are not used and unique numbers are needed, a table should be created to record the most recently used number, which can then be used and increased by each transaction. However, this method can significantly decrease database performance due to data conflicts from locks between all transactions generating unique numbers.

Creating Sequences

A different system privilege is needed to create a sequence for the following two cases:

  • When creating a sequence in the current user's own schema, the system privilege for the CREATE SEQUENCE statement is required.

  • When creating a sequence in another user's schema, the system privilege for the CREATE ANY SEQUENCE statement is required.

To create a sequence, use the CREATE SEQUENCE statement.

The following is a list of parameters used when creating a sequence:

ComponentDescription
Sequence name

Sequence's name.

This parameter is required.

MINVALUEMinimum value of a sequence.
MAXVALUEMaximum value of a sequence.
INCREMENT BYAmount to increment or decrement the sequence values.
CACHECaches values in memory to improve database performance.
START WITH

Starting value for a sequence.

By default, increasing sequences use MINVALUE and decreasing sequences use MAXVALUE

NOCYCLE

No more values can be created when MAXVALUE or MINVALUE is reached unless the ALTER SEQUENCE command is used.

(Default value)

CYCLEAutomatically cycles the sequence to MINVALUE or MAXVALUE when the sequence value reaches MAXVALUE or MINVALUE.
ORDEROption to create sequence numbers according to the order in which the requests are made. This is only useful for a TAC (sequence is alwasy preserved in a single node setting).

A sequence internally caches values into memory to improve database performance. The MAX_SEQ_BUFFER parameter stores the size of the cache, and its default value is 20.

When the system terminates normally, the values which exist in the cache but have not yet been used will be stored on disk and used again the next time Tibero RDBMS starts. However, if the system terminates abnormally, all values in the cache are treated as if they were used, so the sequence value can skip by as many numbers as the maximum size of the cache.

To prevent this, the sequence can be declared with NOCACHE. This causes database performance to drop because the disk is accessed every time the sequence is used. Except in special circumstances, using NOCACHE is not recommended.

Creating a sequence is shown below:


Using sequence values is shown below:

CREATE TABLE EMP_ID (ID NUMBER, NAME VARCHAR(30));

INSERT INTO EMP_ID VALUES(NEW_ID.NEXTVAL, 'Peter'); 

After being used, a sequence value cannot be reused even if the relevant transaction is rolled back or the system terminates abnormally.

Changing Sequences

A different system privilege is needed to change a sequence for the following two cases:

  • When changing a sequence that belongs to the current user's own schema, the system privilege for the ALTER SEQUENCE statement is required.

  • When changing a sequence that belongs to another user's schema, the system privilege for the ALTER ANY SEQUENCE statement is required.

To change a sequence, use the ALTER SEQUENCE statement.

Changing a sequence is shown below:


SQL> CREATE SEQUENCE S1 START WITH 10 INCREMENT BY 1;
created

SQL> ALTER SEQUENCE S1 INCREMENT BY 5;
altered

SQL> SELECT S1.NEXTVAL FROM DUAL;
   NEXTVAL
----------
        14

1 selected

Deleting Sequences

A different system privilege is needed to delete a sequence in the following two cases:

  • When deleting a sequence that belongs to the current user's own schema, the system privilege for the DROP SEQUENCE statement is required.

  • When deleting a sequence that belongs to another user's schema, the system privilege for the DROP ANY SEQUENCE statement is required.

To delete a sequence, use the DROP SEQUENCE statement.

Deleting a sequence is shown below:


A synonym is an alias for a schema object. However, the synonym is not a schema object that contains actual data and is instead defined by another schema object.

This section describes how to create and delete public synonyms.

Creating Public Synonyms

A synonym can be specified to be accessible by all users. This type of synonym is called a public synonym. A public synonym is owned by a specific user defined as PUBLIC in Tibero.

To create a public synonym, use the CREATE PUBLIC SYNONYM statement.

The following is a list of parameters used when creating a public synonym:

ComponentDescription
Public synonym nameName of the public synonym.
Table nameName of the table to which the public synonym refers.

Creating a public synonym is shown below:


Synonyms provide convenience and transparency for object reference methods. For example, if a user other than U1 tries to access the EMP table, which is owned by U1, the user should specify U1.EMP each time. However, if a synonym T1 is defined, the user can specify just T1.

The following two statements return the same result:

SELECT EMPNO, ENAME, ADDR FROM PUB_T1;

SELECT EMPNO, ENAME, ADDR FROM U1.EMP;

If an application that accesses a table uses synonyms to access other tables, the user can just change the synonyms without having to change the table names accessed by the application.

Deleting Public Synonyms

To delete a public synonym, use the DROP PUBLIC SYNONYM statement. The DROP PUBLIC SYNONYM system privilege is required to use the statement.

Deleting a public synonym is shown below:


Triggers are Persistent Stored Module (PSM) procedures which are predefined to be executed automatically when inserting, updating, or deleting a row. They are used to express logical conditions for a database which are difficult to express with constraints. For example, triggers can be used to provide different limits for the range of values for each user.

Partitions are an option that improves the efficiency and manageability of a large volume service database.

If a table's volume becomes large and many transactions try to simultaneously access the table, database performance becomes poor due to physical constraints such as frequent I/O and database locks.

To solve the problem, one logical table can be divided into several physical spaces, called partitions.

Partitions can be created in different tablespaces to reduce physical constraints such as I/O.

If all data is stored in a single table, all transactions will occur in a single area. If this happens, each transaction is more likely to wait for other transactions to finish, which lowers database performance. However, if the table is divided into several partitions, each transaction only needs to access the relevant partition, which reduces the wait time.

For some DML statements that access only data from a certain partition, it is possible to search only 1/N (where N is the number of partitions) of the information rather than searching the entire table.

Partitions have the following three types:

PartitionDescription
RANGERange partition by specifying a RANGE included in the partition.
HASHHash partition using the HASH function.
LISTList partition by directly specifying the data to be included in each partition.

To create partitions, define the partition information when using the CREATE TABLE statement. No privilege other than the table creation privilege is needed.

Creating a partitioned table is shown below:


As shown above, a table can be divided into partitions by specifying ranges. Data is inserted to specific partitions based on the ranges. Based on the information, users can determine the partition where data is located.. A maximum of 10,000 partitions can be created.

Another benefit of partitioning is convenient management. When a table is partitioned according to each year, unnecessary partitions which store information more than 10 years old can be easily removed. If a partition for the next year is needed, a new partition can be added.

Adding or deleting a partition using the partition option in the ALTER TABLE statement is shown below:

ALTER TABLE PARTITIONED_TABLE1 ADD PARTITION PART4
        VALUES LESS THAN (70, 80);

ALTER TABLE PARTITIONED_TABLE1 DROP PARTITION PART1; 

Notes for Defining a Partition

Some notes for defining a partition:

  • The order the ranges are listed in is important.

    For example, the following example will return an error:

    CREATE TABLE PARTITIONED_TABLE2 (C1 NUMBER, C2 CLOB, C3 NUMBER)
                 PARTITION BY RANGE (C1, C3)
                 (
                    PARTITION PART1 VALUES LESS THAN (50, 20),
                    PARTITION PART2 VALUES LESS THAN (30, 10),
                    PARTITION DEF_PART VALUES LESS THAN (MAXVALUE, MAXVALUE)
                 );

    PART1 is declared before PART2, but according to the ranges, PART1 includes PART2. The clause VALUES LESS THAN, when used to define a range, means 'A partition which is not included in a previous partition and has data whose values are less than -'. Therefore, note the order of ranges for partitions.

  • New partitions created with ALTER TABLE should have a higher range than that of the previous partition.

    A larger key of a preceding partition indicates a higher range. If the key of the preceding partition is specified as MAXVALUE, a new partition cannot be created because it cannot have a larger key.

    An error will occur for the following example:

    CREATE TABLE PARTITIONED_TABLE3 (C1 NUMBER, C2 CLOB, C3 NUMBER)
                 PARTITION BY RANGE (C1, C3)
                 (
                    PARTITION PART1 VALUES LESS THAN (50, 20),
                    PARTITION PART2 VALUES LESS THAN (60, 70)
                 );
    
    ALTER TABLE PARTITIONED_TABLE3 ADD PARTITION PART3
                VALUES LESS THAN (80, 40);
    
    ALTER TABLE PARTITIONED_TABLE3 ADD PARTITION PART3
                VALUES LESS THAN (70, 100);
  • Currently, Tibero does not support partition addition to a HASH partition table.

Composite partitioning is a table partitioning method. To create composite partitions, partitions are generated with a key and then each partition is itself partitioned with a key.

In the following example, RANGE partitioning is used with the sold_date column and then HASH partitioning is used with the product_id for each partition.

CREATE TABLE years_sales
(
    product_id          NUMBER,
    product_name        VARCHAR(20),
    price               NUMBER,
    sold_date           DATE
)
PARTITION BY RANGE (sold_date)
  SUBPARTITION BY HASH (product_id)
  (
    PARTITION jan_sales VALUES LESS THAN
                         (TO_DATE('31-01-2006', 'DD-MM-YYYY')),
    PARTITION feb_sales VALUES LESS THAN
                         (TO_DATE('28-02-2006', 'DD-MM-YYYY')),
    PARTITION mar_sales VALUES LESS THAN
                         (TO_DATE('31-03-2006', 'DD-MM-YYYY')),
    PARTITION apr_sales VALUES LESS THAN
                         (TO_DATE('30-04-2006', 'DD-MM-YYYY')),
    PARTITION may_sales VALUES LESS THAN
                         (TO_DATE('31-05-2006', 'DD-MM-YYYY')),
    PARTITION jun_sales VALUES LESS THAN
                         (TO_DATE('30-06-2006', 'DD-MM-YYYY')),
    PARTITION jul_sales VALUES LESS THAN
                         (TO_DATE('31-07-2006', 'DD-MM-YYYY')),
    PARTITION aug_sales VALUES LESS THAN
                         (TO_DATE('31-08-2006', 'DD-MM-YYYY')),
    PARTITION sep_sales VALUES LESS THAN
                         (TO_DATE('30-09-2006', 'DD-MM-YYYY')),
    PARTITION oct_sales VALUES LESS THAN
                         (TO_DATE('31-10-2006', 'DD-MM-YYYY'))
    SUBPARTITIONS 2,
    PARTITION nov_sales VALUES LESS THAN
                         (TO_DATE('30-11-2006', 'DD-MM-YYYY'))
    SUBPARTITIONS 4,
    PARTITION dec_sales VALUES LESS THAN
                         (TO_DATE('31-12-2006', 'DD-MM-YYYY'))
    (SUBPARTITION dec_1, SUBPARTITION dec_2,
     SUBPARTITION dec_3, SUBPARTITION dec_4)
  );

When a composite partition is used for a large table where searches often use two columns (in the above example, sold_date and product_id), performance can improve because the amount of data to be searched is reduced.

Note

For information about the syntax for composite partitions, refer to Tibero SQL Reference Guide.

Like tables, indexes can also be divided into partitions. This can also improve database performance.

There are two methods to partition an index.

Local Partitions

This method divides partitions with the keys used for the table partitions.

Declare LOCAL without entering any information for each partition. Names will be automatically created and other information will be specified as default values.

Each local partition index is associated with one table partition. In other words, one partition of a local partition index references the rows of one partition of a table.

Creating indexes as local partitions is shown below:


Global Partitions

This method defines partitions for indexes with regard for the tables. Global partition indexes can be created whether or not a table is divided into partitions. Any global partition index can point to any row of the table.

Creating a global partition index is shown below: