Back in the late 1980s when I wrote my first Teradata SQL query, direct access to physical tables was the norm.
The DBMS was relied upon to take care of any locking conflicts. Clearly we didn’t want end users or developers getting in the way of us updating the core tables.
However, the ETL jobs still had to wait until the SQL placing read locks had completed (or failed). This wasn’t so much of a problem when our data refresh cycle was quarterly (no, really!).
Roll forward to my time in support at (pre-Lloyds takeover) TSB in the mid-1990s.
The weekly ETL jobs were frequently held up by users running read-only SQL directly against core tables. Many a frantic hour was spent on a Friday afternoon trying to track down the owner and asking them nicely to kill their queries so we could actually update the database.
The solution to the read/write lock conflict was quite simple and very effective.
Instead of accessing tables directly, a set of full table views was created. These views had the same name as the underlying table and contained the same column names. No SQL changes were required.
Essentially the views contained ‘CREATE VIEW USERS.X AS SELECT * FROM PROD.X’ (in shouty upper case as a homage to IBM mainframe BTEQ scripts).
The benefit was that we could add a table locking modifier to the view. This would allow end users to access production tables via a view that contained an ACCESS (dirty read) lock rather than the default READ lock.
The benefit is that end user queries no longer forced production ETL jobs to wait in a queue behind a READ lock in order for a WRITE lock to be granted.
Controlling table locks is the first use case for views that I’ve encountered that I’m still 100% happy with. Views are all too easily abused, unfortunately.
Other use cases I support include:
- hiding/obfuscating sensitive columns
- filtering access to underlying rows
- re-ordering columns
The flip side of the coin are the view use cases I don’t support:
- embedded business logic
- derived columns
- joins
- layered views
- column renaming
- aggregations
- de-normalisations
If I had a £1 for every non-simple view I’d materialised…
For all those that disagree, let’s here from you in the comments section. You know who you are.
Use views wisely folks!