外部表与内部表的本质区别

一、外部表与常规内部表在存储结构上存在根本差异：

​1.存储机制：外部表仅将表定义信息（元数据）存储在数据字典中，不会像内部表那样在表空间中创建实际的段、页簇等物理存储结构
​2.数据来源：外部表数据直接来源于操作系统文件（如文本文件），不占用数据库表空间存储

外部表的重要限制
二、使用外部表时需特别注意以下约束条件：

​1.只读特性：
严格禁止执行INSERT、UPDATE、DELETE等DML操作
不允许使用TRUNCATE语句清空表数据
所有数据修改操作必须通过修改源文件实现
​2.权限要求：
用户创建外部表必须对指定文件目录拥有读写权限
此权限要求是因为查询外部表时系统会在该目录下生成日志文件等临时文件
​3.性能考虑：
由于数据不经过数据库引擎优化存储，查询性能通常低于内部表
建议对频繁查询的外部表字段创建适当的索引
三、最新版的建表方式：
1.外部表创建需要指定数据库目库
grant CREATE ANY directory to dmtest;
create directory dir_data as '/dm/backup/data';
grant read, write on directory dir_data to dmtest;
2.方式一指定数据文件创建外部表：
create EXTERNAL TABLE t_extemp (
"EMPLOYEE_ID" INT,
"EMPLOYEE_NAME" VARCHAR(20),
"IDENTITY_CARD" VARCHAR(18),
....)
from DATAFILE DEFAULT DIRECTORY DIR_DATA LOCATION
('text01.txt')
parms (fields DELIMITED by '|', records 0x0a, skip 1, character_code 'UTF-8');
方式二：指定控制文件创建外部表
（1）编写控制文件
[dmdba@localhost data]$ cat dept.ctl
options
(skip=1
errors=10
log='t_extdept.log'
character_code='UTF-8')
load data
infile 'DMHR_DEPARTMENT.txt' str X '0d0a'
badfile 't_extdept.bad'
into table t_extdept
fields '|'
（2）基于控制文件创建外部表
create EXTERNAL TABLE t_extdept (
"EMPLOYEE_ID" INT,
"EMPLOYEE_NAME" VARCHAR(20),
"IDENTITY_CARD" VARCHAR(18),
....)
from DEFAULT DIRECTORY DIR_DATA LOCATION ('dept.ctl') ;

四、外部表的参数选项
FIELDS DELIMITED BY <表达式>
| RECORDS DELIMITED BY <表达式>
| ERRORS
| BADFILE '<错误日志文件名称>'
| LOG '<日志文件名称>'
| NULL_STR 字符串>
| SKIP <跳过行数>
| CHARACTER_CODE <文件字符集
注意：
外部表不支持指定约束，不支持创建索引（如not null等）