No replies
11 Posts

Pinned topic Q&A - Using an ALIAS within a view

‏2013-03-01T03:29:38Z |
I get a lot of questions and thought I would periodically post the questions and my responses here, for the possible benefit of others.
Scott Forstie

When we use an alias in a view the view ends up being hard bound to the alias target. When the alias target changes, the view continues to refer to the original alias target. However, if we avoid using the view and simply have a query within our procedure, the query always finds the current alias target, which is the behavior we want. Why do views behave in this manner?

The simple explanation is that for views and logical files, object references are hard bound at build time. The technical reason for this hard binding at build time is to enable the database to be the most efficient and secure.

Avenues to consider:
1) Anytime the alias target changes, recreate the views. This is probably not an ideal choice, but CREATE OR REPLACE ALIAS and CREATE OR REPLACE VIEW could be used.
2) Reverse the dependency tree from VIEW ---> ALIAS ---> TABLE to ALIAS ---> VIEW ---> TABLE. This would imply creating multiple views and using the alias to seamlessly switch. This will work well, if you can tolerate the additional views.
3) Avoid the view altogether by having a query that references the alias similar to what might have been placed within the view definition. The query will react to any changes in the target of the alias.

The SQL Reference includes the following:

The effect of using an alias in an SQL statement is similar to that of text substitution. The alias, which must be defined before the SQL statement is executed, is replaced at statement preparation time by the qualified base table, partition of a table, view, or database file member name. For example, if PBIRD.SALES is an alias for DSPN014.DIST4_SALES_148, then at statement run time:

effectively becomes
The effect of dropping an alias and recreating it to refer to another table depends on the statement that references the alias.

> SQL Data or SQL Data Change statements that refer to that alias will be implicitly rebound when they are next run.
> Indexes that reference the alias are not affected.
> Materialized query tables or views that reference the alias are not affected.

Related articles: