1 说明

分区的外部表和存储在数据库中的分区表有点像，但不同的是外部表分区可以存储在文件系统上，如 Apache Hive storage，或Hadoop Distributed File System (HDFS)。

2 分区的外部表一些限制

1) All restrictions that apply to non-partitioned external tables also apply to partitioned external tables.http://www.cndba.cn/Expect-le/article/2521http://www.cndba.cn/Expect-le/article/2521

所有外部表上的限制在分区的外部表上都有。

2) Partitioning restrictions that apply to tables stored in the database also apply to partitioned external tables, such as the maximum number of partitions.

分区表上的所有限制，在分区的外部表上也都有。

3) Oracle Database cannot guarantee that the external files for partitions contain data that satisfies partitioning definitions.

Oracle数据库不能保证分区的外部文件包含满足分区定义的数据。http://www.cndba.cn/Expect-le/article/2521http://www.cndba.cn/Expect-le/article/2521

4) Only the DEFAULT DIRECTORY and LOCATION clauses can be specified in a PARTITION or SUBPARTITION clause.

 http://www.cndba.cn/Expect-le/article/2521

5) When altering a partitioned external table with the ALTER TABLE statement, the following clauses are not supported: MODIFY PARTITION, EXCHANGE PARTITION, MOVE PARTITION, MERGE PARTITIONS, SPLIT PARTITION, COALESCE PARTITION, and TRUNCATE PARTITION.

6) Reference partitioning, automatic list partitioning, and interval partitioning are not supported.

7) Subpartition templates are not supported.

http://www.cndba.cn/Expect-le/article/2521http://www.cndba.cn/Expect-le/article/2521

8) The ORACLE_DATAPUMP access driver cannot populate external files for partitions using a CREATE TABLE AS SELECT statement.

9) Incremental statistics are not gathered for partitioned external tables.

10) In addition to restrictions on partitioning methods that can be used for the other drivers, range and composite partitioning are not supported for the ORACLE_HIVE access driver.

11) A SELECT statement with the EXTERNAL MODIFY clause cannot override partition-level or subpartition-level clauses. Only external clauses supported at the table level can be overridden with the EXTERNAL MODIFY clause. Because the LOCATION clause is not allowed at the table level for a partitioned external table, it cannot be overridden with the EXTERNAL MODIFY clause.

3 创建分区的外部表示例

-- 创建目录，并赋访问权限

CREATE OR REPLACE DIRECTORY data_dir1
    AS '/flatfiles/data1';
CREATE OR REPLACE DIRECTORY data_dir2
    AS '/flatfiles/data2';
CREATE OR REPLACE DIRECTORY bad_dir
    AS '/flatfiles/bad';

CREATE OR REPLACE DIRECTORY log_dir
    AS '/flatfiles/log';

GRANT READ ON DIRECTORY data_dir1 TO oe;

GRANT READ ON DIRECTORY data_dir2 TO oe;

GRANT WRITE ON DIRECTORY bad_dir TO oe;

GRANT WRITE ON DIRECTORY log_dir TO oe;

-- 使用oe用户登录，输入密码

CONNECT oe

-- 创建分区的外部表

CREATE TABLE orders_external_range(
    order_id           NUMBER(12),
    order_date         DATE NOT NULL,
    customer_id        NUMBER(6) NOT NULL,
    order_status       NUMBER(2),
    order_total        NUMBER(8,2),
    sales_rep_id       NUMBER(6))
ORGANIZATION EXTERNAL(
   TYPE ORACLE_LOADER
   DEFAULT DIRECTORY data_dir1
   ACCESS PARAMETERS(
      RECORDS DELIMITED BY NEWLINE
      BADFILE bad_dir: 'sh%a_%p.bad'
      LOGFILE log_dir: 'sh%a_%p.log'
      FIELDS TERMINATED BY '|'
      MISSING FIELD VALUES ARE NULL))
PARALLEL
REJECT LIMIT UNLIMITED
PARTITION BY RANGE (order_date)
  (PARTITION month1 VALUES LESS THAN (TO_DATE('31-12-2014', 'DD-MM-YYYY'))
        LOCATION ('sales_1.csv'),
    PARTITION month2 VALUES LESS THAN (TO_DATE('31-01-2015', 'DD-MM-YYYY'))
        LOCATION ('sales_2.csv'),
    PARTITION month3 VALUES LESS THAN (TO_DATE('28-02-2015', 'DD-MM-YYYY'))
        LOCATION ('sales_3.csv'),
    PARTITION pmax VALUES LESS THAN (MAXVALUE)
        DEFAULT DIRECTORY data_dir2 LOCATION('sales_4.csv'));

更多示例和关于分区的外部表，请查看官方文档：

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/admin/managing-tables.html#GUID-A20ABC65-3E62-46B8-90D2-85AE6A29697D

 

版权声明：本文为博主原创文章，未经博主允许不得转载。

外部表分区