vikkee vikkee - 2 months ago 7
SQL Question

Impact: Delete a View, Create table with same name

So there was this VIEW in oracle named XYZ and somebody says, we are going to replace it with a TABLE of the same name.

What kind of an impact can this create to existing SQL's written on that view?
Is the syntax for querying a VIEW same as that for a TABLE?

Answer

By "... replace it with a table ..." I assume you mean that a table is created with the same data the view was referencing. In other words, the new table redundantly contains data from other tables (those that the view was referencing).

SELECT-Statements will not need to be changed - the syntax is the same.

BUT: The view will immediately reflect changes in underlying tables. The table obviously not - it will have to be kept in sync by triggers or application logic. So depending on the view, this might be a rather big change. Even more so if the view was updateable.

Example:

Suppose the view was defined as ... select a.key, b.name from a,b where b.key = a.b_ref

Then selecting from the view will always reflect changes to tables a and b.

If you replace it by a table, you would have to update that new table every time you update table a or b.