Oracle query synonyms

Synonyms in Oracle can be used to create another access method for tables and views, which can improve the efficiency of query and management.

Synonym is an object in Oracle database, which can be regarded as providing a simplified alias for another object. In the query, using synonyms can improve the readability and maintainability of the query without writing long table names or view names.

The following are the detailed steps on how to create, modify and use synonyms:

Creating Synonyms You can use the CREATE SYNONYM statement to create synonyms. The syntax is as follows:

CREATE [PUBLIC] SYNONYM [schema_name.]synonym_name FOR [schema_name.]object_name[@db_link];

Among them, schema_name stands for schema name, synonym_name stands for synonym name, and object_name stands for the names of objects such as actual tables and views. If you need to access objects in other databases, you can do so through @db_link syntax.

For example, to create a synonym (EMPLOYEE) to access the EMP table, you can use the following statement:

CREATE SYNONYM EMPLOYEE FOR HR.EMP;

Modify synonyms Use the ALTER SYNONYM statement to modify the definition of synonyms. The syntax is as follows:

ALTER [PUBLIC] SYNONYM [schema_name.]synonym_name RENAME TO new_synonym_name;

For example, to rename the EMPLOYEE synonym to STAFF, you can use the following statement:

ALTER SYNONYM EMPLOYEE RENAME TO STAFF;

Deleting Synonyms Use the DROP SYNONYM statement to delete synonyms. The syntax is as follows:

DROP [PUBLIC] SYNONYM [schema_name.]synonym_name;

For example, to delete the STAFF synonym, you can use the following statement:

DROP SYNONYM STAFF;

Using Synonyms To use synonyms in a query, you only need to use synonyms at the table name or view name. For example, when querying EMP tables, you can use the following two methods:

SELECT * FROM HR.EMP;

or

SELECT * FROM EMPLOYEE;

The same effect can be achieved. When using synonyms, Oracle will automatically replace synonyms with the actual names of objects, so that the query engine can accurately obtain the required data.

summary

Synonym is a very useful Oracle object, which can improve the efficiency of database management and query. Creating, modifying and deleting synonyms are very simple operations. When using synonyms, we only need to use synonyms in the query, without considering the actual table name or view name, which improves the readability and maintainability of the query.