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:

Use the CREATE TABLE statement to create a table.

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

Creating a table is shown below:

CREATE TABLE DEPT
  (
      DEPTNO    NUMBER PRIMARY KEY,
      DEPTNAME  VARCHAR(20),
      PDEPTNO   NUMBER
  )
  TABLESPACE my_space
  PCTFREE 5 INITRANS 3;

CREATE TABLE EMP
  (
      EMPNO     NUMBER PRIMARY KEY,
      ENAME     VARCHAR(16) NOT NULL,
      ADDR      VARCHAR(24),
      SALARY    NUMBER,
      DEPTNO    NUMBER,
      CHECK (SALARY >= 10000),
      FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO)
  )
  TABLESPACE my_space
  PCTFREE 5 INITRANS 3;  

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:

Use the ALTER TABLE statement to alter a table.

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

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:

Use the DROP TABLE command to remove a table.

Dropping a table is shown below:

DROP TABLE EMP;

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 the static views shown below to support viewing table data, which can be used by both the DBA and general users.

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.

CREATE TABLE EMP (
       EMPNO DECIMAL(4),
       ENAME VARCHAR(10),
       JOB VARCHAR(9),
       MGR DECIMAL(4),
       HIREDATE VARCHAR(14),
       SAL NUMBER(7,2),
       COMM NUMBER(7,2),
       DEPTNO NUMBER(2))
       COMPRESS; 
 

CREATE TABLE EMP (
       EMPNO DECIMAL(4),
       ENAME VARCHAR(10),
       JOB VARCHAR(9),
       MGR DECIMAL(4),
       HIREDATE VARCHAR(14),
       SAL NUMBER(7,2),
       COMM NUMBER(7,2),
       DEPTNO NUMBER(2))
       COMPRESS
   PARTITION BY RANGE(EMPNO)
   ( PARTITION EMP_PART1 VALUES LESS THAN(500),
     PARTITION EMP_PART2 VALUES LESS THAN(1000) NOCOMPRESS,
     PARTITION EMP_PART3 VALUES LESS THAN(1500),
     PARTITION EMP_PART4 VALUES LESS THAN(2000) NOCOMPRESS,
     PARTITION EMP_PART5 VALUES LESS THAN(MAXVALUE)); 
 

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.

select table_name, compression from user_tables;

TABLE_NAME      COMPRESSION
--------------- ------- 
EMP             YES

Table Compression Methods

Data in a table is compressed using the following methods:

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.

ALTER TABLE TBL_COMP MOVE COMPRESS;
ALTER TABLE EMP MOVE PARTITION EMP_PART1 NOCOMPRESS;  

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.

ALTER TABLE EMP COMPRESS;  

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:

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:

CREATE TABLE TBL_IOT 
  (
      COL1 NUMBER,
      COL2 VARCHAR2(20),
      COL3 VARCHAR2(10),
      COL4 VARCHAR2(10),
      PRIMARY KEY (COL1, COL2)
  )
  ORGANIZATION INDEX
  PCTTHRESHOLD 40
  OVERFLOW;  

Constraints for Creating INDEX ORGANIZED TABLEs

Note the following constraints when creating an INDEX ORGANIZED TABLE:

Deleting INDEX ORGANIZED TABLEs

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

DROP TABLE TBL_IOT;  

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

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

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:

CREATE TABLE DEPT
  (
      DEPTNO    NUMBER PRIMARY KEY,
      DEPTNAME  VARCHAR(20),
      PDEPTNO   NUMBER
  )
  TABLESPACE my_space
  PCTFREE 5 INITRANS 3;

CREATE TABLE EMP
  (
      EMPNO       NUMBER        PRIMARY KEY,
      ENAME       VARCHAR(16)   NOT NULL,
      ADDR        VARCHAR(24),
      SALARY      NUMBER,
      DEPTNO      NUMBER,
      CONSTRAINT  SALARY_MIN CHECK (SALARY >= 10000),
      CONSTRAINT  DEPTNO_REF FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO)
  )
  TABLESPACE my_space
  PCTFREE 5 INITRANS 3;

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:

