Encapsulate table with view

Question

I need to change the order of columns in my table (Oracle and SQL Server). Reason is that some of our software calls select * and insert without specifying columns. Refactoring will be too expensive so I need to solve it on the database level.

My idea is to create a new view which will encapsulate the table and just change the order of columns. Current table will be renamed and new view will have the original name. I found here that encapsulating a table with a view is some kind of database design pattern.

Here are my questions:

• Do I lose performance when I encapsulate a table with a view and call the view only?
• Do you see any risk with naming the view (there will not be V_ prefix)?
• Do you see any other advantage / disadvantage in encapsulating a table with a view?

Answer 1

Well,

• A view increases the overhead but if the view only contains a select with a different column ordering, the overhead will not be noticeable.
• You can also rebuild the table with the column order you want (a rather expensive one time operation)
• Most people agrees that views should follow the same naming standard as tables (i.e. no V_, or any other, special prefix)

Having said that, software should almost never do selects or inserts, without specifying the columns.