Чем отличается materialized view от with?

Question

[beduin01]

Вроде бы и первый и второй оператор создает временную таблицу. В чем различия?

Уточню. Интересно по двум БД. PostgresSQL и Oracle. Разница в них есть?

Comments

[Melkij]

Специфично для субд. Вы про какую спрашиваете?

Ну например, materialized view - это совсем не временная штука.
А под временной таблицей обычно имеют в виду вполне конкретный create temporary table. а не cte

Answer 1

[freeExec]

materialized view - создаёт вполне реальную структуру, где будут лежать данные на момент создания. Если в активной таблице их менять, то во вьюшке они не изменяться. Ну возможно физически они как то привязаны к версии данных из актуальной таблицы, но не суть.
with - просто синтаксический сахар для удобства написания запроса. Хотя и позволяет делать вещи не возможные при обычном написании запроса.

Comments

[beduin01]

Но With же создаст временную таблицу? Я к тому, что он нужен же для того, чтобы не выполнять один и тот же запрос много раз, а выполнить его один и дальше пользоваться результатом.

[freeExec]

beduin01, Каким образом вы его используете дальше?

[beduin01]

freeExec, дальше делаю по результатам выборки из него: WHERE main_person.inn IN (SELECT grf_inn from source_INN) где source_INN -- результат with

[freeExec]

beduin01, Но это всё ещё в рамках одного запроса. Как движок БД внутри хранит промежуточные результаты не так важно.

[Melkij]

with - просто синтаксический сахар для удобства написания запроса

опасное заблуждение.
в частности, до postgresql 12 cte материализуется всегда и не может пробрасывать условия из внешнего запроса. А потом более интересные фокусы из-за того что результат cte местами эквивалентен black box и никакой вменяемой оценки не имеет, а потому планировщик может хотеть делать странное.

[Melkij]

Bavashi, да, целью было inline частей cte в основной запрос: https://www.postgresql.org/message-id/E1gv7F7-0003...
Который затем планируется как единая штука.