ALTER TABLE EMP
        DROP PRIMARY KEY;
  ... Removes a constraint set with the primary key. ...

ALTER TABLE EMP
        DROP CONSTRAINT SALARY_MAX;
  ... Removes a constraint whose name is SALARY_MAX. ...

There are two constraint states.

Modifying Constraint States

Use the ALTER TABLE command to modify a constraint state.

Modifying a constraint state is shown below:

Tibero provides the static views below to support viewing constraint data, which can be used by both DBAs and general users.

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.

The PCTFREE and INITRANS parameters can be specified for each schema object. These parameters can be specified when the schema object is created or they can be changed later.

Specifying the parameter values when creating the EMP table is shown below:

CREATE TABLE EMP (
                   ENAME    VARCHAR(16) NOT NULL,
                   ADDR     VARCHAR(24),
                   SALARY   INT,
                   DEPTNO   INT
                 )
                 PCTFREE  5
                 INITRANS 5;

Even if a disk block parameter value is changed, it is not directly applied to the actual disk block. Each parameter is applied to a disk block as follows:

The parameters can be modified using the ALTER TABLE command:

ALTER TABLE EMP PCTFREE 10; 

When creating an index, only the INITRANS parameter can be specified as follows:

CREATE INDEX EMP_DEPTNO_IDX ON EMP (DEPTNO) INITRANS 5;

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:

To create an index, use the CREATE INDEX statement.

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

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

CREATE INDEX EMP_FK ON EMP (DEPTNO); 

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:

DROP INDEX EMP_FK;

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:

Tibero provides static views as shown below to support viewing index data, which can be used by both the DBA and general users.

Tibero The monitoring results can be checked using V$OBJECT_USAGE.

The following is an example of monitoring an index usage.

SQL> CREATE TABLE T (A NUMBER);
Table 'T' created.

SQL> CREATE INDEX I ON T(A);
Index 'I' created.

SQL> ALTER INDEX I MONITORING USAGE;
Index 'I' altered.

SQL> SELECT /*+ index(t i) */ * from t where a > 0;
0 rows selected.

SQL> SELECT USED FROM V$OBJECT_USAGE;
   USED
-------
      Y

1 row selected.

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:

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:

CREATE VIEW MANAGER AS
       SELECT * FROM EMP
       WHERE DEPTNO = 1;


CREATE VIEW EMP_DEPT AS
       SELECT E.EMPNO, E.ENAME, E.SALARY, D.DEPTNO, D.LOC
       FROM EMP E, DEPT D
       WHERE E.DEPTNO = D.DEPTNO;

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:

CREATE OR REPLACE VIEW MANAGER AS
       SELECT * FROM EMP
       WHERE DEPTNO = 2;

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:

To delete a view, use the DROP VIEW command.

Deleting a view is shown below:

DROP VIEW EMP_DEPT;

Tibero RDBMS provides the static views shown below to support viewing view data, which can be used by both the DBA and general users.

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:

To create a sequence, use the CREATE SEQUENCE statement.

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

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:

CREATE SEQUENCE NEW_ID
       MINVALUE 1000
       MAXVALUE 9999
       INCREMENT BY 10
       CACHE 100
       NOCYCLE;

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:

To change a sequence, use the ALTER SEQUENCE statement.

Changing a sequence is shown below:

ALTER SEQUENCE NEW_ID
      MAXVALUE 99999
      INCREMENT BY 1
      CACHE 200;

When a sequence is used in Tibero, the value to be retrieved next is stored, not the most recently used value. Therefore, the execution result from the ALTER SEQUENCE command might differ from that of other DBMSs.

In the following example, Tibero internally records "the value to be taken next time = 10" so the next result will be 10.

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
----------
        10

1 selected

Deleting Sequences

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

To delete a sequence, use the DROP SEQUENCE statement.

