Neoview SQL Reference Manual (R2.2)

CREATE SYNONYM Statement
• “Syntax Description of CREATE SYNONYM ”
• “Considerations”
• “Versioning Considerations”
• “Example of CREATE SYNONYM”
The CREATE SYNONYM statement allows you to create synonyms (use an alternate name) for
tables, views, or materialized views that can be used in queries. You can write queries that
reference the synonym and later alter the synonym to point to a different object with the same
structure. The advantage of this is to change the object being referenced without changing the
query.
When a synonym is assigned to an object, the object can be referenced either with the synonym
name or the actual name. The resultant query results will be the same.
Syntax Description of CREATE SYNONYM
CREATE SYNONYM alternate-name FOR object;
alternate-name
specifies the name of the synonym. See “Database Object Names” (page 196).
object
specifies Neoview SQL tables, views, and materialized views can be specified. See “Database
Object Names” (page 196).
Considerations
• When the object being referenced by the synonym is dropped, the synonym is also dropped.
• Only the owner of the schema or the services ID can create, alter, or drop the alternate names
for a table.
• You can create an unlimited number of synonyms for a single object.
• Grant and Revoke commands are allowed on synonyms. The command will be applied to
the actual reference object.
• A synonym is silently mapped to the underlying referenced object for DML operations and
UPDATE STATISTICS command. This mapping does not occur for DDL and utility requests.
• When a synonym for an object is changed, any currently executing plans fail and required
name resolution and similarity check. If the similarity check fails, existing plans referencing
the new object associated with the synonym name require compilation or recompilation.
• View, constraints, and trigger text cannot use synonym names in their DML text.
• Synonyms cannot be renamed. The RENAME operation is allowed on actual table names
only.
Versioning Considerations
Synonyms are only available in schema version 2000. If you need to downgrade your schema to
version 1200, all synonyms must be dropped.
Example of CREATE SYNONYM
• This example creates a synonym:
CREATE SYNONYM aname FOR s-table
68 SQL Statements