Oracle 表

一、表的分类

    堆组织表：普通的Oracle表，数据的存储没有特定的顺序。

    索引组织表：存储B-树结构中排序的数据。

    集群表：集群表的列经常是一起被请求的，所以集群表是共享相同数据块的一组表的一部分。

    分区表：将大量数据根据不同的条件划分成称为分区的子表。

计算表和索引的潜在尺寸的算法：以字节为单位的行尺寸乘以表中估计的行数。(DBMS_SPACE.CREATE_TABLE_COST(...))

二、创建表

create table emp(
empno number(5) primary key, --主键约束
ename varchar2(15) not null,
ssn number(9),
job varchar2(10),
mgr number(5),
hiredate date default(sysdate),
sal number(7,2),
comm number(7,2),
deptno number(3) not null
constraint dept_fkey references hr.dept(dept_id)) --外键约束
tablespace test01; --指定表所属表空间

三、管理表

    创建虚拟列：

create table emp(
empno number(5) primary key,
ename varchar2(15) not null,
ssn number(9),
sal number(7,2),
hrly_rate number(7,2) generated always as (sql/2080)
[virtual] --虚拟列关键字
constraint hourlyrate check(hrly_rate > 8.00)); --检查约束

虚拟列由基于一个或多个实际列的表达式计算派生，虚拟列的数据不永久存储在磁盘上，可以在虚拟列上定义索引。

添加表列：

alter table emp add (retired char(1));

删除表列：

alter table emp drop (retired);

将表列标记为未用(用于包含大量数据的表):

alter table emp set unused(hiredate, mgr);

    查询是不会看到该列，且定义在该列上的所有依赖对象(约束、索引)都被删除。

    之后再合适的时间永久删除列数据：

alter table emp drop unused columns [checkpoint 10000];

重命名表列：

alter table emp rename to emp01;

删除表中数据：

truncate table test;
delete * from table_name;

    truncate是DDL命令，不能使用rollback撤销，可以快速删除所有行。

    delete是DML命令，需要将更改写入撤销段，执行起来耗时。

CTAS建表与速度优化：

create table employee_new
as
select * from employees
parallel degree 4 --用多个进程执行数据加载
nologging; --不将更改信息记录进重做日志和回滚段中

在表空间之间快速移动表：

alter table employee move new_tablespace;

    移动后表的索引不可用，需要重建(rebuild)

设置表为只读模式：

alter table test01 read only;
alter table test01 read write;

表压缩：

create table test01(
name varchar2(20)
address varchar2(50))
compress for all operations; --对表上的所有操作启用压缩
create table test01(
name varchar2(20)
address varchar(50))
compress for direct_load operations; --仅启用直接路径插入的压缩

    表压缩优点：节省磁盘空间，降低内存使用，提高查询性能。

    表压缩缺点：执行数据装载和DML操作时表压缩需要一定的开销，删除压缩数据的速度也慢，在压缩表中进行更新也比较慢。

    可以采用表空间压缩，该表空间中创建的所有表将自动压缩。

    查看哪些表是压缩的：

select table_name,compression,compress_for from dba_tables;

删除表：

drop table table_name;
flashback table emp to before drop;
drop tabel emp purge; --永久删除表
drop table emp cascade constraints; --将表中包含的约束一并删除

    删除表时，表上的所有索引也将被删除。

临时表：

    临时表在用户的临时表空间创建，并且只在向临时表发布了第一个insert语句后才分配临时段，当完成事务或会话后，将根据临时表的定义撤销临时段的分配。临时表提高了包含复杂查询的事务的性能。

create global temporary table flight_status(
destination varchar2(30),
startdate DATE,
reture_date date,
ticket_price number)
on commit preserve rows; --创建会话临时表
create global temporary table sales_info(
customer_name varchar2(30),
transaction_no number,
transaction_date date)
on commit delete rows; --创建事务临时表

    会话临时表在整个会话期间都保存数据，事务临时表只要事务被提交或者回滚，数据将从临时表中删除。

索引组织表：

拥有所有你和表两者的特征，以B-树索引结构(主键排序方式)存储数据。适合需要根据主键值来发布查询的情况。

create table employee_new(
employee_id number,
dept_id number,
name varchar2(30),
address varchar2(120),
constraint pk_employee_new primary key(employee_id))
organization index --指定为索引组织表
tablespace empindex_01  
pctthreshold 25 --指定索引块中为employee_new预留的空间百分比，表行中超出每个数据块中的25%极限的任何部分都保存在溢出区
include name --指定和键列一起存储的非键列
overflow tablespace overflow_tables; --保存索引块中的溢出数据

分区表：

    将大表从逻辑上分为相对较小的块，以便于查询处理、DML操作和数据库管理，所有的分区都共享相同的逻辑定义、列定义和约束。可以在一个大表的分区上执行备份和恢复操作、数据装载以及索引创建。

    分区方法：范围分区、间隔分区、散列分区、列表分区、引用分区、系统分区。

范围分区：

create table sales_data(
ticket_no number,
sale_year int not null,
sale_month int not null,
sale_day int not null)
partition by range(sale_year,sale_month,sale_day)
(partition sales_q1 values less than(2008,04,01)
tablespace ts1,
partition sales_q2 values less than(2008,07,01)
tablespace ts2,
partition sales_q3 values less than(2008,10,01)
tablespace ts3,
partition sales_q4 values less than(2009,01,01)
tablespace ts4);

间隔分区：

create table interval_sales(
prod_id number(6),
cust_id number,
time_id date,
channel_id char(1),
promo_id number(6),
quantity_sold number(3),
amount_sold number(10,2))
partition by range(time_id) --在指定间隔分区之前必须指定至少一个范围分区
interval(numtoyminterval(1,'MONTH')) --interval指定创建间隔分区表，分区键必须是number或date类型
(partition p0 values less than(to_date('1-1-2006','DD-MM-YYYY')),
partition p1 values less than(to_date('1-1-2007','DD-MM-YYYY')),
partition p2 values less than(to_date('1-7-2008','DD-MM-YYYY')),
partition p3 values less than(to_date('1-1-2009','DD-MM-YYYY')));