LLL的数据库培训-75-第五部分—Oracle11g-Oracle SQL对象管理—第2讲—创建表

LLL的数据库培训-75-第五部分—Oracle11g-Oracle SQL对象管理—第2讲—创建表

一、创建表

1、创建表通常应考虑的因素

--表的类型(堆组织表、临时表、索引组织表、分区表等);
--命名惯例;
--列的数据类型和尺寸;
--约束(主键、外键等);
--索引要求;
--初始存储要求;
--特殊功能(虚拟列、只读、并行、压缩、生成最少的重做日志、隐藏列等);
--增长需求;
--表及其索引使用的表空间。

2、创建堆组织表

什么是堆？堆是指表中的数据不以特定顺序存储，就是简单地堆到一起。

（1）创建默认的表

CREATE TABLE test(
ID number(8,0),
name varchar2(10),
sex char(2))
);

（2）创建指定表空间的表（包含主键、外键、等）

CREATE TABLE test(
ID number(8,0),
name varchar2(10),
sex char(2),
constraint id_pk primary key(id)
) tablespace test_tbsp

注意:一般创建表时，使用当前用户默认的表空间，而不单独设置，便于维护和管理。对于后面会讲的分区表，建议设置单独的表空间，同样是为了便于维护和管理。

3、实现虚拟列

（1）什么是虚拟列？

虚拟列是Oracle中一个非常有用的特性，可以在查询中添加计算列，而无需在表结构中添加实际列。

虚拟列常用于生成报告中的计算列、日期列、维度列等功能。

虚拟列(Virtual Column)是一种特殊的列，它并没有实际的存储空间，仅仅是一个计算式或一个函数等运算结果的映射。

该映射结果只在查询时进行计算，并返回查询结果集。因此，虚拟列不需要占用表结构的存储空间，也不需要在数据库中保存计算结果。

此功能在Oracle 11g及更高版本中拥有。

（2）使用虚拟列的优点

--可以在虚拟列上创建索引。Oracle会在内部创建基于函数的索引。
--可以在虚拟列中存储统计数据，这些数据可用于基于成本的优化器( CBO )。
--可以在WHERE子句中引用虚拟列。
--可以在数据库中永久地定义虚拟列，该虚拟列拥有核心定义。

（3）虚拟列案例

 --在where子句中引用虚拟列

insert into test values(1,'LLL01','F');
insert into test values(2,'LLL02','M');
insert into test values(20,'LLL20','F');
commit;

select id,name,
case 
 when id>10 then 'GOOD'
 when id<10 then 'WELL'
end
from test;

 --在数据库中永久定义虚拟列

create table test3 (
id number,
name varchar2(10),
virt_col {generated always} as (
 case 
  when id>10 then 'GOOD'
  when id<10 then 'WELL'
 end
 )
);
insert into test3(id,name) values(1,'LLL01');
insert into test3(id,name) values(2,'LLL02');
insert into test3(id,name) values(20,'LLL20');
commit;

（4）虚拟列的使用

--可以直接查询虚拟列；

select name,virt_col from test3;

--可以根据虚拟列更新表

update test3 set name='TTT01' where virt_col='GOOD';

（5）虚拟列的注意事项

--只能在普通的堆组织表中定义虚拟列。无法在索引组织表、外表、临时表、对象表和聚簇表中定义虚拟列。
--虚拟列无法引用其他虚拟列
--虚拟列只能引用它所在表中的列
--虚拟列的输出必须是标量值（如单个值，而不是一组值）

4、实现隐藏列

注意：此功能是在Oracle12C开始时才有的新特性。

（1）当列处于隐藏模式时，使用下面的命令无法查看它

--DESCRIBE命令;
--SELECT * 命令（查看表的所有列）;
--%ROWTYPE（在PL/SQL中）;
--Oracle CALL Interface(OCI)中的describe命令;

（2）如何实现隐藏列

创建表时实现隐藏列，表中的列也可以更改为隐藏模式，还可以向表添加隐藏列。

create table test3(
id number,
name varchar2(10),
invis_col number invisible)

insert into test3(id,name,invis_col) values(1,'LLL01',1);

（3）如何将隐藏列更改为可见列

alter table test modify(invis_col visible);

（4）如何可以访问隐藏列

使用select语句明确指定隐藏列名或使用其他DML语句（insert,delete,update等）直接设置引用也可以访问。

select id,name,invis_col from test3;

5、创建只读表

自Oracle Database11g开始，可以将单个表设置为只读模式。之前的版本只能对整个表空间或数据库进行设置为只读。

（1）设置只读表的原因

--表中的数据都是历史记录，一般情况下这些数据不应更新;
--你正在维护表，并希望在进行处理时表不会被其他人或程序更改;
--你想要删除表，并且想在执行该操作前，确定是否有用户要更新表中的数据。

