Overview

SELECT

• Supports most query features, including single- and multi-table queries, subqueries, inner joins, semi joins, outer joins, grouping, aggregation, hierarchical queries, and regular data mining functions such as probability and linear regression functions.

• Supports the following collection operations: UNION, UNION ALL, INTERSECT, and MINUS.

• Supports the EXPLAIN statement for viewing execution plans.

INSERT

• Supports single- and multi-row inserts, as well as insertion into a specified partition.

• Supports the INSERT INTO ... SELECT ... statement.

• Supports single- and multi-table inserts.

UPDATE

• Supports single- and multi-column updates.

• Supports updates by using subqueries.

• Supports collection updates.

DELETE

• Supports single- and multi-table deletion.

TRUNCATE

• Supports the truncation of a specified table.

Parallel queries

• Supports parallel queries similar to those in Oracle Database.

  OceanBase Database supports the auto degree of parallelism (DOP) feature. You can also manually specify the DOP by using hints or session variables.

• Supports parallel DML statements.

Hint

OceanBase Database supports hints. For more information about hints, see Hint.

Materialized views

• Supports the CREATE MATERIALIZED VIEW statement for creating a materialized view.
• Supports the DROP MATERIALIZED VIEW statement for dropping a materialized view.

Materialized view logs

• Supports the CREATE MATERIALIZED VIEW LOG statement for creating a materialized view log.

  Note

  The default behavior of with_clause (where you can specify PRIMARY KEY, ROWID, and SEQUENCE) in the CREATE MATERIALIZED VIEW LOG statement in OceanBase Database is incompatible with those in Oracle Database.

• Supports the DROP MATERIALIZED VIEW LOG statement for dropping a materialized view log.

OceanBase Database supports the following PL features of Oracle Database:

• Data types

• Process control

• Collections and records (multi-dimensional collections are not supported)

• Static SQL statements

• Dynamic SQL statements

• Subprograms

• Triggers

• Exception handling

• Packages

• Performance optimization

• Custom data types

• PL packages, including DBMS_CRYPTO, DBMS_DEBUG, DBMS_LOB, DBMS_LOCK, DBMS_METADATA, DBMS_OUTPUT, DBMS_RANDOM, DBMS_SQL, DBMS_XA, UTL_I18N, and UTL_RAW

• PL tag security packages, including SA_SYSDBA, SA_COMPONENTS, SA_LABEL_ADMIN, SA_POLICY_ADMIN, SA_USER_ADMIN, and SA_SESSION

In the implementation of NOCOPY for a parameter, compatibility between OceanBase Database and Oracle Database varies as follows:

• If the parameter is of a basic data type or the parameter has the OUT attribute and is of a complex data type, OceanBase Database is incompatible with the pass-by-reference behavior in Oracle Database.

• If the parameter has the IN OUT attribute and is of a complex data type, OceanBase Database is compatible with the pass-by-reference behavior in Oracle Database.