已索引

外部表

定义：外部表是存储在数据库外面的表，外部表的数据存储在操作系统中，是操作系统文件。建立外部表时候，不会产生段、页、族等存储结构。只有与表相关的定义放在数据字典中，在数据库中不能对外部表的内容进行修改，不能对外部建立索引。不需要将外部表的数据装载到数据库中来，通过 sql 解码器来访问外部表。

外部表建表语法：
Create external table <表名> <表结构定义> from <控制文件选项> ｜ datafile <数据文件选项> [<数据文件参数列表>]；

案例1：从控制文件生成

1、查看数据文件：

$ cat cy.txt
1,AAAAA
2,BBBBB
3,DD
4,CCCC
5,FF
6,WERWRWRW
7,iiiii
8,nnnnn
9,SER
10,EWDEWRWRWRWRW

2、编写控制文件：

$ cat cy.ctl
LOAD DATA
INFILE 'cy.txt'
INTO TABLE DMTEST.EXT
FIELDS ','

3、创建目录对象

SQL> create directory extdir as '/dm8/ext';
SQL> grant read, write on directory extdir to dmtest;

注意：
如果 read 和 write 权限都不给的话，DMTEST 用户查询 EXT 表时会报没有读权限：
[-5724]:No read access to directory object [EXTDIR].
如果只给 read 不给 write 权限的话，DMTEST 用户查询 EXT 表时会报没有写权限：
[-5725]:No write access to directory object [EXTDIR].
之所以要有写权限，是因为访问外部表时要写日志文件。

4、创建外部表

SQL> create external table DMTEST.EXT(id int, name varchar(20)) 
from default directory extdir location('cy.ctl');

命令完成后，对表中数据进行查检，并检查日志文件，同时查看是否有 .bad 文件生成，如果有数据未导入，则根据情况进行处理。

5、查询外部表

SQL> connect DMTEST/DMTEST123
SQL> select * from ext;

LINEID     ID          NAME
---------- ----------- -------------
1          1           AAAAA
2          2           BBBBB
3          3           DD
4          4           CCCC
5          5           FF
6          6           WERWRWRW
7          7           iiiii
8          8           nnnnn
9          9           SER
10         10          EWDEWRWRWRWRW

10 rows got

案例2：从数据文件生成

1、查看数据文件：

$ cat cfop.txt
10|9|7
4|3|2|5
1|3|4|5
6|7

2、创建目录对象

SQL> create directory extdir as '/dm8/ext';
SQL> grant read, write on directory extdir to dmtest;

注意：
如果 read 和 write 权限都不给的话，DMTEST 用户查询 EXT 表时会报没有读权限：
[-5724]:No read access to directory object [EXTDIR].
如果只给 read 不给 write 权限的话，DMTEST 用户查询 EXT 表时会报没有写权限：
[-5725]:No write access to directory object [EXTDIR].
之所以要有写权限，是因为访问外部表时要写日志文件。

3、 创建外部表

SQL> create external table DMTEST.EXT2(c1 int, c2 int, c3 int) 
from datafile default directory extdir location('cfop.txt')
parms (fields delimited by '|' );

4、查询外部表

SQL> connect DMTEST/DMTEST123
SQL> select * from ext2;
LINEID     C1          C2          C3
---------- ----------- ----------- -----------
1          10          9           7
2          4           3           2
3          1           3           4
4          6           7           NULL

可以看到，表定义有3个列，数据文件中列数不够的为NULL，多的列丢弃。

跳过行头

如果数据文件中存在行头，可以使用 skip 1 来跳过第一行。

create external table DMTEST.EXT2(c1 int, c2 int, c3 int) 
from datafile default directory extdir location('cfop.txt')
parms (fields delimited by '|', skip 1);

删除外表部

跟普通表的删除方法一样:

SQL> drop table DMTEST.EXT;

外部表使用限制

在SQL手册中，定义外部表部分有说明。
1、 <表名>指定了所在建立的外部表名。
2、 外部表的表名最大长度为 128 字符。
3、 所建外部表至少包含一列，一张外部基表中至多可以包含 2048 列。
4、 外部表不能存大字段。
5、 外部表不能存在任何约束条件。
6、 外部表不能为临时表，不能建立分区。
7、 外部表不能创建任何索引。
8、 外部基表是只读的，不存在表锁，不允许对外部表进行增删改数据操作， 不允许 truncate 外部表操作。
9、 之前的版本控制文件路径、数据文件路径是采用的绝对路径，2023年下半年（第四季度）开始必须采用目录对象。