All Products
Search
Document Center

Reference schema objects

Last Updated: Jun 18, 2021

When an object is referenced in an SQL statement, ApsaraDB for OceanBase considers the context of the SQL statement and locates the object in the appropriate namespace. After the object is located, ApsaraDB for OceanBase executes the SQL statement to perform the specified operation on the object. If the named object cannot be located in the appropriate namespace, an error is returned.

Reference objects in users' schemas

The following example is used to illustrate how ApsaraDB for OceanBase resolves object references in SQL statements:

Execute the following statement to add a row of data to the table named departments:

INSERT INTO departments 
VALUES (280, 'ENTERTAINMENT_CLERK', 206, 1700);

Based on the context of the SQL statement, departments may represent one of the following items in the database:

  • A table in your schema

  • A view in your schema

  • A private synonym of a table or a view

  • A public synonym

ApsaraDB for OceanBase always attempts to resolve the referenced objects in the namespaces of your schema before it considers the namespaces outside your schema. In this example, ApsaraDB for OceanBase attempts to resolve the object:

  1. Firstly, ApsaraDB for OceanBase attempts to locate the object in the namespace of your schema that contains tables, views, and private synonyms. If the object is a private synonym, ApsaraDB for OceanBase locates the object that the synonym represents. The object can be in your own schema, another schema, or another database. The object can also be another synonym. In this case, ApsaraDB for OceanBase locates the object that the synonym represents.

  2. If the object is located in the namespace, ApsaraDB for OceanBase attempts to execute the SQL statement on the object. In this example, ApsaraDB for OceanBase attempts to add a row of data to departments. If the type of the object is not the required type of the SQL statement, ApsaraDB for OceanBase returns an error. In this example, departments must be a table, view, or private synonym that can be resolved as a table or a view. If departments is a sequence, ApsaraDB for OceanBase returns an error.

  3. If the object is not found in all of the namespaces that have been searched so far, ApsaraDB for OceanBase searches the namespace that contains public synonyms. If the object is in the namespace that contains public synonyms, ApsaraDB for OceanBase attempts to execute the statement on the object. If the type of the object is not the required type of the SQL statement, ApsaraDB for OceanBase returns an error. In this example, if departments is a public synonym that represents a sequence, ApsaraDB for OceanBase returns an error.

Assume that the public synonym has dependent tables or user-defined types. In this case, you cannot create an object with the same name as the synonym in the same schema as that of the dependent object.

On the contrary, assume that the synonym does not have dependent tables or user-defined types. In this case, you can create an object with the same name as the synonym in the same schema as that of the dependent object. ApsaraDB for OceanBase invalidates all of the dependent objects, and revalidates them when they are accessed the next time.

Reference objects in other schemas

To reference objects in schemas other than your schema, add the schema name to the beginning of the object name:

schema.object

In the following example, the employees table is deleted from the schema named hr:

DROP TABLE hr.employees;