DDL Execution in a Sharded Database
To create a schema in a sharded database, you must issue DDL commands on the shard catalog database, which validates the DDLs and executes them locally before they are executed on the shards.
The shard catalog database contains local copies of all of the objects that exist in the sharded database, and serves as the master copy of the sharded database schema. If the shard catalog validation and execution of DDLs are successful, the DDLs are automatically propagated to all of the shards and applied in the order in which they were issued on the shard catalog.
If a shard is down or not accessible during DDL propagation, the shard catalog keeps track of DDLs that could not be applied to the shard, and then applies them when the shard is back up.
When a new shard is added to a sharded database, all of the DDLs that have been executed in the sharded database are applied in the same order to the shard before it becomes accessible to clients.
There are two ways you can issue DDLs in a sharded database.
-
Use the
GDSCTL SQL
command.When you issue a DDL with the
GDSCTL SQL
command, as shown in the following example,GDSCTL
waits until all of the shards have finished executing the DDL and returns the status of the execution.GDSCTL> sql “create tablespace set tbsset”
-
Connect to the shard catalog database using SQL*Plus using the
GDS$CATALOG.sdbname
service.When you issue a DDL command on the shard catalog database, it returns the status when it finishes executing locally, but the propagation of the DDL to all of the shards happens in the background asynchronously.
SQL> create tablespace set tbsset;
Note:
Using the SYS account to execute shard DDL is not recommended; create a privileged account for this purpose.For information about DDL syntax extensions for Oracle Sharding, see DDL Syntax Extensions for Oracle Sharding.
- Creating Objects Locally and Globally
Objects created using GDSCTL creates global, sharded database objects; however, you can create local or global objects by connecting to the shard catalog with SQL*Plus. - DDL Syntax Extensions for Oracle Sharding
Oracle Sharding includes SQL DDL statements with syntax that can only be run against a sharded database.
Parent topic: Sharded Database Schema Design
Creating Objects Locally and Globally
Objects created using GDSCTL creates global, sharded database objects; however, you can create local or global objects by connecting to the shard catalog with SQL*Plus.
When a DDL to create an object is issued using the GDSCTL
sql
command, the object is created on all of the shards. A master
copy of the object is also created in the shard catalog database. An object that exists
on all shards, and the shard catalog database, is called a sharded database
object.
When connecting to the shard catalog using SQL*Plus, two types of objects can be created: sharded database objects and local objects. Local objects are traditional objects that exist only in the shard catalog. Local objects can be used for administrative purposes, or they can be used by multi-shard queries originated from the shard catalog database, to generate and store a report, for example.
Sharded objects cannot have any dependency on local objects. For example, you cannot create an all-shard view on a local table.
The type of object (sharded database or local) that is created in a SQL*Plus
session depends on whether the SHARD DDL
mode is enabled in the
session. This mode is enabled by default on the shard catalog database for the
all-shards user, which is a user that exists on all of the shards and the shard catalog
database. All of the objects created while SHARD DDL
is enabled in a
session are sharded database objects.
To enable SHARD DDL
in the session, the all-shards user must run
ALTER SESSION ENABLE SHARD DDL
All of the objects created while SHARD DDL
is disabled are
local objects. To create a local object, the all-shards user must first run
ALTER SESSION DISABLE SHARD DDL
See ALTER SESSION for more information about the SHARD DDL
session
parameter.
Parent topic: DDL Execution in a Sharded Database
DDL Syntax Extensions for Oracle Sharding
Oracle Sharding includes SQL DDL statements with syntax that can only be run against a sharded database.
Changes to query and DML statements are not required to support Oracle Sharding, and the changes to the DDL statements are very limited. Most existing DDL statements will work the same way on a sharded database, with the same syntax and semantics, as they do on a non-sharded database.
- CREATE TABLESPACE SET
This statement creates a tablespace set that can be used as a logical storage unit for one or more sharded tables and indexes. A tablespace set consists of multiple Oracle tablespaces distributed across shards in a shardspace. - ALTER TABLESPACE SET
This statement alters a tablespace set that can be used as a logical storage unit for one or more sharded tables and indexes. - DROP TABLESPACE SET and PURGE TABLESPACE SET
These statements drop or purge a tablespace set, which can be used as a logical storage unit for one or more sharded tables and indexes. - CREATE TABLE
TheCREATE TABLE
statement has been extended to create sharded and duplicated tables, and specify a table family. - ALTER TABLE
TheALTER TABLE
statement is extended to modify sharded and duplicated tables. - ALTER SESSION
TheALTER SESSION
statement is extended to support sharded databases.
Parent topic: DDL Execution in a Sharded Database
CREATE TABLESPACE SET
This statement creates a tablespace set that can be used as a logical storage unit for one or more sharded tables and indexes. A tablespace set consists of multiple Oracle tablespaces distributed across shards in a shardspace.
The CREATE TABLESPACE SET
statement is intended specifically for Oracle
Sharding. Its syntax is similar to CREATE TABLESPACE
.
CREATE TABLESPACE SET tablespace_set
[IN SHARDSPACE shardspace]
[USING TEMPLATE (
{ MINIMUM EXTENT size_clause
| BLOCKSIZE integer [ K ]
| logging_clause
| FORCE LOGGING
| ENCRYPTION tablespace_encryption_spec
| DEFAULT [ table_compression ] storage_clause
| { ONLINE | OFFLINE }
| extent_management_clause
| segment_management_clause
| flashback_mode_clause
}...
)];
Note that in system-managed sharding there is only one default shardspace in the sharded database. The number of tablespaces in a tablespace set is determined automatically and is equal to the number of chunks in the corresponding shardspace.
All tablespaces in a tablespace set are bigfile tablespaces and have the same
properties. The properties are specified in the USING TEMPLATE
clause
and they describe the properties of one single tablespace in the tablespace set. This
clause is the same as permanent_tablespace_clause
for a typical
tablespace, with the exception that a data file name cannot be specified in the
datafile_tempfile_spec
clause. The data file name for each
tablespace in a tablespace set is generated automatically.
Note that a tablespace set can only consist of permanent tablespaces, there is no system, undo, or temporary tablespace set. Also, note that in the example below the total data file size of the tablespace set is 100mxN (where N is the number of tablespaces in the tablespace set).
Example
CREATE TABLESPACE SET TSP_SET_1 IN SHARDSPACE sgr1
USING TEMPLATE
( DATAFILE SIZE 100m
EXTEND MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
);
Parent topic: DDL Syntax Extensions for Oracle Sharding
ALTER TABLESPACE SET
This statement alters a tablespace set that can be used as a logical storage unit for one or more sharded tables and indexes.
The SHARDSPACE
property of a tablespace set cannot be
modified. All other attributes of a tablespace set can be altered just as for a regular
permanent tablespace. Because tablespaces in a tablespace set are bigfile, the
ADD DATAFILE
and DROP DATAFILE
clauses are not
supported.
Parent topic: DDL Syntax Extensions for Oracle Sharding
DROP TABLESPACE SET and PURGE TABLESPACE SET
These statements drop or purge a tablespace set, which can be used as a logical storage unit for one or more sharded tables and indexes.
The syntax and semantics for these statements are similar to
DROP
and PURGE TABLESPACE
statements.
Parent topic: DDL Syntax Extensions for Oracle Sharding
CREATE TABLE
The CREATE TABLE
statement has been extended to create
sharded and duplicated tables, and specify a table family.
Syntax
CREATE [ { GLOBAL TEMPORARY | SHARDED | DUPLICATED} ]
TABLE [ schema. ] table
{ relational_table | object_table | XMLType_table }
[ PARENT [ schema. ] table ] ;
The following parts of the CREATE TABLE
statement
are intended to support Oracle Sharding:
-
The
SHARDED
andDUPLICATED
keywords indicate that the table content is either partitioned across shards or duplicated on all shards respectively. TheDUPLICATED
keyword is the only syntax change to create duplicated tables. All other changes described below apply only to sharded tables. -
The
PARENT
clause links a sharded table to the root table of its table family. -
In system and composite sharding, to create a sharded table,
TABLESPACE SET
is used instead ofTABLESPACE
. All clauses that containTABLESPACE
are extended to containTABLESPACE SET
. -
Three clauses:
consistent_hash_partitions
,consistent_hash_with_subpartitions
, andpartition_set_clause
in thetable_partitioning_clauses
.table_partitioning_clauses ::= {range_partitions | hash_partitions | list_partitions | composite_range_partitions | composite_hash_partitions | composite_list_partitions | reference_partitioning | system_partitioning | consistent_hash_partitions | consistent_hash_with_subpartitions | partition_set_clause }
Example
CREATE SHARDED TABLE customers
( cust_id NUMBER NOT NULL
, name VARCHAR2(50)
, address VARCHAR2(250)
, location_id VARCHAR2(20)
, class VARCHAR2(3)
, signup_date DATE
,
CONSTRAINT cust_pk PRIMARY KEY(cust_id, class)
)
PARTITIONSET BY LIST (class)
PARTITION BY CONSISTENT HASH (cust_id)
PARTITIONS AUTO
(PARTITIONSET gold VALUES (‘gld’) TABLESPACE SET ts2,
PARTITIONSET silver VALUES (‘slv’) TABLESPACE SET ts1)
;
Example of consistent_hash_with_subpartitions
CREATE SHARDED TABLE Customers
( "custi_id" NUMBER NOT NULL
, name VARCHAR2(50)
, class VARCHAR2(3) NOT NULL
, signup_date DATE
,
CONSTRAINT cust_pk PRIMARY KEY("custi_id",name,signup_date,class)
)
PARTITIONSET BY LIST (class)
PARTITION BY CONSISTENT HASH ("custi_id",name)
SUBPARTITION BY RANGE (signup_date)
SUBPARTITION TEMPLATE
( SUBPARTITION per1 VALUES LESS THAN (TO_DATE('01/01/2000','DD/MM/YYYY'))
, SUBPARTITION per2 VALUES LESS THAN (TO_DATE('01/01/2010','DD/MM/YYYY'))
, SUBPARTITION per3 VALUES LESS THAN (TO_DATE('01/01/2020','DD/MM/YYYY'))
, SUBPARTITION future VALUES LESS THAN (MAXVALUE))
PARTITIONS AUTO
(
PARTITIONSET "gold" VALUES ('Gld','BRZ') TABLESPACE SET ts1 SUBPARTITIONS STORE IN(TBS1,TBS2,TBS3,TBS4)
, PARTITIONSET "silver" VALUES ('Slv','OTH') TABLESPACE SET ts2 SUBPARTITIONS STORE IN(TBS5,TBS6,TBS7,TBS8)
) ;
Limitations
Limitations for sharded tables in the current release:
-
There is no default tablespace set for sharded tables.
-
A temporary table cannot be sharded or duplicated.
-
Index-organized sharded tables are not supported.
-
A sharded table cannot contain a nested table column or an identity column.
-
A primary key constraint defined on a sharded table must contain the sharding column(s). A foreign key constraint on a column of a sharded table referencing a duplicated table column is not supported.
-
System partitioning and interval range partitioning are not supported for sharded tables. Specification of individual hash partitions is not supported for partitioning by consistent hash.
-
A column in a sharded table used in
PARTITION BY
orPARTITIONSET BY
clauses cannot be a virtual column.
Duplicated tables in the current release are not supported with the following:
-
System and reference partitioned tables
-
Non-final abstract types
-
Maximum number of columns without primary key is 999
-
The
nologging
option -
XMLType column in a duplicated table cannot be used in non-ASSM tablespace
See CREATE TABLE for more information about using the clauses supporting Oracle Sharding.
Parent topic: DDL Syntax Extensions for Oracle Sharding
ALTER TABLE
The ALTER TABLE
statement is extended to modify sharded and
duplicated tables.
There are limitations on using ALTER TABLE
with a
sharded database.
The following options are not supported for a sharded table in a system-managed or composite sharded database:
-
Rename
-
All operations on individual partitions and subpartitions
-
All partition-related operations on the shard, except
TRUNCATE
partition,UNUSABLE LOCAL INDEXES
, andREBUILD UNUSABLE LOCAL INDEXES
The following are not supported for duplicated tables:
-
Data types: Non-final abstract types
-
Column options: vector encode, invisible column, nested tables
-
Clustered table
-
External table
-
ILM policy
-
PARENT
clause -
Flashback table operation
-
System and Reference partitioning
-
Enable
NOLOGGING
option -
Drop duplicated table materialized view log
-
Drop duplicated table materialized views on shards
-
Alter materialized views (of duplicated tables) on shards
Parent topic: DDL Syntax Extensions for Oracle Sharding
ALTER SESSION
The ALTER SESSION
statement is extended to support sharded
databases.
The session-level SHARD DDL
parameter sets the scope
for DDLs issued against the shard catalog database.
ALTER SESSION { ENABLE | DISABLE } SHARD DDL;
When SHARD DDL
is enabled, all DDLs issued in the
session are executed on the shard catalog and all shards. When SHARD
DDL
is disabled, a DDL is executed only against the shard
catalog database. SHARD DDL
is enabled by default for a
sharded database user (the user that exists on all shards and the catalog).
To create a sharded database user, the SHARD DDL
parameter
must be enabled before running CREATE USER
.
Parent topic: DDL Syntax Extensions for Oracle Sharding