Oracle NoSQL Database Cloud Service supports many common data types.

Learn about the different table states and their significance (table life cycle process).

Each table passes through a series of different states from table creation to deletion (drop). For example, a table in the DROPPING state cannot proceed to the ACTIVE state, while a table in the ACTIVE state can change to the UPDATING state. You can track the different table states by monitoring the table life cycle. This section describes the various table states.

If you still get an error after eliminating some of the possible situations as discussed above, you can use Cloud Shell to run your query and capture the exact error as shown in the example below.

Example : Getting the error message for a SELECT statement from the cloud shell

This will give you the exact error in your SQL statement.

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 APIs to Create Tables and Indexes in Oracle NoSQL Database Cloud Service .

CREATE TABLE [IF NOT EXISTS] (
    field-definition, field-definition-2 ...,
    PRIMARY KEY (field-name, field-name-2...),
) [USING TTL ttl]

CREATE TABLE IF NOT EXISTS audience_info (
    cookie_id LONG,
    ipaddr STRING,
    audience_segment JSON,
    PRIMARY KEY(cookie_id))

ALTER TABLE table-name (ADD field-definition)
ALTER TABLE table-name (DROP field-name)
ALTER TABLE table-name USING TTL ttl 

ALTER TABLE audience_info USING TTL 7 days

CREATE INDEX [IF NOT EXISTS] index-name ON table-name (path_list)

CREATE INDEX segmentIdx ON audience_info
       (audience_segment.sports_lover AS STRING)

DROP TABLE [IF EXISTS] table-name

DROP TABLE audience_info

Learn how to use SQL statements to update and query data in Oracle NoSQL Database Cloud Service.

The Oracle NoSQL Database uses the SQL query language to update and query data in NoSQL tables. See SQL Reference for Oracle NoSQL Database to learn the query language syntax.

SELECT <expression>
FROM <table name>
[WHERE <expression>]
[GROUP BY <expression>]
[ORDER BY <expression> [<sort order>]]
[LIMIT <number>]
[OFFSET <number>]; 

For example:
SELECT * FROM Users;
SELECT id, firstname, lastname FROM Users WHERE firstname = "Taylor";

UPDATE <table_name> [AS <table_alias>]
    <update_clause>[, <update_clause>]*
WHERE <expr>[<returning_clause>];

For example:
UPDATE JSONPersons $j
  SET TTL 1 DAYS
  WHERE id = 6
  RETURNING remaining_days($j) AS Expires;

Each NoSQL Database driver provides an API to execute a query statement.

A query execution plan is the sequence of operations Oracle NoSQL Database performs to run a query.

A query execution plan is a tree of plan iterators. Each kind of iterator evaluates a different kind of expression that may appear in a query. In general, the choice of index and the kind of associated index predicates can have a drastic effect on query performance. As a result, you as a user often want to see what index is used by a query and what predicates have been pushed down to it. Based on this information, you may want to force the use of a different index via index hints. This information is contained in the query execution plan. . All Oracle NoSQL drivers provide APIs to display the execution plan of a query.

Some of the most common and important iterators used in queries are :

SELECT iterator: It is responsible for executing the SELECT expression.

"iterator kind" : "SELECT",
"FROM" :
  {
  },
"FROM variable" : "...",
"SELECT expressions" : 
[
  {
  }
]

The SELECT iterator has fields like: “FROM”, "WHERE", “FROM variable”, and “SELECT expressions”. “FROM” and “FROM variable” represent the FROM clause of the SELECT expression, WHERE represents the filter clause, and “SELECT expression” represents the SELECT clause.

In general, the RECEIVE iterator acts as a query coordinator. It sends its subplan to appropriate RNs for execution and collects the results. It may perform additional operations such as sorting and duplicate elimination and propagates the results to its ancestor iterators (if any) for further processing.

Distribution kinds :

A distribution kind specifies how the query will be distributed for execution across the RNs participating in an Oracle NoSQL database (a store). The distribution kind is a property of the RECEIVE iterator.

Anatomy of a query execution plan:

Query execution takes place in batches. When a query subplan is sent to a partition or shard for execution, it will execute there until a batch limit is reached. The batch limit is a number of read units consumed locally by the query. The default is 2000 read units (about 2MB of data), and it can only be decreased via a query-level option.

When the batch limit is reached, any local results that were produced are sent back to the RECEIVE iterator for further processing along with a boolean flag that says whether more local results may be available. If the flag is true, the reply includes resume information. If the RECEIVE iterator decides to resend the query to the same partition/shard, it will include this resume information in its request, so that the query execution will restart at the point where it stopped during the previous batch. This is because no query state is maintained at the RN after a batch finishes. The next batch for the same partition/shard may take place at the same RN as the previous batch or at a different RN that also stores the same partition/shard.