Using the ORACLE_LOADER Access Driver to Create Partitioned External Tables

p1a.dat:

1, AAAAA Plumbing,01372,

28, Sparkly Laundry,78907,

13, Andi's Doughnuts,54570,

p1b.dat:

51, DIY Supplies,61614,

87, Fast Frames,22201,

89, Friendly Pharmacy,89901,

p2.dat:

121, Pleasant Pets,33893,

130, Bailey the Bookmonger,99915,

105, Le Bistrot du Chat Noir,94114,

p3.dat:

210, The Electric Eel Diner,07101,

222, Everyt'ing General Store,80118,

231, Big Rocket Market,01754,

The external table uses range partitioning on CUSTOMER_NUMBER to create three partitions.

SQL> create table customer_list_xt

  (CUSTOMER_NUMBER number, CUSTOMER_NAME VARCHAR2(50), POSTAL_CODE CHAR(5))

    organization external

      (type oracle_loader default directory def_dir1)

    partition by range(CUSTOMER_NUMBER)

    (

      partition p1 values less than (100) location('p1a.dat', 'p1b.dat'),

      partition p2 values less than (200) location('p2.dat'),

      partition p3 values less than (300) location('p3.dat')

   );

1. Using the ORACLE_DATAPUMP Access Driver to Create Partitioned External Tables

In this example, the dump files used are the same as those created in the previous example using the ORACLE_LOADER access driver.

把上面的表每个分区分成两个子分区：

SQL> create table customer_list_dp_p1_sp1_xt

    organization external

      (type oracle_datapump default directory def_dir1 location('p1_sp1.dmp'))

    as

      select customer_number, customer_name, postal_code

        from customer_list_xt partition (p1)

        where to_number(postal_code) < 50000;

SQL> create table customer_list_dp_p1_sp2_xt

    organization external

      (type oracle_datapump default directory def_dir1 location('p1_sp2.dmp'))

    as

      select customer_number, customer_name, postal_code

        from customer_list_xt partition (p1)

        where to_number(postal_code) >= 50000;

SQL> create table customer_list_dp_p2_sp1_xt

    organization external

      (type oracle_datapump default directory def_dir1 location('p2_sp1.dmp'))

    as

      select customer_number, customer_name, postal_code

        from customer_list_xt partition (p2)

        where to_number(postal_code) < 50000;

SQL> create table customer_list_dp_p2_sp2_xt

    organization external

      (type oracle_datapump default directory def_dir1 location('p2_sp2.dmp'))

    as

      select customer_number, customer_name, postal_code

        from customer_list_xt partition (p2)

        where to_number(postal_code) >= 50000;

SQL> create table customer_list_dp_p3_sp1_xt

    organization external

      (type oracle_datapump default directory def_dir1 location('p3_sp1.dmp'))

    as

      select customer_number, customer_name, postal_code

        from customer_list_xt partition (p3)

        where to_number(postal_code) < 50000;

SQL> create table customer_list_dp_p3_sp2_xt

    organization external

      (type oracle_datapump default directory def_dir1 location('p3_sp2.dmp'))

    as

      select customer_number, customer_name, postal_code

        from customer_list_xt partition (p3)

        where to_number(postal_code) >= 50000;

使用上面文件创建一个包含子分区的表(此处还使用了虚拟列)：

SQL> create table customer_list_dp_xt

    (customer_number    number,

     CUSTOMER_NAME      VARCHAR2(50),

     postal_code        CHAR(5),

     postal_code_NUM    as (to_number(postal_code)))

    organization external

      (type oracle_datapump default directory def_dir1)

    partition by range(customer_number)

     subpartition by range(postal_code_NUM)

      (

      partition p1 values less than (100)

       (subpartition p1_sp1 values less than (50000) location('p1_sp1.dmp'),

        subpartition p1_sp2 values less than (MAXVALUE) location('p1_sp2.dmp')),

      partition p2 values less than (200)

       (subpartition p2_sp1 values less than (50000) location('p2_sp1.dmp'),

        subpartition p2_sp2 values less than (MAXVALUE) location('p2_sp2.dmp')),

      partition p3 values less than (300)

       (subpartition p3_sp1 values less than (50000) location('p3_sp1.dmp'),

        subpartition p3_sp2 values less than (MAXVALUE) location('p3_sp2.dmp'))

     );