DEV Community

Discussion on: Database Views Don't Really Exist

Collapse
 
geraldew profile image
geraldew

A big issue with views is how they get treated by various parts of the data engine as well as some other tools.

On larger platforms it seems common for query optimisers to see right through the views, which can have some interesting effects.

As a benefit, it can (should?) mean that columns defined in a view that don't get used anywhere will not be implemented at any stage of the query plan. Indeed, for systems that let you see the query plan, it's common to not see the view mentioned at all, only the tables it called upon. This allows for the definition of views that provide a great many variations of column usage, with it being very unlikely in practice that many will ever be used at the same time. At run time, all the unused ones simply never come into existence.

While that sounds good, there can be a catch with view transparency. Because if metadata capture is done from the query plans then you can find your lineage tool also never shows the views either. When you were expecting the metadata lineage to show you which scripts or processes were using various views, that invisibility has now become a maintenance liability. FWIW this is why I prefer my lineage analysis to be based on the SQL rather than the inside knowledge of the query plan - but as I had to write my own to achieve that, I probably would say that.