Ну я это и написал, что надо переписывать. А мысль была в том, что SQL сам мог бы компилировать циклы в запросы. Ну теоретически в идеальном мире. Мы, к примеру, это реализовали во всяком случае.
Это очевидная неточность. Материализация представление поддерживается всегда, но не всегда поддерживаются те или иные опциональные возможности, как например FAST REFRESH.
Ну под материализацией я имел ввиду естественно обновляемую материализацию. Не обновляемая нужна только для задач аналитики. Но тут вопрос чья терминология первична (вон в MS SQL они вообще индексированными называются), если считать SQL(а точнее Oracle) первичной то да, может написать online или что-то типа того.
если вы подумаете и сами попробуете проанализировать как можно реализовать инкрементальные обновления, то поймете, что список ограничений абсолютно адекватен текущей сложности SQL.
Я не то что подумал, я учавствовал в реализации этого функционала, причем работающего на OLTPых террабайтных базах систем класса ERP.
Сюрприз?! При создании мвью вы сами выбираете «on commit», так что должны знать, что это происходит при коммите.
Я заметил, и почему это не удобно написал в статье.
Что?! На таком серьезном ресурсе как хабр и упоминать бурлесоновщину?!
Тут согласен, надо тщательнее изучить список экспертов. Не поделитесь ссылкой?
В Oracle можно использовать контексты, функции, можно создать FGAC и тд и тп.
Если вы про контексты, о которых я думаю, то это адский ад. То что в Oracle есть аналог table inlined функций возможно, я и не утверждал обратное. Причем тут FGAC не совсем понял.
в оракле тоже есть и, кроме того, туда спокойно можно передавать параметры из других таблиц
То есть запрос который я привел в Oracle заработает? А можно пример? Тогда дополню статью.
Работает, просто оконные функции вы не умеете готовить:
вы указываете «row_number() OVER (PARTITION BY shipment ORDER BY id)» — то есть сами инструктируете СУБД сначала посчитать ROW_NUMBER для всего дата сета из таблицы, а фильтруете на другом уровне, после этого подсчета. Если СУБД сначала отфильтровала по вашему предикату, то результат ROW_NUMBER был бы неверным.
Я там внизу привел пример как надо было делать JPPD (и как мы его делаем).
что вы тут хотели и почему запросы не эквивалентны? Вообще во всех указанных СУБД есть LATERALS/CROSS APPLY, которые легко решают проблемы с JPPD
Не уверен что CROSS APPLY сработают с оконными функциями, но это и не важно. Вопрос, что Oracle сам этого не делает. По такой логике зачем вообще оптимизатор нужен, если можно все самому сделать. Собственно я для этого и начал с представлений, так как именно с ними задача оптимизаций запросов особенно актуальна.
кстати, советую прочитать про Oracle vs ANSI синтаксис, и пре- и пост- предикаты в отличной книге «The Power of Oracle SQL» habr.com/en/post/461971
И какое отношение это имеет к тому что я написал?
Оракл может построить хороший план:
Я там 100 раз все пересчитывал. Но в любом случае там вроде COUNT STOPKEY должен быть, а не FILTER(COUNT(*)>0). Но тут не уверен. Вы можете скриптом который вначале статьи сгенерить базу (он минут 10 выполняется максимум) и выполнить запрос.
Зачем делать материализованное представление, чтоб потом упереться в его ограничения. Материализованное представление — самый негибкий способ поддержки синхронности исходных и вычисленных данных, используется в простейших случаях. В приближенных к жизни сценариях синхронизация делается отдельным кодом, не обязательно синхронно с изменениями исходной таблицы.
Так, а как поддерживать остатки в таком случае? Ну и разработчик может изначально не знать, нужно ли материализовывать данное представление или нет.
Какие-то странные выводы про неудобства табличных функций в cross apply — вполне можно и параметры функции использовать, и далее во where накладывать условия — inline-функция раскроется в запросе, и все условия сработают.
Это как? Можно пример?
Проблема N+1 в данном случае высосана из пальца. Вопрос привычки и опыта.
Так а что делать если у вас написана хранимка, и ее надо для 1000 записей вызвать?
Триггеры уровня оператора в postgreSQL вполне себе поддерживают получение старых и измененных данных — иначе, действительно, зачем они нужны, этакий чемодан без ручки.
Data type RECORD; variable holding the new database row for INSERT/UPDATE operations in row-level triggers. This variable is null in statement-level triggers and for DELETE operations.
OLD
Data type RECORD; variable holding the old database row for UPDATE/DELETE operations in row-level triggers. This variable is null in statement-level triggers and for INSERT operations.
Есть transition tables, но это как я понял не совсем то. Во всяком случае примеры там какие то совсем странные.
Ну как пишут Oracle это известная штука. Гулял с год назад соответствующий пост.
Собственно, я когда тестировал матпредставления, если честно так и не смог их заставить работать с UPDATE при FAST REFRESH ON COMMIT. При INSERT обновляются, а при UPDATE нет. Но я не стал уже это в статье писать, уверен что где-то есть секретное знание, как это нужно делать. Просто мне не удалось его найти.
Ну и вообще если погуглить, про проблемы с производительностью FAST REFRESH матпредставлений, там такие веселые костыли выплывают, но я с Oracle в какой-то момент уже перестал чему-то удивляться.
Это да. Я сам 100 раз перечитывал документацию не веря, что таких функций нет. Но это видимо потому что в MS SQL есть оптимизация SELECT MAX, и предполагается что именно его надо использовать. Но там совсем жесткие запросы получаются.
Кстати в Posgres эти агрегатные функции не очень сложно создаются:
CREATE OR REPLACE FUNCTION last_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE sql IMMUTABLE AS $$
SELECT $2;
$$;
DROP AGGREGATE IF EXISTS last(anyelement) CASCADE;
CREATE AGGREGATE last (
sfunc = last_agg,
basetype = anyelement,
stype = anyelement
);
Но это потому что в PostgreSQL в custom aggregate функциях ORDER clause поддерживают.
В MS SQL бодаться с созданием таких функцией куда больше. Там и ORDER'а нет и их на C# надо писать.
Вообще чаще всего, если представления простые, обычно проблемы со статистикой. Тот же Oracle очень чувствителен к ней (и к примеру при добавлении индекса, не всегда пересчитывает ее по этому индексу, это вообще крышу сносит). Но да если у вас там внутри функции от колонок, громоздкие Or'ы, аналитические функции и хоть что-то нестандартное, готовьтесь к удару по iowait вашего сервера.
И ещё важная проблема — не все базы хорошо переводятся на «кластерный» способ работы, как в NoSQL.
Кстати это модный тренд сейчас — Distributed SQL с ACID. Google Spanner, Yugabyte'ы всякие. Но они к сожалению даже window функций вроде не поддерживают, не то что рекурсивных CTE. Но надеюсь это пока.
Спасибо. Да вы правы, наверное можно было бы так разделить, но это имело бы смысл, если бы какие-то из этих трех вещей могли использоваться друг без друга.
На практике же дело приходится иметь с ними всеми вместе взятыми + на самом деле обертками еще из логики представлений, ORM. То есть и так общей картины у большинства людей нет, а если еще раздробить информацию, то совсем какой-то информационный шум получится.
Я слабо представляю, как при этом надо будет переписать balance. А еще непонятнее как это все будет выполняться, учитывая что СУБД все эти OR'ы и IS NULL / IS NOT NULL не очень то любят (только в этой статье два примера есть).
Спасибо, что-то про CROSS APPLY с LATERAL я забыл написать, сейчас дополню статью. Хотя с CROSS APPLY все равно проблемы будут, потому как если вы сделаете функцию от трех параметров, как тогда скажем вернуть остатки на дату по всем товарам / складам? То есть:
SELECT FROM balance('01.01.2019')
Получается что это уже не виртуальная таблица (представление), а именно функция будет.
В остальном согласен условия соединения в WHERE на сложных запросах — это ад.
Я этот пример, если честно в гугле нашел.
Да, уже подсказали выше. Спасибо. Сейчас подправлю статью.
Ну под материализацией я имел ввиду естественно обновляемую материализацию. Не обновляемая нужна только для задач аналитики. Но тут вопрос чья терминология первична (вон в MS SQL они вообще индексированными называются), если считать SQL(а точнее Oracle) первичной то да, может написать online или что-то типа того.
Я не то что подумал, я учавствовал в реализации этого функционала, причем работающего на OLTPых террабайтных базах систем класса ERP.
Я заметил, и почему это не удобно написал в статье.
Тут согласен, надо тщательнее изучить список экспертов. Не поделитесь ссылкой?
Если вы про контексты, о которых я думаю, то это адский ад. То что в Oracle есть аналог table inlined функций возможно, я и не утверждал обратное. Причем тут FGAC не совсем понял.
То есть запрос который я привел в Oracle заработает? А можно пример? Тогда дополню статью.
Я там внизу привел пример как надо было делать JPPD (и как мы его делаем).
Не уверен что CROSS APPLY сработают с оконными функциями, но это и не важно. Вопрос, что Oracle сам этого не делает. По такой логике зачем вообще оптимизатор нужен, если можно все самому сделать. Собственно я для этого и начал с представлений, так как именно с ними задача оптимизаций запросов особенно актуальна.
И какое отношение это имеет к тому что я написал?
Я там 100 раз все пересчитывал. Но в любом случае там вроде COUNT STOPKEY должен быть, а не FILTER(COUNT(*)>0). Но тут не уверен. Вы можете скриптом который вначале статьи сгенерить базу (он минут 10 выполняется максимум) и выполнить запрос.
Так, а как поддерживать остатки в таком случае? Ну и разработчик может изначально не знать, нужно ли материализовывать данное представление или нет.
Это как? Можно пример?
Так а что делать если у вас написана хранимка, и ее надо для 1000 записей вызвать?
Тут вопрос есть на stackoverflow.
Есть transition tables, но это как я понял не совсем то. Во всяком случае примеры там какие то совсем странные.
Собственно, я когда тестировал матпредставления, если честно так и не смог их заставить работать с UPDATE при FAST REFRESH ON COMMIT. При INSERT обновляются, а при UPDATE нет. Но я не стал уже это в статье писать, уверен что где-то есть секретное знание, как это нужно делать. Просто мне не удалось его найти.
Ну и вообще если погуглить, про проблемы с производительностью FAST REFRESH матпредставлений, там такие веселые костыли выплывают, но я с Oracle в какой-то момент уже перестал чему-то удивляться.
SELECT LAST(id ORDER BY date,id) FROM shipment
В MSSQL ЕМНИП WITHIN GROUP есть но у конкретных агрегирующих функций. У custom aggregate нет. Во всяком случае лет 5 назад вроде так было.
Про FIRST_VALUE и LAST_VALUE уже ответили. Это оконные функции.
Кстати в Posgres эти агрегатные функции не очень сложно создаются:
Но это потому что в PostgreSQL в custom aggregate функциях ORDER clause поддерживают.
В MS SQL бодаться с созданием таких функцией куда больше. Там и ORDER'а нет и их на C# надо писать.
Что там в Oracle если честно не помню.
Ну это чтобы не было комментариев: «в последней версии это скорее всего исправили» :) Просто у Oracle 19c у MS SQL тоже хотелось 19.
Про остальное сверху вроде ответили.
Кстати это модный тренд сейчас — Distributed SQL с ACID. Google Spanner, Yugabyte'ы всякие. Но они к сожалению даже window функций вроде не поддерживают, не то что рекурсивных CTE. Но надеюсь это пока.
На практике же дело приходится иметь с ними всеми вместе взятыми + на самом деле обертками еще из логики представлений, ORM. То есть и так общей картины у большинства людей нет, а если еще раздробить информацию, то совсем какой-то информационный шум получится.
Получается что это уже не виртуальная таблица (представление), а именно функция будет.