Code Re-Use Via Views

Much of my group works on a reporting application. In order to achieve greater SQL re-use we have tried to use views to avoid re-writing the same portions of SQL. The problems we've run into so far include: (i) union-alls in views seem to force evaluation of the view rather than merging with the larger SQL statement, (ii) transposing of certaing columns slows down a query quite a bit, (iii) making new rows appear based on joins to an inline view on dual (e.g., select 0 as flag from dual union all select 1 from dual) as flag_view) makes our views run slower.

The basic problems we're trying to solve are: (i) code re-used via views, (ii) without modifying our schema treat separate table polymorphically via views.

Does this make sense? Or does this smell of bad schema design? Intuition tells me that if we're using views to select from different tables and union-all'ing them to make them appear as one, maybe they should be refactored into one table. What strategies make sense for promoting code re-use in a database application? (Yes, we're already using stored-procedures and packages to abstract away CRUD operations). Received on Sun Jan 08 2006 - 21:19:35 CST