A Tables

　　1 Creating Tables

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; 

2 Altering Tables

 Altering a Table - Column Attribute

ALTER TABLE EMP
       MODIFY (SALARY DEFAULT 5000 NOT NULL);

Altering a Table - Column Name

ALTER TABLE EMP RENAME COLUMN ADDR TO ADDRESS;

Altering a Table - Disk Block Parameter

ALTER TABLE EMP PCTFREE 10;

3 Dropping Tables

Dropping a Table

DROP TABLE EMP;

DROP TABLE John.EMP;
删除有相关约束的表：
DROP TABLE EMP CASCADE CONSTRAINTS;

4 Viewing Table Data

Static View	Description
DBA_TABLES	Information about all tables within Tibero.
USER_TABLES	Information about all tables that belong to the current user.
ALL_TABLES	Information about all tables that the current user can access.
DBA_TBL_COLUMNS	Information about all columns that belong to tables and views within Tibero.
USER_TBL_COLUMNS	Information about all columns that belong to the current user's tables and views.
ALL_TBL_COLUMNS	Information about all columns that belong to tables and views to which the current user has access.

5 压缩表（Compressing Tables）
Decimal 十进制、小数、十进制数
1 Creating a Compressed Table
CREATE TABLE EMP(
　　EMPNO DECIMAL(4),
　　ENAME VARCHAR(10),
　　JOB VARCHAR(9),
　　NGR DECAMAL(4),
　　HIREDATE VARCHAR(14),
　　SAL NUMBER(7,2),
　　COMM NUMBER(7,2),
　　DEPTNO NUMBER(2)
) COMPRESS;

2 Creating a Table with Commpressed Partitions(创建一个压缩分区表)

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)
　　);

查看压缩状态:

select table_name, compression from user_tables;

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

Compressing or Decompressing a Table

压缩

ALTER TABLE TBL_COMP MOVE COMPRESS;

解压缩

ALTER TABLE EMP MOVE PARTITION EMP_PART1 NOCOMPRESS;

 

 Changing the Compression of Additional DML

 

ALTER TABLE EMP COMPRESS;

6 Creating INDEX ORGANIZED TABLES

OVERFLOW(溢出)

Columns 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.

 

超过索引组织表的最大行大小的列或包含指定的列之后的列将保存在溢出数据区域。
这里也可以使用用户定义的表空间。

INCLUDING （包含、包括）

Columns 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.

根据块大小，这是可以包含在索引组织表的索引区域中的行的最大比例。

如果未指定包含，则将超过PCTTHRESHOLD范围的列保存在溢出数据区域。

即使指定了include，前一列的总大小超过PCTTHRESHOLD范围的列也会保存

在溢出数据区域。

Creating an INDEX ORGANIZED TABLE

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

Deleting an INDEX ORGANIZED TABLE

DROP TABLE TBL_IOT;