Table Management

Learn how to design tables and use Data Definition Language to work with tables.

You can create, modify, and delete NoSQL tables from your application. You can also add indexes to optimize your application common access paths. The table DDL is what you use to specify these actions.

The language driver of your choice executes your DDL statement. See Using Tables in Java for examples of how to issue your DDL statements.

Data Definition Language Reference

Learn how to use DDL in Oracle NoSQL Database Cloud Service.

Use Oracle NoSQL Database Cloud Service DDL to create, alter, and drop tables and indexes.

For information on the syntax of the DDL language, see Table Data Definition Language Guide. This guide documents the DDL language as supported by the on-premises Oracle NoSQL Database product. The Oracle NoSQL Database Cloud Service supports a subset of this functionality and the differences are documented in the DDL Differences in the Cloud section.

Also, each NoSQL <language> driver provides an API to execute a DDL statement. To write your application, see Using Tables in Java.

Typical DDL Statements

Few samples of common DDL statements are as follows:

Create Table
CREATE TABLE [IF NOT EXISTS] (
    field-definition, field-definition-2 ...,
    PRIMARY KEY (field-name, field-name-2...),
) [USING TTL ttl]
For example:
CREATE TABLE IF NOT EXISTS audience_info (
    cookie_id LONG,
    ipaddr STRING,
    audience_segment JSON,
    PRIMARY KEY(cookie_id));
Alter Table
ALTER TABLE table-name (ADD field-definition)
ALTER TABLE table-name (DROP field-name)
ALTER TABLE table-name USING TTL ttl 
For example:
ALTER TABLE audience_info USING TTL 7 days;
Create Index
CREATE INDEX [IF NOT EXISTS] index-name ON table-name (path_list)
For example:
CREATE INDEX segmentIdx ON audience_info
       (audience_segment.sports_lover AS STRING)
Drop Table
DROP TABLE [IF EXISTS] table-name
For example:
DROP TABLE audience_info;

See the reference guides for a complete list:

DDL Differences in the Cloud

The cloud service DDL language differs from what is described in the reference guide in the following way:

Table Names

  • Limited to 256 characters, and are restricted to alphanumeric characters and underscore
  • Must start with a letter
  • Cannot include special characters
  • Child tables are not supported

Unsupported Concepts

  • DESCRIBE and SHOW TABLE statements.
  • Full text indexes
  • User and role management
  • On-premise regions