ORACLE Table

 表分类：堆表、分区表、IOT表、cluster表

堆表：无序性，存储放表（普通表）

修改表结构：使用"SET UNUSED"将一个或多个列改成不使用，select和desc均无法查询到这些列，操作不可恢复；
可以在user_unused_col_tabs查询到不使用列数量。
写法：alter table dept set unused (ABC);
然后使用如下脚本来删除不使用列
写法：alter table dept drop unused columns;

使用DROP删除表时：
1.表的数据，结构均被删除；
2.表的所有索引均被删除；
3.表的视图，同义词保留，但不再有效；
4.不可恢复

RENAME:更改表，视图，序列或同义词名称
写法：RENAME DEPT TO DEPT_1

COMMENT:向表或者列添加备注
写法：COMMENT ON TABLE dept IS 'ABCD';
查询效果：ALL_COL_COMMENTS;USER_COL_COMMENTS;ALL_TAB_COMMENTS;USER_TAB_COMMENTS

Partition 分区表：

物理上分割表，逻辑上仍是一个完整表

查看：select table_name,partition_name,tablespace_name from user_tab_partitions where table_name=分区表名;

性能上：

1. 直接prun，减少查询数据源，缩短时间

2. 可以实现wise join

管理上：

1. 维护成本降低，rebuild时间缩短；

2. 可以单独处理一个分区内数据；

什么时候使用分区表：

1、表的大小超过2GB。

2、表中包含历史数据，新的数据被增加都新的分区中。

创建：

分类方式：

1. range：时间，数字为标准；

CREATE TABLE sales_history
(
   sal_date   DATE NOT NULL,
   sal_id     NUMBER NOT NULL,
   sal_row    NUMBER (12) NOT NULL
)
PARTITION BY RANGE (sal_date)
   (PARTITION sal_11
       VALUES LESS THAN (TO_DATE ('2012-01-01', 'YYYY-MM-DD')) tablespace users,
    PARTITION sal_12
       VALUES LESS THAN (TO_DATE ('2013-01-01', 'YYYY-MM-DD')) tablespace users,
    PARTITION sal_13
       VALUES LESS THAN (TO_DATE ('2014-01-01', 'YYYY-MM-DD')) tablespace users,
    PARTITION sal_14
       VALUES LESS THAN (TO_DATE ('2015-01-01', 'YYYY-MM-DD')) tablespace users,
    PARTITION sal_15
       VALUES LESS THAN (TO_DATE ('2016-01-01', 'YYYY-MM-DD')) tablespace users,
    PARTITION sal_16
       VALUES LESS THAN (TO_DATE ('2017-01-01', 'YYYY-MM-DD')) tablespace users,
    PARTITION sal_other
       VALUES LESS THAN (maxvalue) tablespace users)
NOLOGGING;

2. list：文本，字符串为标准；

3. hash：没有明显分割，主要平均分配

4. range+hash

5. range+list

建立索引：

1. 本地分区索引local partition index

2. 全局分区索引global partition index：

create index in_date on sales_history (sal_date)
global PARTITION BY RANGE (sal_date)
   (PARTITION sal_11
       VALUES LESS THAN (TO_DATE ('2012-01-01', 'YYYY-MM-DD')),
    PARTITION sal_12
       VALUES LESS THAN (TO_DATE ('2013-01-01', 'YYYY-MM-DD')),
    PARTITION sal_13
       VALUES LESS THAN (TO_DATE ('2014-01-01', 'YYYY-MM-DD')),
    PARTITION sal_14
       VALUES LESS THAN (TO_DATE ('2015-01-01', 'YYYY-MM-DD')),
    PARTITION sal_15
       VALUES LESS THAN (TO_DATE ('2016-01-01', 'YYYY-MM-DD')),
    PARTITION sal_16
       VALUES LESS THAN (TO_DATE ('2017-01-01', 'YYYY-MM-DD')),
    PARTITION sal_other
       VALUES LESS THAN (maxvalue)) ;

堆表与分区表转换：

SQL> CREATE TABLE p_emp
 (sal NUMBER(7,2))
 PARTITION BY RANGE(sal)
 (partition emp_p1 VALUES LESS THAN (2000),
 partition emp_p2 VALUES LESS THAN (4000));
 Table created.
 
SQL> CREATE TABLE dummy_y as SELECT sal
 FROM emp WHERE  sal<2000;
 Table created.

SQL> CREATE TABLE dummy_z as SELECT sal FROM emp WHERE sal
 BETWEEN 2000 AND 3999;
 Table created.

SQL> alter table p_emp exchange partition emp_p1
 with table dummy_y;
 Table altered.

SQL> alter table p_emp exchange partition emp_p2
 with table dummy_z;
 Table altered.

IOT表 索引组织表：

作用：1. 数据有序；2. 有利查询；3. I/O比较多

特点：

1. 存储rowheader / key / non-key(实际值) 由于non-key内容较多，可以选择overflow参数，将内容存储到某个特定表空间中

2. 在IOT表非key列做index，用的是logical rowid，不是用rowid，否则容易引起index invalid

创建：

 CREATE TABLE iot
(
   x  varchar2(10),
   y   DATE,
   z   CHAR (2000),
   CONSTRAINT iot_pk PRIMARY KEY (x)
)
ORGANIZATION INDEX
   TABLESPACE users
   INCLUDING y
   OVERFLOW TABLESPACE mytbs3;

CLUSTER表

作用：将主表+副表（总表+明细）组合；

特点：

1. 两表内查询省时；

2. 关联其他表，或者进行DML 费时间 

分类：

1. Index cluster 创建时不分配空间；

2. hash cluster 创建是直接分配空间

创建：

create cluster emp_dept_cluster
( deptno number(2) )
size 8192;

create table dept
( deptno number(2) primary key,
dname  varchar2(14),
loc    varchar2(13)
)
cluster emp_dept_cluster(deptno) ;

create table emp
( empno    number primary key,
   ename    varchar2(10),
    job      varchar2(9),
    mgr      number,
   hiredate date,
    sal      number,
   comm     number,
   deptno number(2) references dept(deptno) ;
)
cluster emp_dept_cluster(deptno) ;