Neoview SQL Reference Manual (R2.5)

CREATE SYNONYM Statement
• “Syntax Description of CREATE SYNONYM ”
• “Considerations for CREATE SYNONYM”
• “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. See “Synonyms” (page 309).
CREATE SYNONYM alternate-name FOR object
Syntax Description of CREATE SYNONYM
alternate-name
specifies the name of the synonym. See “Database Object Names” (page 242).
object
specifies Neoview SQL tables, views, and materialized views can be specified. See “Database
Object Names” (page 242).
Considerations for CREATE SYNONYM
• The object owner, the schema owner, and anyone with the CREATE privilege for the schema
can create, alter, or drop the alternate names for a table. You can create a synonym for the
object if you have the CREATE privilege, but you still need to have privileges on the
underlying object in order to reference information.
• 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 except
GRANT/REVOKE and utility requests.
• When a synonym for an object is changed, any currently executing plans fail and requires
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, materialized views, 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.
Example of CREATE SYNONYM
• This example creates a synonym:
CREATE SYNONYM aname FOR s-table
CREATE SYNONYM Statement 71