It differs from a materialized view with?

It seems to be the first and the second statement creates a temporary table. What are the differences?

To clarify. Interested in two BD. PostgreSQL and Oracle. The difference in them is there?
April 7th 20 at 11:07
2 answers
April 7th 20 at 11:09
Solution
materialized view - creates a very real structure, which will be based on data at the time of creation. If the active table to change them in the view they will not change. Well, maybe physically they are tied to the version of the data from the actual tables, but not the essence.
with - just syntactic sugar for ease of writing the query. Although and allows you to do things not possible in the normal write request.
But With will create a temporary table? I mean that he needs in order not to execute the same query many times, but to do it alone and continue to use the result. - Selena commented on April 7th 20 at 11:12
@Devon_FahHow you use it on? - abraham.Sta commented on April 7th 20 at 11:15
@Eldridge9, then do the selection results from it: WHERE main_person.inn IN (SELECT grf_inn from source_INN) where source_INN-the result with - Selena commented on April 7th 20 at 11:18
@Devon_Fah, But it's still within the same request. As the DB engine inside stores intermediate results are not so important. - abraham.Sta commented on April 7th 20 at 11:21
with - just syntactic sugar for ease of writing the query

dangerous delusion.
in particular, postgresql cte to 12 materializes always and can not push the conditions from the outer query. And then more interesting tricks due to the fact that the result of cte places an equivalent black box and no sane assessment has, but because the scheduler may want to make things weird. - Osborne_Ernse commented on April 7th 20 at 11:24
@Elizabeth_Kerlu, I think that with so as partly introduced not materialized in ps12 to predict the behavior of the scheduler. - Fay_Romaguera commented on April 7th 20 at 11:27
@chyna57, Yes, the goal was to inline parts of the cte in your main query: https://www.postgresql.org/message-id/E1gv7F7-0003...
Which is then scheduled as a single thing. - Osborne_Ernse commented on April 7th 20 at 11:30
April 7th 20 at 11:11
If it is very simplistic for PostgreSQL:
  • materialized view - the query result in a table, which can be accessed several times and which you can refresh using the refresh materialized.
  • with - it as temporary table, but exists only for a single request, not for session as a whole.

Read more here and here.
Thanks, and temporary tables then what is needed? - Selena commented on April 7th 20 at 11:14
@Devon_Fah, if you're talking about with that just to run the query. If in General about temporary tables, it is another structure. It is convenient to put some intermediate data, to use them in the future. And after use they are automatically deleted after the session. - Fay_Romaguera commented on April 7th 20 at 11:17

Find more questions by tags SQL