Creating Tables Sharded by Directory

Create directory-based sharded tables using PARTITION BY DIRECTORY in the CREATE SHARDED TABLE statement.

For example:

CREATE SHARDED TABLE customers
( id             NUMBER NOT NULL
, name           VARCHAR2(30)
, address        VARCHAR2(30)
, status         VARCHAR2(1)
,
CONSTRAINT cust_pk PRIMARY KEY(id)
)
PARTITION BY DIRECTORY (id)
( PARTITION p1 TABLESPACE tbs1,
  PARTITION p2 TABLESPACE tbs2,
  PARTITION p3 TABLESPACE tbs3…)
DIRECTORY TABLESPACE tablespace-name //This clause is valid only for root table creation
;

Note:

  • The clause DIRECTORY TABLESPACE is only allowed after the partition definition, and it is only valid for the root table creation of a directory-based sharded table.

    This clause gives you a way to create a separate tablespace just for the directory table instead of having to use the default tablespace.

  • Maximum length for the root table name (identifier length limit) is 113, to account for the additional characters added to the view name created on the root table (as in root_table_name_$SHARD_DIR_VIEW).

  • The directory table is automatically created during root table creation. The definition of the directory table is:

    <shard user schema>.<root_table>$SDIR

  • Unlike in user-defined sharding, key values are not specified for the partitions in the CREATE TABLE statement.

  • If a child table is created with parent clause in a different schema from the root table, an additional privilege is required for the child table's schema owner. (This is only for directory-based sharding and is not required for regular user-defined sharding.)

    This is because there is a foreign key constraint on the child table to the directory table's sharding key columns, to ensure that no rows can be inserted into the child table without the sharding key value being present in the directory mapping. As a consequence, the child table's schema needs a reference privilege on the directory table's sharding key columns.

    See "Granting References" below.

Granting References

This case is illustrated in this example:

  • Root table dealerships is under schema user1, and has account_id as the sharding key.

  • Child table salespeople is under schema user2, and is defined via "parent user1.dealerships".

Before this salespeople child table can be created, you need: 

  • grant all privileges on user1.dealerships to user2;

    This is the same as needed for user-defined sharding.

  • grant references (account_id) on user1.dealerships$sdir to user2;

    This is new for directory-based sharding.

Note that dealerships$sdir is the internally generated directory table name; it has the format of root_table_name$sdir.

Without the 2nd grant, the child table creation DDL will succeed on the shard catalog but will fail on the shards (as the foreign key is only added on the shards).