View or temporary table?
Analyzing a code problem I came across different approaches to similar problems, where one has a view for data access, and another uses a temporary table.
I went searching and found this question that deals specifically with views. I was reading the answer marked as accepted and I understood more about what the "views " are and what they are for, but in the last line a my doubt was exposed.
Good thing it didn't ask the difference to use a temporary table
(In ) fortunately this is my point!!
- What differentiates a view from a temporary table ?
- is there a "formula" for figuring out when to use one or the other?
2 answers
The jocular text there is only because there would be many subjects:)
In fact they have nothing to do, they are very different mechanisms for very different purposes, with different commitments.
View
A View , as can be seen there, is not a real table (unless it is materialized), it is just a way to query something in a simpler way, possibly already making a denormalization and offering a way of access to certain data without entering others for a user you do not want to expose all of the table (s) used in View. Data is always generated from actual existing tables and the specific result is available for this query. Each query in it can generate a different result without doing anything in it. It is available for queries at all times, until someone has it removed. Some implementations allow you to change your data by reflecting in the original table (s) (ais). The cost only exists in the normal query processing, which is virtually the same as manually doing the query entered in the View template.
For completeness, the materialized view creates the physical table, but always based on existing Table(s). It may look more like the temporary table, but it always (potentially) generates different results in each result and the table is always available to everyone with due privilege, until it is removed. There is cost of space and processing, even not using it directly. I've seen cases where it can be updated independently by being able to create scenarios, and then it goes back to its original state, but I don't know if it was made for this.
Temporary table
The temporary table is physical and only exists when you have it created. It is usually based on another table (s), but it can only be something coming from the outside (technically the views can too, but it makes less sense). If nothing is changed in it all applied query will generate the same result. To change your data, as well as in its creation, you need an explicit code that tells you to do. Usually it is available for that session, but there is a way to make it more "permanent", even if it does not make sense. It is used on demand and has cost of space and processing as needed.
As the name says it serves temporary purposes, for something you do not intend to keep in the system, and may even do something unrelated with the rest of the base, although it makes little sense. It compares a little more to the materialized vision because both are physical. But the temporary ones are for extra operations apart from the normal operation of the base, the materialized ones are optimizations to the normal access of the base.
When to use each
It is rare to need materialized view, almost always it brings too much costs to compensate.
When comparing the simple View with the temporary table it becomes easy establish where it should be used. The View does not allow you to touch it. It is a simplification and optimization mechanism and is preferable where appropriate.
Already the temporary table has more costs and implications, so it always needs more justification, you have to make sure that another solution does not solve. It should almost never be used as a View. It should be used to create new data, prepare data for use in the database, create scenarios. You do what well you understand at that moment, you can experiment without fear, she does not make Mars of her model. Treat it like a draft. Think of it as a training, a preparation for something, a simulation, something independent of the base.
It may be required in very complex queries that depend on some manipulation of the data before it is used.
The temporary table is a normal table and you can do everything with it, including creating indexes.
DBAs tend to use more that developers. It makes more sense in procedures or complex processes that only DBA usually does. Those who do not do more "normal" queries and processing make less sense, but can do, especially in very complex reports.
If your SGDB thinks it should it can create a temporary table to better handle the View , but that's up to it, something transparent to you.
The temporary table may never go to disk.
Temporary tables
Using a temporary table in MySQL, allows you to perform tests or services on a transient entity, without worrying about cleaning up the dirt afterwards. when you disconnect from the server, temporary tables are automatically discarded.
When is it useful to use temporary tables in MySQL
Some operations require that the existence of some information be short - and that it be removed, when it is no longer necessary.
The removal part can be done automatically by MySQL.
You do not need to remember to delete a table, which no longer has serventia, with the Command DROP TABLE.
For this, simply create a table, with the option TEMPORARY, like this:
CREATE TABLE TEMPORARY Nome_da_tabela
List temporary tables
SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO\G
Views
A View is an object that belongs to a database, defined based on select's statements, returning a given visualization of data from one or more tables. these objects are sometimes called" virtual tables", formed from other tables which in turn are called" based tables " or other Views. And some cases, Views are updatable and can be Declaration targetsINSERT, UPDATE e DELETE, which actually modify your " based tables ".
The benefits of using Views (in addition to those already highlighted)
A View can be used, for example, to return a value based on a record identifier;
It can be used to enforce restrictions on data to increase data security and define access policies at the table and column level. Can be configured to show different columns for different database users;
It can be used with a set of tables that can be joined to other sets of tables using JOIN’s or UNION.
List views
SELECT * FROM INFORMATION_SCHEMA.VIEWS
References and links
How to create temporary tables in MySQL