（2）将表设置为只读模式

alter table test4 read only;

（3）将表设置为读写模式

alter table test4 read write;

6、创建延迟段

这个功能是从Oracle 11g R2后才可以默认的。即创建表时，都会延迟创建段，直到向表中插入第一行数据后，相关的段才会被创建。

注意:只有普通用户创建的表是可以延迟段创建。
向sys/system用户等创建的表是立刻创建段。
还有其他表类型创建也不能实现延迟段创建。
IOTs and other special tables like clustered tables, global temporary tables, session-specific temporary tables, internal tables, typed tables, AQ tables, external tablesare not supported. Tables owned by SYS,SYSTEM, PUBLIC, OUTLN, and XDB are also excluded.

（1）创建一张表

create table test5(id number,name varchar2(10));

（2）查看此表对应段的大小

select * from user_segments where segement_name='TEST5' and segment_type='TABLE';

7、创建含有自增列（自动增长列）的表

此功能是从12C开始有的功能。

在Oracle Database 12c以前，必须手动创建序列，然后在向表插入数据时访问这个序列，才能实现标识列的增长。有时候DBA会在表上创建触发器，依靠序列模仿自动增长列的功能。

（1）创建带自增列的表

create table test7(
id number generated by default as identity,
name varchar2(10));
alter table test7 add constraint id_pk primary key(id);

（2）插入数据

insert into test7(name) values('LLL02');

（3）使用自增列应注意的事项

--只允许应用于一个表；
--数据类型必须为数值型;
--不能设置默认值:
--会自动应用not null和not deferrable约束;
--使用create table ..as select命令无法集成标识列的属性。

还应注意：插入了自动列增长后，如果执行了回滚命令，事务就会回滚，但序列中的自动增长值不会回滚。这是序列的固有特。

可以回滚插入操作，但是序列值会被占用。

8、默认的SQL并行执行方式

在处理较大的表时，可能需要将表创建为并行模式。这可以设置Oracle以并行方式执行INSERT、UPDATE、 DELETE、 MERGE和查询等命令的程度。

（1）创建并行方式表

create table test8(id number,name varchar2(10)) parallel 2;

重点：如果表被创建以磨人的并行设置执行命令，那么以后所有命令都会以并行方式执行。

（2）修改并行度

alter table test8 parallel 1;

（3）注意：

并行度会对数据库性能产生影响，所以要慎用。

9、减少重做日志（nologging）

（1）nologgin的作用

在创建表时，可以使用NOLOGCING子句。这个NOLOGGING功能可以大幅度地减少某些操作产生的大量重做日志。在处理大量数据的情况中，为了提高性能，可以在创建表伊始和初次向表中插入数据时，减少产生的重做日志。

（2）减少重做日志的缺点

如果加载数据后(在备份表之前)出现了失效事件，就无法恢复数据。如果你能够承受一定的损失数据危险(或者可以快速重新加载数据)，就可以使用NOLOGGING
子句。如果这些数据是关键数据，那么就不应该使用NOLOGGING子句。如果你的数据可以轻松重建并需要提高加载大量数据的性能，就可以使用NOLOGGING子句。

有人认为NOLOGGING子句的作用，是避免生成表的所有DML操作的重做日志。这种理解是错误的。NOLOGGING功能从来不会影响标准DML语句(普通的INSERT、UPDATE和DELETE )重做日志的生成。

（3）NOLOGGING功 能可以大幅度减少下列操作生成的重做日 志:

--SQL*Loader直接路径加载;
--直接路径的INSERT /*+ append */;
--CREATE TABLE AS SELECT;
--ALTER TABLE MOVE;
--创建和重建索引。

（4）在使用NOLOGGING子句时需要注意一些技巧

如果数据库处于FORCE LOGGING模式，那么不论是否设置了NOLOGGING，所有操作都会生成重做日志。

同样，当加载表时如果表拥有已定义的外键约束，那么不论是否设置了NOLOGGING，都会生成重做日志。

SQL>col force_logging for a10
SQL>select force_logging from v$database;

FORCE_LOGG
----------
NO

（5）语法

create table test9(id number,name varchar2(10)) nologging;

alter table test9 nologging;

（6）通过开启自动跟踪统计数据信息查看

set autotrace on或set autotrace trace statistics;

SQL>create table test12(id number) nologging;
SQL>create table test13(id number) nologging;

insert  /*+append*/ into test12 select level from dual connect by level<=10000;
2996 redo size

insert into test13 select level from dual connect by level<=10000;
150148 redo size

10、通过查询结果创建表

（1）包含表结构+数据

create table test14 as select * from dba_data_files;

（2）只包含表结构

create table test15 as select * from dba_data_files where 1=0;