oracle 外部表使用详解

最新推荐文章于 2024-07-31 21:21:45 发布

Errol的杂货铺

最新推荐文章于 2024-07-31 21:21:45 发布

阅读量8.7k

点赞数 3

分类专栏： oracle 文章标签： oracle 数据库大数据

本文链接：https://blog.csdn.net/Mr_Errol/article/details/52100877

版权

oracle 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

外部表，相对于数据库内部表来说的，意思是将操作系统文件作为一个数据库，数据从文件而来。
我把它理解成类似指针的东西，告诉用户，这个表的数据是从哪些文件来的。

外部表的创建，这里写的比网上其他介绍复杂许多，简单的用法，自行百度，这里只考虑多文件
大数据、每行数据没有分隔符，字段靠截取、外部白导入类型是oracle loader的条件。网上的
一些文章只做了一些简单的介绍和用法，只能满足简单的需求，而真是需要使用到外部表的一半都
是上百万级别的大数据。先看看基本语法，里面的参数将会一一详解。

语法如下：

create table "table1" 
(
    id varchar2(4),
    name varchar2(20)
)ORGANIZATION external --外部表的关键字
(
    type oracle_loader --外部表有两种类型，这里使用oracle_loader
    DEFAULT DIRECTORY DATA_FILE_PATH --文件目录
    ACCESS PARAMETERS( --参数
        RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK  
        SKIP 10
        BADFILE 'DATA_LOG_PATH':'table1.bad' 
        NODISCARDFILE
        LOGFILE 'DATA_LOG_PATH':'table1.log' 
        READSIZE 1048576 
        FIELDS LDRTRIM 
        REJECT ROWS WITH ALL NULL FIELDS
        ( 
            "id" (1,4) char(4) nullif ("id" = blanks),
            "name" (5,24) char(20) nullif ("name" = blanks)
        )
)
location
(
    test1.txt,
    test2.txt
)REJECT LIMIT UNLIMITED;

使用外部表需要注意一下几点：
1、数据文件（test1.txt，test2.txt）的存放路径必须是数据库服务器能够访问到的地方！
这点非常关键，许多产品一半是数据库服务器跟应用后台分离的方式，所以，这就要求文件能
够放在数据库服务器上，而不是后台服务器，或者两者之间加一块共享存储，这里两台都能访
问。

2、创建外部表之前，需要先创建目录对象DATA_FILE_PATH，也就是，数据文件需要放在这
个目录对象中，也就是，这个目录对象的路径，是数据库服务器上的路径。创建语法如下：

CREATE OR REPLACE DIRECTORY IMPORT_DATA_FILE_PATH AS '/home/file'

由于上面的例子我把生成的log、bad文件单独存放，所以建立两个目录。

3、执行创建外表语句，可以在oracle客户端执行，且创建表不加载数据，也就是数据如果有错，
或者文件不存在，不会报错，创建是成功的，只有当insert到正式表时，才是加载数据的过程。

4、外部表只能查询，不能做DML操作，外部表只能查询，不能做DML操作，外部表只能查询，
不能做DML操作，重要的事情说3遍。外部表只能当作中间工具，建立好外部表时，使用
insert into 正式表 select * from 外部表；
加载数据到正式表中。这个过程可以使用查询并行和DML并行，提升加载性能（这里有坑，后面
解释）。

现在来讲讲外部表的一些参数

1、TYPE 外部表的类型，有两种，一种是oralce_loader，一种是ORACLE_DATAPUMP，后者
没用过，也讲不来，自行百度。oralce_loader类型，就可以把外部表当作多个sqlldr，如果没有
了解过sqlldr，可以先了解一下，比较外部表的本质还是这个。具体有多少个sqlldr呢，差不多
location里面有多少个文件，就有多少个sqlldr，所以，使用外部表导入大数据，性能会比一个
sqlldr好得多。

2、DEFAULT DIRECTORY 就是指定目录对象，文件一定要在这里面！！！

3、RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK 指定数据库的字符集，
数据库字符集可以查询出来。
select userenv(‘language’) from dual；

4、BADFILE 指定bad文件存在路径，什么是bad文件，就是数据里面，违反了字段规则的数据
，就会被当中bad数据放到里面。如果不需要生成，则写NOBADFILE.

5、DISCARDFILE 丢弃的文件，跟bad文件不一样，这个是违反了筛选条件的数据，上面的例
子没写，这里写下：

SKIP 10
BADFILE 'DATA_LOG_PATH':'table1.bad' 
NODISCARDFILE
LOGFILE 'DATA_LOG_PATH':'table1.log'
LOAD WHEN ("id" != blanks)

6、LOAD WHEN 加载条件，结合上面说，就是筛选条件，这里面的写法只能指定字段的值，不
能限制类型，而且语法限制条件也很多，sql的函数不能用，只能使用and or = != 这些。上面
DISCARDFILE ，就是如果不满足这个load when，则这行数据将会被丢弃，放到你指定的文件中
去，如果不需要生成，则NODISCARDFILE。
回过来说这个load when ，写法也很多，可以指定字段，也可以指定长度，比如：
load when (1:20) != blanks，就是说，从1到20个字符不能是空白。这个load when官方文档也
说的很少，具体的用法，需要自己慢慢摸索。

7、SKIP N 跳过，顾名思义，加载数据时候告诉Oracle跳过多少行，这里挺坑，一个数据文件，
跳过没问题，如果有多个数据文件，它只能跳过第一个文件的n行，这不坑爹吗？还有，上面说
insert到正式表的可以使用并行，注意！！！！！SKIP，只能在非并行模式下使用！！！！！！！
如果需要使用并行DML或者查询，麻烦把这个参数去掉！！！！！

8、READSIZE 1048576 这个是指定一次性读取最大值。

9、FIELDS LDRTRIM 加载的时候，每个字段都要trim一下

10、REJECT ROWS WITH ALL NULL FIELDS 就是说如果字段没有数据，则为null

11、字段规则，很简单，如果是有分隔符的写法，网上很多，这里模拟的数据是没有分隔符的，
类似于这样：
132334342354656546646454564564564564535242
“id” (1,4) char(4) nullif (“id” = blanks) 分别是字段名，截取起始位置，类型和长度，条件是
如果为空格则字段为Null。这里是字符串类型的，如果是number类型的字段，条件这么写:
“age” (21,23) INTEGER EXTERNAL(3)

12、location 里面就是需要加载的文件名字

13、REJECT LIMIT UNLIMITED 默认是有限制的加载，这里设置的是无限制加载。

外部表的参数太多了，有一些没用过，也不知道怎么用，有兴趣的，可以去oracle官方文档里面
慢慢看，全英文的。