Deleting a sequence is shown below:

DROP SEQUENCE NEW_ID;

Tibero provides the static views shown below to support viewing sequence data, which can be used by both the DBA and general users.

This section describes how to create and delete synonyms.

Creating Synonyms

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

To create a synonym, use the CREATE SYNONYM statement.

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

Creating a synonym is shown below:

CREATE SYNONYM T1 FOR U1.EMP; 

Deleting Synonyms

To change a defined synonym, delete and recreate the synonym.

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

To delete a synonym, use the DROP SYNONYM statement.

Deleting a synonym is shown below:

DROP SYNONYM T1;

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:

Creating a public synonym is shown below:

CREATE PUBLIC SYNONYM PUB_T1 FOR U1.EMP;

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 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:

DROP PUBLIC SYNONYM PUB_T1;

Tibero provides the static views shown below to support viewing synonym data, which can be used by both the DBA and general users.

This section describes how to create and delete triggers.

Creating a Trigger

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

To create a trigger, use the CREATE TRIGGER command. The trigger can be executed either before or after INSERT, ALTER or DELETE operations.

Creating a trigger that executes immediately after a new row is inserted in the EMP table is shown below:

CREATE TRIGGER TRG1
        AFTER INSERT ON EMP
        FOR EACH ROW
        WHEN (TRUE)
        BEGIN
                --- PSM BLOCK ---
        END; 

The created trigger inherits the same privilege as the user who created it and operates with that privilege.

Deleting a Trigger

To delete a trigger, use the DROP TRIGGER statement.

Deleting a trigger is shown below:

DROP TRIGGER TRG1;  

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

Creating a partitioned table is shown below:

CREATE TABLE PARTITIONED_TABLE1 (C1 NUMBER, C2 CLOB, C3 NUMBER)
             PARTITION BY RANGE (C1, C3)
             (
                PARTITION PART1 VALUES LESS THAN (30, 40),
                PARTITION PART2 VALUES LESS THAN (50, 60),
                PARTITION PART3 VALUES LESS THAN (60, 70)
             ); 

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 PART3
        VALUES LESS THAN (70, 80);

ALTER TABLE PARTITIONED_TABLE1 DROP PARTITION PART1; 

Some notes for defining a partition:

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.

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:

CREATE TABLE PARTITIONED_TABLE1 (C1 NUMBER, C2 CLOB, C3 NUMBER)
             PARTITION BY RANGE (C1, C3)
             (
                PARTITION PART1 VALUES LESS THAN (30, 40),
                PARTITION PART2 VALUES LESS THAN (50, 60),
                PARTITION DEF_PART VALUES LESS THAN (MAXVALUE, MAXVALUE)
             );

CREATE INDEX PARTITIONED_INDEX1 ON PARTITIONED_TABLE1 (C1) LOCAL
             (
                PARTITION IPART1 INITRANS 3,
                PARTITION IPART2 PCTFREE 10,
                PARTITION IPART3
             );

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:

CREATE TABLE PARTITIONED_TABLE1 (C1 NUMBER, C2 CLOB, C3 NUMBER)
             PARTITION BY RANGE (C1, C3)
             (
                PARTITION PART1 VALUES LESS THAN (30, 40),
                PARTITION PART2 VALUES LESS THAN (50, 60),
                PARTITION DEF_PART VALUES LESS THAN (MAXVALUE, MAXVALUE)
             );

CREATE INDEX PARTITIONED_INDEX1 ON PARTITIONED_TABLE1 (C3, C1)
             GLOBAL PARTITION BY RANGE (C3)
             (
                PARTITION IPART1 VALUES LESS THAN (20) INITRANS 3,
                PARTITION IPART2 VALUES LESS THAN (70) PCTFREE 10,
                PARTITION IPART3 VALUES LESS THAN (MAXVALUE)
             );

Tibero provides the static views shown below to support viewing partition data, which can be used by both the DBA and general users.