Почему не SQL?



    Без преувеличения можно сказать, что SQL — один из самых распространенных в мире языков. Информационные системы могут быть написаны на Java, Python, JavaScript, C#, PHP и десятке других языков, но SQL база в том или ином виде будет в абсолютном большинстве таких систем. Среди бизнес-приложений процент систем, использующих SQL, вообще стремится к 100%.

    При этом большинство существующих материалов о SQL на хабре и других ресурсах сводятся к простым вопросам, вроде: «какие типы соединений бывают», «чем левое соединение отличается от правого», «что такое триггеры» и так далее. Более того, в этих материалах практически ничего не говорится о проблемах SQL (и его реализациях), которых на самом деле очень и очень немало. Поэтому мы решили восполнить оба этих пробела: с одной стороны рассказать, как многие вещи в SQL работают изнутри, а с другой стороны — почему они работают не так как нужно / хотелось бы.

    При этом речь в статье пойдет не о «вкусах и цветах фломастеров». Все затрагиваемые проблемы носят фундаментальный характер: присутствуют при разработке практически любой информационной системы и не ограничиваются «красотой кода», а в той или иной степени приводят либо к критическому падению производительности, либо к существенному росту порога вхождения, либо к значительным трудозатратам со стороны разработчика.

    Статья получилась достаточно объемной, и далеко не все осилят ее за один раз. Поэтому, чтобы было удобнее в ней ориентироваться, а также иметь возможность оценить «масштабы бедствия», начнем с оглавления со списком всех затрагиваемых проблем:


    Тестироваться все будет на трех РСУБД — PostgreSQL, MS SQL Server и Oracle. Все СУБД последних версий — 11, 2019 и 19c соответственно.

    Перед тем как начать, создадим базу с товарами, контрагентами и документами приходов и отгрузок. Наполним ее данными, причем большим количеством, так как дальше будет много примеров с демонстрацией проблем оптимизации запросов / производительности на больших объемах данных.

    Скрипт заполнения базы в Oracle
    CREATE TABLE customer (
        id INT PRIMARY KEY,
        name VARCHAR(100)
    );
    
    CREATE TABLE stock (
        id INT PRIMARY KEY,
        name VARCHAR(100)
    );
    
    CREATE TABLE product (
        id INT PRIMARY KEY,
        name VARCHAR(100),
        "group" INT
    );
    CREATE INDEX product_group ON product ("group");
    
    CREATE TABLE receipt (
        id INT PRIMARY KEY,
        customer INT NOT NULL,
       	"date" DATE NOT NULL,
        CONSTRAINT receipt_customer_fk
            FOREIGN KEY (customer) REFERENCES customer (id),
       	stock INT NOT NULL,
        CONSTRAINT receipt_stock_fk
            FOREIGN KEY (stock) REFERENCES stock (id),
        data VARCHAR(400)
    );
    CREATE INDEX receipt_customer_fk ON receipt (customer);
    CREATE INDEX receipt_stock_fk ON receipt (stock);
    
    CREATE TABLE shipment (
        id INT PRIMARY KEY,
        customer INT NOT NULL,
       	"date" DATE NOT NULL,
        CONSTRAINT shipment_customer_fk
            FOREIGN KEY (customer) REFERENCES customer (id),
        stock INT NOT NULL,
        CONSTRAINT shipment_stock_fk
            FOREIGN KEY (stock) REFERENCES stock (id),
        data VARCHAR(400)
    );
    CREATE INDEX shipment_customer_fk ON shipment (customer);
    CREATE INDEX shipment_stock_fk ON shipment (stock);
    CREATE TABLE receiptdetail (
        id INT PRIMARY KEY,
        receipt INT NOT NULL,
    	product INT NOT NULL,
    	quantity NUMERIC(16,3),
        CONSTRAINT receiptdetail_receipt_fk
            FOREIGN KEY (receipt) REFERENCES receipt (id),
        CONSTRAINT receiptdetail_product_fk
        	FOREIGN KEY (product) REFERENCES product (id),
        data VARCHAR(400)
    );
    CREATE INDEX receiptdetail_receipt_fk ON receiptdetail (receipt);
    CREATE INDEX receiptdetail_product_fk ON receiptdetail (product);
    
    CREATE TABLE shipmentdetail (
        id INT PRIMARY KEY,
        shipment INT NOT NULL,
    	product INT NOT NULL,
    	quantity NUMERIC(16,3),
        CONSTRAINT shipmentdetail_shipment_fk
            FOREIGN KEY (shipment) REFERENCES shipment (id),
        CONSTRAINT shipmentdetail_product_fk
        	FOREIGN KEY (product) REFERENCES product (id),
        sd INT,
        data VARCHAR(400)
    );
    CREATE INDEX shipmentdetail_shipment_fk ON shipmentdetail (shipment);
    CREATE INDEX shipmentdetail_product_fk ON shipmentdetail (product);
    CREATE INDEX shipmentdetail_sd ON shipmentdetail (sd);
    CREATE INDEX shipmentdetail_p_s ON shipmentdetail (product,shipment);
    
    INSERT INTO stock (id, name)
    WITH
    numbers ( n ) AS (
       SELECT 1 AS n FROM dual
       UNION ALL
       SELECT n + 1 AS n
       FROM   numbers
       WHERE  n <= 10
    )
    SELECT n, 'Stock '||n FROM numbers;
    
    INSERT INTO customer (id, name)
    WITH
    numbers ( n ) AS (
       SELECT 1 AS n FROM dual
       UNION ALL
       SELECT n + 1 AS n
       FROM   numbers
       WHERE  n <= 1000
    )
    SELECT n, 'Customer '||n FROM numbers;
    
    INSERT INTO product (id, name, "group")
    WITH
    numbers ( n ) AS (
       SELECT 1 AS n FROM dual
       UNION ALL
       SELECT n + 1 AS n
       FROM   numbers
       WHERE  n <= 50000
    )
    SELECT n, 'Product '||n, round(dbms_random.value() * 1000) + 1 FROM numbers;
    
    INSERT INTO receipt (id, customer, stock, "date", data)
    WITH
    numbers ( n ) AS (
       SELECT 1 AS n FROM dual
       UNION ALL
       SELECT n + 1 AS n
       FROM   numbers
       WHERE  n <= 10000
    )
    SELECT n, round(dbms_random.value() * 1000) + 1, round(dbms_random.value() * 10) + 1, CURRENT_DATE - round(dbms_random.value() * 300), 'data'||n FROM numbers;
    
    INSERT INTO receiptdetail (id, receipt, product, quantity, data)
    WITH
    numbers ( n ) AS (
       SELECT 1 AS n FROM dual
       UNION ALL
       SELECT n + 1 AS n
       FROM   numbers
       WHERE  n <= 1000000
    )
    SELECT n, round(dbms_random.value() * 10000) + 1, round(dbms_random.value() * 50000) + 1, round(dbms_random.value() * 100) + 1, 'data'||n FROM numbers;
    
    INSERT INTO shipment (id, customer, stock, "date", data)
    WITH
    numbers ( n ) AS (
       SELECT 1 AS n FROM dual
       UNION ALL
       SELECT n + 1 AS n
       FROM   numbers
       WHERE  n <= 100000
    )
    SELECT n, round(dbms_random.value() * 1000) + 1, round(dbms_random.value() * 10) + 1, CURRENT_DATE - round(dbms_random.value() * 300), 'data'||n FROM numbers;
    
    INSERT INTO shipmentdetail (id, shipment, product, quantity, data, sd)
    WITH
    numbers ( n ) AS (
       SELECT 1 AS n FROM dual
       UNION ALL
       SELECT n + 1 AS n
       FROM   numbers
       WHERE  n <= 10000000
    )
    SELECT n, round(dbms_random.value() * 100000) + 1, round(dbms_random.value() * 50000) + 1, round(dbms_random.value() * 10) + 1, 'data'||n, CASE WHEN REMAINDER(n, 100000) = 0 THEN n+1 ELSE NULL END FROM numbers;
    
    exec DBMS_STATS.GATHER_DATABASE_STATS;
    


    Скрипт заполнения базы в MSSQL
    CREATE TABLE customer (
        id INT PRIMARY KEY,
        name VARCHAR(100)
    );
    
    CREATE TABLE stock (
        id INT PRIMARY KEY,
        name VARCHAR(100)
    );
    
    CREATE TABLE product (
        id INT PRIMARY KEY,
        name VARCHAR(100),
        [group] INT
    );
    CREATE INDEX product_group ON product ([group]);
    
    CREATE TABLE receipt (
        id INT PRIMARY KEY,
        customer INT NOT NULL,
    	date DATE NOT NULL,
        CONSTRAINT receipt_customer_fk
            FOREIGN KEY (customer) REFERENCES customer (id),
    	stock INT NOT NULL,
        CONSTRAINT receipt_stock_fk
            FOREIGN KEY (stock) REFERENCES stock (id),
        data VARCHAR(400)
    );
    CREATE INDEX receipt_date ON receipt (date);
    CREATE INDEX receipt_customer_fk ON receipt (customer);
    CREATE INDEX receipt_stock_fk ON receipt (stock);
    
    CREATE TABLE shipment (
        id INT PRIMARY KEY,
        customer INT NOT NULL,
    	date DATE NOT NULL,
        CONSTRAINT shipment_customer_fk
            FOREIGN KEY (customer) REFERENCES customer (id),
    	stock INT NOT NULL,
        CONSTRAINT shipment_stock_fk
            FOREIGN KEY (stock) REFERENCES stock (id),
        data VARCHAR(400)
    );
    CREATE INDEX shipment_date ON shipment (date);
    CREATE INDEX shipment_customer_fk ON shipment (customer);
    CREATE INDEX shipment_stock_fk ON shipment (stock);
    CREATE TABLE receiptdetail (
        id INT PRIMARY KEY,
        receipt INT NOT NULL,
    	product INT NOT NULL,
    	quantity NUMERIC(16,3),
        CONSTRAINT receiptdetail_receipt_fk
            FOREIGN KEY (receipt) REFERENCES receipt (id),
        CONSTRAINT receiptdetail_product_fk
        	FOREIGN KEY (product) REFERENCES product (id),
        data VARCHAR(400)
    );
    CREATE INDEX receiptdetail_receipt_fk ON receiptdetail (receipt);
    CREATE INDEX receiptdetail_product_fk ON receiptdetail (product);
    
    CREATE TABLE shipmentdetail (
        id INT PRIMARY KEY,
        shipment INT NOT NULL,
    	product INT NOT NULL,
    	quantity NUMERIC(16,3),
        CONSTRAINT shipmentdetail_shipment_fk
            FOREIGN KEY (shipment) REFERENCES shipment (id),
        CONSTRAINT shipmentdetail_product_fk
        	FOREIGN KEY (product) REFERENCES product (id),
        sd INT,
        data VARCHAR(400)
    );
    CREATE INDEX shipmentdetail_shipment_fk ON shipmentdetail (shipment);
    CREATE INDEX shipmentdetail_product_fk ON shipmentdetail (product);
    CREATE INDEX shipmentdetail_sd ON shipmentdetail (sd);
    CREATE INDEX shipmentdetail_p_s ON shipmentdetail (product,shipment);
    
    WITH
    numbers ( n ) AS (
       SELECT 1 AS n
       UNION ALL
       SELECT n + 1 AS n
       FROM   numbers
       WHERE  n <= 10
    )
    INSERT INTO stock (id, name)
    SELECT n, CONCAT('Stock ',n) FROM numbers option (maxrecursion 0);
    
    WITH
    numbers ( n ) AS (
       SELECT 1 AS n
       UNION ALL
       SELECT n + 1 AS n
       FROM   numbers
       WHERE  n <= 1000
    )
    INSERT INTO customer (id, name)
    SELECT n, CONCAT('Customer ',n) FROM numbers option (maxrecursion 0);
    
    WITH
    numbers ( n ) AS (
       SELECT 1 AS n
       UNION ALL
       SELECT n + 1 AS n
       FROM   numbers
       WHERE  n <= 50000
    )
    INSERT INTO product (id, name, [group])
    SELECT n, CONCAT('Product ',n),  ABS(checksum(NewId()))% 1000 + 1 FROM numbers option (maxrecursion 0);
    
    WITH
    numbers ( n ) AS (
       SELECT 1 AS n
       UNION ALL
       SELECT n + 1 AS n
       FROM   numbers
       WHERE  n <= 10000
    )
    INSERT INTO receipt (id, customer, stock, date, data)
    SELECT n,  ABS(checksum(NewId()))% 1000 + 1, ABS(checksum(NewId()))% 10 + 1, DATEADD(day, -ABS(checksum(NewId()))% 300, GETDATE()), CONCAT('data',n) FROM numbers option (maxrecursion 0);
    
    WITH
    numbers ( n ) AS (
       SELECT 1 AS n
       UNION ALL
       SELECT n + 1 AS n
       FROM   numbers
       WHERE  n <= 1000000
    )
    INSERT INTO receiptdetail (id, receipt, product, quantity, data)
    SELECT n,  ABS(checksum(NewId()))% 10000 + 1,  ABS(checksum(NewId()))% 50000 + 1,  ABS(checksum(NewId()))% 100 + 1, CONCAT('data',n) FROM numbers option (maxrecursion 0);
    
    WITH
    numbers ( n ) AS (
       SELECT 1 AS n
       UNION ALL
       SELECT n + 1 AS n
       FROM   numbers
       WHERE  n <= 100000
    )
    INSERT INTO shipment (id, customer, stock, date, data)
    SELECT n,  ABS(checksum(NewId()))% 1000 + 1, ABS(checksum(NewId()))% 10 + 1, DATEADD(day, -ABS(checksum(NewId()))% 300, GETDATE()), CONCAT('data',n) FROM numbers option (maxrecursion 0);
    
    WITH
    numbers ( n ) AS (
       SELECT 1 AS n
       UNION ALL
       SELECT n + 1 AS n
       FROM   numbers
       WHERE  n <= 10000000
    )
    INSERT INTO shipmentdetail (id, shipment, product, quantity, data, sd)
    SELECT n,  ABS(checksum(NewId()))% 100000 + 1,  ABS(checksum(NewId()))% 50000 + 1,  ABS(checksum(NewId()))% 10 + 1, CONCAT('data',n), CASE WHEN n % 100000 = 0 THEN n+1 ELSE NULL END FROM numbers option (maxrecursion 0);
    
    EXEC sp_updatestats;   
    


    Скрипт заполнения базы в PostgreSQL
    CREATE TABLE customer (
        id INT PRIMARY KEY,
        name VARCHAR(100)
    );
    
    CREATE TABLE stock (
        id INT PRIMARY KEY,
        name VARCHAR(100)
    );
    
    CREATE TABLE product (
        id INT PRIMARY KEY,
        name VARCHAR(100),
        "group" INT
    );
    CREATE INDEX product_group ON product ("group");
    
    CREATE TABLE receipt (
        id INT PRIMARY KEY,
        customer INT NOT NULL,
    	"date" DATE NOT NULL,
        CONSTRAINT receipt_customer_fk
            FOREIGN KEY (customer) REFERENCES customer (id),
       	stock INT NOT NULL,
        CONSTRAINT receipt_stock_fk
            FOREIGN KEY (stock) REFERENCES stock (id),
        data VARCHAR(400)
    );
    CREATE INDEX receipt_customer_fk ON receipt (customer);
    CREATE INDEX receipt_stock_fk ON receipt (stock);
    
    CREATE TABLE shipment (
        id INT PRIMARY KEY,
        customer INT NOT NULL,
    	"date" DATE NOT NULL,
        CONSTRAINT shipment_customer_fk
            FOREIGN KEY (customer) REFERENCES customer (id),
        stock INT NOT NULL,
        CONSTRAINT shipment_stock_fk
            FOREIGN KEY (stock) REFERENCES stock (id),
        data VARCHAR(400)
    );
    CREATE INDEX shipment_customer_fk ON shipment (customer);
    CREATE INDEX shipment_stock_fk ON shipment (stock);
    
    CREATE TABLE receiptdetail (
        id INT PRIMARY KEY,
        receipt INT NOT NULL,
    	product INT NOT NULL,
    	quantity NUMERIC(16,3),
        CONSTRAINT receiptdetail_receipt_fk
            FOREIGN KEY (receipt) REFERENCES receipt (id),
        CONSTRAINT receiptdetail_product_fk
        	FOREIGN KEY (product) REFERENCES product (id),
        data VARCHAR(400)
    );
    CREATE INDEX receiptdetail_receipt_fk ON receiptdetail (receipt);
    CREATE INDEX receiptdetail_product_fk ON receiptdetail (product);
    
    CREATE TABLE shipmentdetail (
        id INT PRIMARY KEY,
        shipment INT NOT NULL,
    	product INT NOT NULL,
    	quantity NUMERIC(16,3),
        CONSTRAINT shipmentdetail_shipment_fk
            FOREIGN KEY (shipment) REFERENCES shipment (id),
        CONSTRAINT shipmentdetail_product_fk
        	FOREIGN KEY (product) REFERENCES product (id),
        sd INT,
        data VARCHAR(400)
    );
    CREATE INDEX shipmentdetail_shipment_fk ON shipmentdetail (shipment);
    CREATE INDEX shipmentdetail_product_fk ON shipmentdetail (product);
    CREATE INDEX shipmentdetail_sd ON shipmentdetail (sd);
    CREATE INDEX shipmentdetail_p_s ON shipmentdetail (product,shipment);
    
    INSERT INTO stock (id, name)
    WITH RECURSIVE
    numbers ( n ) AS (
       SELECT 1 AS n
       UNION ALL
       SELECT n + 1 AS n
       FROM   numbers
       WHERE  n <= 10
    )
    SELECT n, 'Stock '||n FROM numbers;
    
    INSERT INTO customer (id, name)
    WITH RECURSIVE
    numbers ( n ) AS (
       SELECT 1 AS n
       UNION ALL
       SELECT n + 1 AS n
       FROM   numbers
       WHERE  n <= 1000
    )
    SELECT n, 'Customer '||n FROM numbers;
    
    INSERT INTO product (id, name, "group")
    WITH RECURSIVE
    numbers ( n ) AS (
       SELECT 1 AS n
       UNION ALL
       SELECT n + 1 AS n
       FROM   numbers
       WHERE  n <= 50000
    )
    SELECT n, 'Product '||n, floor(random() * 1000) + 1 FROM numbers;
    
    INSERT INTO receipt (id, customer, stock, "date", data)
    WITH RECURSIVE
    numbers ( n ) AS (
       SELECT 1 AS n
       UNION ALL
       SELECT n + 1 AS n
       FROM   numbers
       WHERE  n <= 10000
    )
    SELECT n, floor(random() * 1000) + 1, floor(random() * 10) + 1, CURRENT_DATE-CAST(floor(random() * 300) AS int), 'data'||n FROM numbers;
    
    INSERT INTO receiptdetail (id, receipt, product, quantity, data)
    WITH RECURSIVE
    numbers ( n ) AS (
       SELECT 1 AS n
       UNION ALL
       SELECT n + 1 AS n
       FROM   numbers
       WHERE  n <= 1000000
    )
    SELECT n, floor(random() * 10000) + 1, floor(random() * 50000) + 1, floor(random() * 100) + 1, 'data'||n FROM numbers;
    
    INSERT INTO shipment (id, customer, stock, "date", data)
    WITH RECURSIVE
    numbers ( n ) AS (
       SELECT 1 AS n
       UNION ALL
       SELECT n + 1 AS n
       FROM   numbers
       WHERE  n <= 100000
    )
    SELECT n, floor(random() * 1000) + 1, floor(random() * 10) + 1, CURRENT_DATE-CAST(floor(random() * 300) AS int), 'data'||n FROM numbers;
    
    INSERT INTO shipmentdetail (id, shipment, product, quantity, data, sd)
    WITH RECURSIVE
    numbers ( n ) AS (
       SELECT 1 AS n
       UNION ALL
       SELECT n + 1 AS n
       FROM   numbers
       WHERE  n <= 10000000
    )
    SELECT n, floor(random() * 100000) + 1, floor(random() * 50000) + 1, floor(random() * 10) + 1, 'data'||n, CASE WHEN n % 100000 = 0 THEN n+1 ELSE NULL END FROM numbers;
    
    ANALYZE;
    


    Уже заполненные базы данных, на которых проводилось тестирование, и параметры подключения к ним можно найти здесь:

    Параметры подключения к Oracle
    Hostname:116.203.61.136
    Port:1521
    SID:orclcdb
    User/DB:test
    Password:test

    Параметры подключения к MS SQL
    Hostname:195.201.126.177
    DB:test
    User:guest
    Password:Guest111

    Параметры подключения к PostgreSQL
    Hostname:159.69.8.94
    Port:5432
    DB:test
    User:guest
    Password:guest

    Представления (View)


    Первоначально SQL задумывался как набор команд для непосредственного общения пользователя с системой. Эта идея достаточно быстро и вполне предсказуемо провалилась, после чего SQL превратился в инструмент разработки, а значит, как и у любого другого инструмента разработки, у него в первую очередь появилась потребность в абстрагировании. Так на свет появились представления (View).

    Представления дали SQL возможность отделять объявление функционала от его реализации. Так, например, разработчик может создать представление остатки:

    CREATE VIEW balance AS
    SELECT stock, product, SUM(quantity) AS quantity
    	FROM
    		(SELECT receipt.stock, product, quantity
    		FROM receiptDetail 
    			JOIN receipt ON receiptDetail.receipt = receipt.id
    		UNION ALL 
    		SELECT shipment.stock, product, -quantity
    			FROM shipmentDetail 
    			JOIN shipment ON shipmentDetail.shipment = shipment.id
    		) details
    	GROUP BY stock, product
    

    И дальше обращаться к этому представлению как к обычной таблице.

    SELECT product.name, balance.stock, balance.quantity 
    FROM product
    JOIN balance ON product.id = balance.product 
    WHERE product.name = 'Product 8693'
    

    При этом, если ему понадобится изменить логику вычисления остатков, он может легко это сделать, просто поменяв само представление остатков, после чего все запросы, которые использовали это представление, будут работать уже с новой его реализацией.

    Как и в структурном программировании, абстрагирование дало SQL две очень важных возможности: упрощение и повторное использование. Казалось бы — вот оно счастье, но дальше, как говорится, что-то пошло не так.

    View: Материализация представлений поддерживается в очень частных случаях


    Если вы начнете использовать описанное выше представление остатков в реальной базе данных, вы, скорее всего, очень быстро обнаружите деградацию производительности. Дело в том, что остатки обычно используются очень часто, а значит, вычислять остатки при каждом обращении к ним будет очень накладно. Соответственно, чтобы избежать этих расходов, логично такое представление «материализовать», то есть сохранить в таблицу и автоматически обновлять ее при изменении данных, которые это представление использует. Это чуть замедлит запись, но очень сильно ускорит чтение. Казалось бы, что может быть проще. Но проблема в том, что поддерживать такую материализацию, мягко говоря, не так легко, как кажется. Во всяком случае, чтобы это работало эффективно на больших объемах. В вышеприведенным представлении остатков, например, при изменении склада в документе нужно от остатков по старому складу для всех товаров в документе отнять количество в этом документе, после чего добавить это количество к остаткам по новому складу. А при изменении количества для строки в документе нужно к остаткам по складу добавить разницу между старым и новым количеством. Если же, к примеру, в логику остатков добавить внутренние перемещения, все совсем запутается.

    Посмотрим, что в этом плане умеют современные СУБД.

    У PostgreSQL все просто, материализованные представления он поддерживает, но никаких инкрементальных обновлений, тем более выполняемых сразу при применении транзакции он не поддерживает.

    В MS SQL есть так называемые индексированные представления (по сути те же материализованные представления), которые умеют инкрементально обновляться, но список ограничений там впечатляет. Собственно, поддерживаются только SUM GROUP BY и INNER JOIN, то есть даже вышеупомянутый элементарный пример с остатками в MS SQL работать не будет. Плюс в MS SQL нельзя ссылаться на другие представления, поэтому можно смело считать, что материализация представлений в MS SQL не поддерживается вообще.

    С Oracle все немного сложнее
    За инкрементальную обновляемость представлений отвечает опция FAST REFRESH.

    У механизма FAST REFRESH в Oracle список ограничений еще длиннее чем у MS SQL, но фактически он сводится к следующему:

    • Из агрегирующих функций для GROUP BY поддерживается только SUM и ее производные (например COUNT, AVERAGE)
    • Не поддерживаются оконные функции, рекурсивные функции, табличные функции и любые другие «нестандартные» возможности.
    • Все подзапросы (и представления) с GROUP BY, UNION ALL, OUTER JOIN также нужно материализовать.
    • У OUTER JOIN и формул есть какие-то проблемы с производительностью (правда, не уточняется, какие именно)
    • Несколько специфических требований, что если есть SUM, то должен быть COUNT, для всех таблиц должны быть включены логи материализованных представлений, UNION'ы не поддерживаются и их нужно переписывать в UNION ALL и т.п.

    Третий и пятый пункты означают, что если вы захотели материализовать какое-то представление, вам надо переписать и материализовать все используемые этим представлением подзапросы / другие представления, что, во-первых, убивает всю идею абстрагирования, а во-вторых, приводит к созданию огромного количества избыточных материализованных представлений. Вторая проблема усугубляется еще тем фактом, что все представления материализуются в отдельные таблицы, то есть нельзя, к примеру, создать одну таблицу с ключами склад и товар и материализовать туда сразу несколько представлений: например, остатков, резервов, наличия в ассортименте и т.п. Соответственно, каждое материализованное представление требует дополнительных ресурсов на хранение его ключей / индексов, а также на их обновление и соединение в запросах, где это представление используется.

    Но даже если вы прорветесь через все эти ограничения, проблемы с производительностью, абстрагированием и избыточным потреблением ресурсов и все таки материализуете нужное представление, вас будет ждать еще один неприятный сюрприз: материализованное представление обновляется только в самом конце транзакции.

    То есть, если у вас есть код:

    START TRANSACTION ;
    …
    UPDATE receiptDetail SET quantity = quantity + 1;
    ...
    SELECT quantity FROM balance WHERE stock = 14 AND sku=65
    …
    COMMIT;
    

    то, если представление balance не материализовано, вы получите актуальный остаток (с учетом изменения строки документа), а если материализовано, вы получите остаток на начало транзакции. Как следствие:

    • процесс материализации становится непрозрачным — при материализации / разматериализации необходимо просмотреть все использования представления на предмет изменения поведения в транзакции
    • абсолютно непонятно, как в принципе получить актуальные данные для материализованного представления внутри транзакции

    Таким образом, в Oracle поддержка материализаций вроде как лучше, чем в MSSQL, но все равно очень далека от жизнеспособности. Поэтому большинство разработчиков, когда их спрашиваешь про материализованные представления, отвечают, что они вроде и слышали о такой возможности, но реально на практике никогда ей не пользовались. С другой стороны, если вы спросите этих же разработчиков, как именно надо решать задачу материализации тех же остатков, скорее всего услышите что-то про триггеры, общие точки изменения остатков, неправильную физическую модель и так далее. А на уточняющие вопросы — триггеры на что, как именно организовывать эти общие точки и физическую модель, скорее всего, услышите ответ: «я стратег, а не тактик» и вообще «доктор, откуда у вас такие картинки», я с такой задачей никогда не сталкивался. Хотя в то, что человек, который разрабатывает ИС, никогда не сталкивался с задачей хранения и обновления остатков (ну или задолженностей и других похожих показателей), как-то не очень верится.

    На самом деле задачи материализации обычно решаются именно что «как-то». Логика вычисления и обновления представления дублируются. Часть сценариев обновления запрещается (например, полностью запрещают изменять документ). Еще иногда используют такой трюк как «перепроведение»: сначала эмулируют полное удаление старых данных (например всего документа), а потом добавление новых данных (то есть создание документа заново). В таком случае достаточно реализовать только логику удаления и добавления, что значительно проще, но куда менее производительно (например, в случаях, когда изменяется только одна строка документа).

    View: Для представлений не поддерживаются ограничения и триггеры


    Допустим, вы создали представление остатков как в примере выше, используете его для различных вычислений, и тут у вас появляется новое бизнес-требование: остаток должен быть больше 0. Если бы эти данные были первичными и хранились в таблице, вы могли бы легко решить эту проблему, создав соответствующее ограничение на нужное поле, но это представление, а для представлений возможность создавать ограничения ни одна из существующих современных СУБД не поддерживает.

    Единственное, что могут предложить в этом плане некоторые коммерческие СУБД (MS SQL и Oracle) — это материализовать представление и создать ограничение для него. Но здесь мы опять-таки возвращаемся к огромному количеству ограничений материализованных представлений. Тут, правда, стоит отметить, что проблема не обновления материализованных представлений в транзакции для ограничений не актуальна. Зато есть другая проблема: ограничения проверяются в самом конце транзакции. Это, в свою очередь, означает, что сначала выполняется вся бизнес-логика системы (а это может быть достаточно большой объем работы), и, если в самом конце вдруг нарушится какое-то ограничение, то вся выполненная работа будет отменена, а значит ресурсы сервера и время пользователя будут потрачены впустую.

    Вообще тему ограничений на материализованные представления (и материализованные представления вообще) относительно подробно разбирал один из достаточно авторитетных экспертов Oracle Donald Burleson в одной из своих книг. И, в общем-то, пришел к тому же выводу, что и я:
    This is pointless from a practical perspective, however.
    Правда следующий абзац:
    Sooner or later incremental refresh limitations will be lifted. For the purpose of further constraint study in this book, let's continue pretending as if it already happened.
    получился у него весьма ироничным c учетом того, что книга писалась в 2005 году, а сейчас уже 2019 год, и за последние 14 лет список ограничений практически не изменился.

    Но если для ограничений такой workaround с материализованными представлениями еще как-то может работать, то с триггерами вообще забавно:
    If you create a trigger on a base table of a materialized view, then you must ensure that the trigger does not fire during a refresh of the materialized view. During refresh, the DBMS_MVIEW procedure I_AM_A_REFRESH returns TRUE.
    То есть триггеры создавать можно, но делать в них ничего нельзя, так как для материализованных представлений они ведут себя очень непредсказуемо. Такой кот Шредингера, вроде триггер есть, а вроде его и нет. Тут можно найти чуть более подробный разбор этой темы на Ask Tom.

    View: В параметризованные представления во FROM можно передавать только константы


    Теперь представим, что нам нужно получить не просто остаток, а остаток на дату. Если бы у нас была таблица со «всеми» датами (например dates), то мы могли бы создать следующее представление:

    CREATE VIEW balance AS
    SELECT stock, product, dates.value AS date, SUM(quantity) AS quantity
    	FROM
    		(SELECT receipt.stock, product, receipt.date, quantity
    		FROM receiptDetail 
    			JOIN receipt ON receiptDetail.receipt = receipt.id
    		UNION ALL 
    		SELECT shipment.stock, product, shipment.date, -quantity
    			FROM shipmentDetail 
    			JOIN shipment ON shipmentDetail.shipment = shipment.id
    		) details
    		JOIN dates ON details.date < dates.value
    	GROUP BY stock, product, dates.value
    

    Но такой таблицы в SQL не существует, соответственно, для решения этой задачи придется использовать что-то другое.

    В MS SQL для решения таких задач есть так называемые table inlined функции, в них можно объявить параметры и использовать их внутри запроса:

    CREATE FUNCTION balance (
        @date DATE
    )
    RETURNS TABLE
    AS
    RETURN
    SELECT stock, product, SUM(quantity) AS quantity
    	FROM
    		(SELECT receipt.stock, product, quantity
    		FROM receiptDetail 
    			JOIN receipt ON receiptDetail.receipt = receipt.id
    			WHERE receipt.date < @date
    		UNION ALL 
    		SELECT shipment.stock, product, -quantity
    			FROM shipmentDetail 
    			JOIN shipment ON shipmentDetail.shipment = shipment.id
    			WHERE shipment.date < @date
    		) details
    	GROUP BY stock, product
    

    В свою очередь, во FROM этим функциям можно передавать аргументы, но только не в ON, а в скобках:

    SELECT product.name, balance.stock, balance.quantity 
    	FROM product 
    	JOIN balance('07.07.2019') ON product.id = balance.product 
    	WHERE product.name = 'Product 8693'
    

    В остальном же эти функции ведут себя точно так же, как и представления (часто их и называют параметризованными представлениями).

    Даже если оставить в стороне тот факт, что для работы с параметрами примитивных типов в SQL нужна отдельная абстракция, у этого механизма есть одно очень неприятное ограничение, которое делает его применение весьма ограниченным. Так, при использовании во FROM этим функциям нельзя передавать в качестве аргументов колонки других таблиц из FROM. То есть, если попытаться выполнить следующий запрос:

    SELECT shipmentDetail.id, b.quantity
    	FROM shipmentDetail 
    	JOIN shipment ON shipmentDetail.shipment = shipment.id
    	JOIN balance(shipment.date) b ON b.stock = shipment.stock AND b.product = shipmentDetail.product 
    	WHERE shipmentDetail.quantity = 5
    

    SQL сервер выдаст ошибку, что таблица shipment не найдена. Тут, конечно, можно подумать, что так и надо, потому как использование таблицы из FROM в аргументах параметризованного представления нарушает идеологию свободной перестановки JOIN (то есть, что JOIN'ы можно переставить в любом порядке, в том числе соединять shipment после balance) и поэтому такой возможности в SQL не может быть теоретически. Но на самом деле многие SQL сервера умеют использовать в подзапросах поля из уже соединенных таблиц (мы увидим это в следующем разделе про Join Predicate Push Down), поэтому они вполне могли бы разрешить это делать разработчику, просто убирая при перестановке JOIN'ов варианты, когда параметризованное представление (или вообще любой подзапрос) соединяется до таблицы, поля которой оно используют. И почему ни один производитель СУБД так не сделал, если честно, для меня загадка.

    UPD: Пришла подсказка из зала, что такую возможность производители СУБД все же реализовали в виде специальной конструкции APPLY (или опции LATERAL в JOIN). Даже если опять-таки отбросить факт необходимости использования еще одной абстракции для реализации по сути одного частного случая, у подхода с APPLY есть два недостатка (один из которых весьма существенный):

    Проблемы APPLY
    • Так как в этих синтаксических конструкциях нету ON, для OUTER APPLY придется делать подзапрос (в CROSS APPLY условие соединения можно писать в WHERE):
      SELECT shipmentDetail.id, b.quantity
      	FROM shipmentDetail 
      	JOIN shipment ON shipmentDetail.shipment = shipment.id
      	OUTER APPLY (
      		SELECT quantity 
      			FROM balance(shipment.date) 
      			WHERE stock = shipment.stock AND product = shipmentDetail.product
      		) b
      	WHERE shipmentDetail.quantity = 5
      

    • Но самое главное, APPLY в таких случаях — это практически гарантировано nested loop join. То есть, если вы попытаетесь выполнить верхний запрос, вы получите следующие планы:

      План MS SQL
      SELECT shipmentDetail.id, b.quantity   FROM shipmentDetail    JOIN shipment ON shipmentDetail.shipment = shipment.id   CROSS APPLY balance(shipment.date) b   WHERE shipmentDetail.quantity = 5 AND b.stock = shipment.stock AND b.product = shipmentDetail.product  OPTION (MAXDOP 1)
        |--Filter(WHERE:([Union1013]=[test].[dbo].[shipment].[stock]))
             |--Nested Loops(Inner Join, OUTER REFERENCES:([test].[dbo].[shipmentdetail].[product], [test].[dbo].[shipment].[date]))
                  |--Hash Match(Inner Join, HASH:([test].[dbo].[shipment].[id])=([test].[dbo].[shipmentdetail].[shipment]))
                  |    |--Clustered Index Scan(OBJECT:([test].[dbo].[shipment].[PK__shipment__3213E83F4B7B4D07]))
                  |    |--Clustered Index Scan(OBJECT:([test].[dbo].[shipmentdetail].[PK__shipment__3213E83F996CFFF4]), WHERE:([test].[dbo].[shipmentdetail].[quantity]=(5.000)))
                  |--Compute Scalar(DEFINE:([Expr1016]=CASE WHEN [Expr1023]=(0) THEN NULL ELSE [Expr1024] END))
                       |--Stream Aggregate(GROUP BY:([Union1013]) DEFINE:([Expr1023]=COUNT_BIG([Union1015]), [Expr1024]=SUM([Union1015])))
                            |--Sort(ORDER BY:([Union1013] ASC))
                                 |--Concatenation
                                      |--Nested Loops(Inner Join, OUTER REFERENCES:([test].[dbo].[receiptdetail].[receipt]))
                                      |    |--Index Spool(SEEK:([test].[dbo].[receiptdetail].[product]=[test].[dbo].[shipmentdetail].[product]))
                                      |    |    |--Clustered Index Scan(OBJECT:([test].[dbo].[receiptdetail].[PK__receiptd__3213E83FE8063B8C]))
                                      |    |--Clustered Index Seek(OBJECT:([test].[dbo].[receipt].[PK__receipt__3213E83FE2F580DF]), SEEK:([test].[dbo].[receipt].[id]=[test].[dbo].[receiptdetail].[receipt]),  WHERE:([test].[dbo].[receipt].[date]<[test].[dbo].[shipment].[date]) ORDERED FORWARD)
                                      |--Nested Loops(Inner Join, OUTER REFERENCES:([test].[dbo].[shipmentdetail].[shipment], [Expr1022]) WITH UNORDERED PREFETCH)
                                           |--Index Spool(SEEK:([test].[dbo].[shipmentdetail].[product]=[test].[dbo].[shipmentdetail].[product]))
                                           |    |--Compute Scalar(DEFINE:([Expr1012]= -[test].[dbo].[shipmentdetail].[quantity]))
                                           |         |--Clustered Index Scan(OBJECT:([test].[dbo].[shipmentdetail].[PK__shipment__3213E83F996CFFF4]))
                                           |--Clustered Index Seek(OBJECT:([test].[dbo].[shipment].[PK__shipment__3213E83F4B7B4D07]), SEEK:([test].[dbo].[shipment].[id]=[test].[dbo].[shipmentdetail].[shipment]),  WHERE:([test].[dbo].[shipment].[date]<[test].[dbo].[shipment].[date]) ORDERED FORWARD)
      


      План Oracle
      ---------------------------------------------------------------------------------------------------
      | Id  | Operation               | Name            | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
      ---------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT        |                 |   637K|    44M|       |    26G  (1)|288:02:33 |
      |   1 |  NESTED LOOPS           |                 |   637K|    44M|       |    26G  (1)|288:02:33 |
      |*  2 |   HASH JOIN             |                 |   901K|    30M|  2736K| 15542   (1)| 00:00:01 |
      |   3 |    TABLE ACCESS FULL    | SHIPMENT        |   100K|  1562K|       |   137   (1)| 00:00:01 |
      |*  4 |    TABLE ACCESS FULL    | SHIPMENTDETAIL  |   909K|    16M|       | 13935   (1)| 00:00:01 |
      |*  5 |   VIEW                  | VW_LAT_F55B5580 |     1 |    39 |       | 29439   (1)| 00:00:02 |
      |   6 |    SORT GROUP BY        |                 |   393K|  8061K|   167M| 29439   (1)| 00:00:02 |
      |   7 |     VIEW                |                 |  5457K|   109M|       | 15337   (1)| 00:00:01 |
      |   8 |      UNION-ALL          |                 |       |       |       |            |          |
      |*  9 |       HASH JOIN         |                 |   498K|    13M|       |  1252   (1)| 00:00:01 |
      |* 10 |        TABLE ACCESS FULL| RECEIPT         |  4984 | 74760 |       |    15   (0)| 00:00:01 |
      |  11 |        TABLE ACCESS FULL| RECEIPTDETAIL   |  1000K|    12M|       |  1234   (1)| 00:00:01 |
      |* 12 |       HASH JOIN         |                 |  4959K|   137M|       | 14085   (1)| 00:00:01 |
      |* 13 |        TABLE ACCESS FULL| SHIPMENT        | 50001 |   781K|       |   137   (1)| 00:00:01 |
      |  14 |        TABLE ACCESS FULL| SHIPMENTDETAIL  |    10M|   123M|       | 13921   (1)| 00:00:01 |
      ---------------------------------------------------------------------------------------------------
       
      Predicate Information (identified by operation id):
      ---------------------------------------------------
       
         2 - access("SHIPMENTDETAIL"."SHIPMENT"="SHIPMENT"."ID")
         4 - filter("SHIPMENTDETAIL"."QUANTITY"=5)
         5 - filter("B"."STOCK"="SHIPMENT"."STOCK" AND "B"."PRODUCT"="SHIPMENTDETAIL"."PRODUCT")
         9 - access("RECEIPTDETAIL"."RECEIPT"="RECEIPT"."ID")
        10 - filter("RECEIPT"."date"<"SHIPMENT"."date")
        12 - access("SHIPMENTDETAIL"."SHIPMENT"="SHIPMENT"."ID")
        13 - filter("SHIPMENT"."date"<"SHIPMENT"."date")
      


      То есть пробег по всем shipmentDetail с quantity > 5 и для каждого расчет остатков, что ну очень неэффективно. Мне даже не удалось дождаться выполнения этих запросов поэтому привожу только estimate планы.

    В любом случае, факт остается фактом, и сейчас единственный выход для разработчика в таких случаях это самостоятельно делать pushdown верхнего контекста внутрь представления:

    Запрос
    SELECT shipmentDetail.id, b.quantity
    	FROM shipmentDetail 
    	JOIN shipment ON shipmentDetail.shipment = shipment.id
    	JOIN (SELECT stock, product, dates.date, SUM(quantity) AS quantity
    		FROM
    			(SELECT receipt.stock, product, receipt.date, quantity
    			FROM receiptDetail 
    				JOIN receipt ON receiptDetail.receipt = receipt.id
    			UNION ALL 
    			SELECT shipment.stock, product, shipment.date, -quantity
    				FROM shipmentDetail 
    				JOIN shipment ON shipmentDetail.shipment = shipment.id
    			) details
    		JOIN 
    			(SELECT shipment.date
    				FROM shipmentDetail 
    				JOIN shipment ON shipmentDetail.shipment = shipment.id
    				WHERE shipmentDetail.quantity = 5
    				GROUP BY shipment.date
    			) dates ON details.date < dates.date
    		GROUP BY stock, product, dates.date
    	) b ON b.stock = shipment.stock AND b.product = shipmentDetail.product AND b.date = shipment.date
    	WHERE shipmentDetail.quantity = 5
    


    а значит, неоднократно повторять логику вычисления этого представления в различных запросах, и, тем самым, нарушать один из ключевых принципов программирования — Don't repeat yourself.

    Подытоживая все вышесказанное касательно представлений, можно сделать вывод, что представления, хоть и выглядят как таблицы, но по факту не умеют и четверти того, что умеют таблицы, поэтому, к сожалению, в состоянии обеспечить в SQL лишь самый базовый уровень абстрагирования (а точнее, очень низкий, по сравнению с тем, который мог бы быть, если бы все вышеописанные возможности поддерживались в общем случае)

    Join Predicate Push Down (JPPD)


    Проталкивание предикатов внутрь подзапросов является одной из самых важных возможностей оптимизатора SQL сервера. Этот механизм позволяет вычислять результаты подзапросов не для всех данных в базе (тем самым приводя порой к катастрофической деградации производительности), а только для тех данных, которые необходимы верхнему запросу.

    Как этот механизм работает в современных СУБД
    Сначала рассмотрим простой пример, когда у нас есть условие, что значение поля из подзапроса должно быть равно некоторой константе (то есть случай просто predicate push down, без join):

    SELECT balance.quantity
    	FROM (SELECT product, SUM(quantity) AS quantity
    		FROM
    			(SELECT product, quantity
    			FROM receiptDetail 
    				JOIN receipt ON receiptDetail.receipt = receipt.id
    			UNION ALL 
    			SELECT product, -quantity
    				FROM shipmentDetail 
    				JOIN shipment ON shipmentDetail.shipment = shipment.id
    			) details
    		GROUP BY product
    	) balance
    	WHERE balance.product = 345
    

    В этом случае SQL сервер видит, что снаружи есть условие на то, что поле product подзапроса должно быть равно 345, и автоматически переносит это условие туда. Заодно, так как в этом подзапросе есть группировка по полю product, SQL сервер автоматически убирает это поле из BY (так как оно всегда равно одному значению), а так как других BY в подзапросе нет, то и весь GROUP BY целиком. Итого получается следующий запрос:

    SELECT balance.quantity
    	FROM (SELECT SUM(quantity) AS quantity
    		FROM
    			(SELECT product, quantity
    			FROM receiptDetail 
    				JOIN receipt ON receiptDetail.receipt = receipt.id
    			UNION ALL 
    			SELECT product, -quantity
    				FROM shipmentDetail 
    				JOIN shipment ON shipmentDetail.shipment = shipment.id
    			) details
    			WHERE details.product = 345
    	) balance
    

    Далее SQL сервер видит такую же ситуацию с UNION подзапросами и автоматически переносит этот предикат внутрь каждого из UNION:

    SELECT balance.quantity
    	FROM (SELECT SUM(quantity) AS quantity
    		FROM
    			(SELECT product, quantity
    			FROM receiptDetail 
    				JOIN receipt ON receiptDetail.receipt = receipt.id
    				WHERE product = 345
    			UNION ALL 
    			SELECT product, -quantity
    				FROM shipmentDetail 
    				JOIN shipment ON shipmentDetail.shipment = shipment.id
    				WHERE product = 345
    			) details
    	) balance
    

    В итоге при наличии индекса по product в shipmentDetail и в receiptDetail такой запрос выполнится ну очень быстро.

    Теперь рассмотрим более сложный случай:

    SELECT balance.quantity
    	FROM product
    	JOIN
    		(SELECT product, SUM(quantity) AS quantity
    			FROM
    				(SELECT product, quantity
    				FROM receiptDetail 
    					JOIN receipt ON receiptDetail.receipt = receipt.id
    				UNION ALL 
    				SELECT product, -quantity
    					FROM shipmentDetail 
    					JOIN shipment ON shipmentDetail.shipment = shipment.id
    				) details
    			GROUP BY product
    		) balance ON balance.product = product.id
    	WHERE product.group = 54
    

    Как и при выполнении любого другого запроса SQL сервер начинает перебор порядков выполнения join. Допустим он уже выбрал, что первым join'ом будет product и что доставать записи оттуда он будет при помощи индекса по group (предположим, что он есть). Далее он пытается присоединить к результату подзапрос balance, например при помощи nested loop join (то есть пробегом по уже имеющемуся результату, в нашем случае — таблице product, отфильтрованной по полю group). В этот момент SQL сервер видит, что у него есть предикат balance.product = product.id, где product.id — константа, то есть, точь-в-точь предикат из примера выше, а значит можно запустить соответствующую технику predicate push, что он собственно и делает.

    План MSSQL
    Среднее время выполнения: 128мс
    Rows	Executes  Stmt Text
    41	1	  |--Nested Loops(Inner Join, OUTER REFERENCES:([test].[dbo].[product].[id]))
    41	1	       |--Index Seek(OBJECT:([test].[dbo].[product].[product_group]), SEEK:([test].[dbo].[product].[group]=(54)) ORDERED FORWARD)
    0	0	       |--Compute Scalar(DEFINE:([Expr1013]=CASE WHEN [globalagg1020]=(0) THEN NULL ELSE [globalagg1022] END))
    41	41	            |--Stream Aggregate(DEFINE:([globalagg1020]=SUM([partialagg1019]), [globalagg1022]=SUM([partialagg1021])))
    82	41	                 |--Concatenation
    41	41	                      |--Stream Aggregate(DEFINE:([partialagg1019]=COUNT_BIG([test].[dbo].[receiptdetail].[quantity]), [partialagg1021]=SUM([test].[dbo].[receiptdetail].[quantity])))
    809	41	                      |    |--Nested Loops(Inner Join, OUTER REFERENCES:([test].[dbo].[receiptdetail].[id]))
    809	41	                      |         |--Index Seek(OBJECT:([test].[dbo].[receiptdetail].[receiptdetail_product_fk]), SEEK:([test].[dbo].[receiptdetail].[product]=[test].[dbo].[product].[id]) ORDERED FORWARD)
    809	809	                      |         |--Clustered Index Seek(OBJECT:([test].[dbo].[receiptdetail].[PK__receiptd__3213E83F930DCBDA]), SEEK:([test].[dbo].[receiptdetail].[id]=[test].[dbo].[receiptdetail].[id]) LOOKUP ORDERED FORWARD)
    41	41	                      |--Stream Aggregate(DEFINE:([partialagg1019]=COUNT_BIG([Expr1010]), [partialagg1021]=SUM([Expr1010])))
    0	0	                           |--Compute Scalar(DEFINE:([Expr1010]= -[test].[dbo].[shipmentdetail].[quantity]))
    8383	41	                                |--Nested Loops(Inner Join, OUTER REFERENCES:([test].[dbo].[shipmentdetail].[id], [Expr1023]) WITH UNORDERED PREFETCH)
    8383	41	                                     |--Index Seek(OBJECT:([test].[dbo].[shipmentdetail].[shipmentdetail_product_fk]), SEEK:([test].[dbo].[shipmentdetail].[product]=[test].[dbo].[product].[id]) ORDERED FORWARD)
    8383	8383	                                     |--Clustered Index Seek(OBJECT:([test].[dbo].[shipmentdetail].[PK__shipment__3213E83F2FF9E454]), SEEK:([test].[dbo].[shipmentdetail].[id]=[test].[dbo].[shipmentdetail].[id]) LOOKUP ORDERED FORWARD)
    


    План Oracle
    Среднее время выполнения: 80мс
    -----------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                                 | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                          |                           |       |       | 11247 (100)|          |
    |   1 |  NESTED LOOPS                             |                           |    50 |  1100 | 11247   (1)| 00:00:01 |
    |   2 |   TABLE ACCESS BY INDEX ROWID BATCHED     | PRODUCT                   |    50 |   450 |    45   (0)| 00:00:01 |
    |*  3 |    INDEX RANGE SCAN                       | PRODUCT_GROUP             |    50 |       |     1   (0)| 00:00:01 |
    |   4 |   VIEW PUSHED PREDICATE                   |                           |     1 |    13 |   224   (0)| 00:00:01 |
    |*  5 |    FILTER                                 |                           |       |       |            |          |
    |   6 |     SORT AGGREGATE                        |                           |     1 |    26 |            |          |
    |   7 |      VIEW                                 |                           |   218 |  5668 |   224   (0)| 00:00:01 |
    |   8 |       UNION-ALL                           |                           |       |       |            |          |
    |   9 |        TABLE ACCESS BY INDEX ROWID BATCHED| RECEIPTDETAIL             |    20 |   180 |    23   (0)| 00:00:01 |
    |* 10 |         INDEX RANGE SCAN                  | RECEIPTDETAIL_PRODUCT_FK  |    20 |       |     3   (0)| 00:00:01 |
    |  11 |        TABLE ACCESS BY INDEX ROWID BATCHED| SHIPMENTDETAIL            |   198 |  1584 |   201   (0)| 00:00:01 |
    |* 12 |         INDEX RANGE SCAN                  | SHIPMENTDETAIL_PRODUCT_FK |   198 |       |     3   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       3 - access("PRODUCT"."group"=54)
       5 - filter(COUNT(*)>0)
      10 - access("RECEIPTDETAIL"."PRODUCT"="PRODUCT"."ID")
      12 - access("SHIPMENTDETAIL"."PRODUCT"="PRODUCT"."ID")
    


    Тем самым подзапрос balance рассчитывается только для товаров с группой 54, а не для всех товаров в базе (правда, нужно понимать, что в этом случае подзапрос рассчитывается несколько раз, для каждого товара с группой 54).

    Тут, конечно, может показаться, что техника JPPD может работать только для nested loop join, но это не так, SQL сервера умеют проталкивать и hash join. В этом случае проталкивается не предикат balance.product = значение, а «виртуальный» предикат hash(balance.product) = значение («виртуальный», потому как у этого предиката нет синтаксического эквивалента в SQL, но тем не менее для выполнения он используется).

    Вообще у механизма JPPD есть другое, более «декларативное» объяснение — через переписывание запроса. Так, верхний запрос можно переписать в виде:

    SELECT balance.quantity
    	FROM product
    	JOIN
    		(SELECT SUM(quantity) AS quantity
    			FROM
    				(SELECT quantity
    				FROM receiptDetail 
    					JOIN receipt ON receiptDetail.receipt = receipt.id
    					WHERE receiptDetail.product = product.id
    				UNION ALL 
    				SELECT -quantity
    					FROM shipmentDetail 
    					JOIN shipment ON shipmentDetail.shipment = shipment.id
    					WHERE shipmentDetail.product = product.id
    				) details
    		) balance ON TRUE
    	WHERE product.group = 54
    

    И именно в таком ключе (с переписыванием запроса) механизм JPPD описан в патенте Oracle. Однако, на мой взгляд, такое описание не совсем корректно, потому как ни в одном SQL сервере (в том числе Oracle) нельзя обращаться к полям таблиц из верхнего запроса (на самом деле, непонятно почему, но на этом вопросе мы уже останавливались, когда говорили о параметризованных представлениях), а значит, понять, как именно работает JPPD, из такого описания очень сложно.

    Справедливости ради, надо сказать, что SQL сервера не всегда делают описанные выше оптимизации автоматически. На самом деле, они только пытаются сделать такой predicate push down, то есть строят соответствующий план, затем рассчитывают стоимость его выполнения и сравнивают его со стоимостью выполнения запроса без проталкивания внутрь предикатов. И только если стоимость плана без проталкивания выше, выбирают план с проталкиванием.

    Отметим, что механизм JPPD особенно важен для описанного выше механизма представлений. И если бы JPPD не существовало, то и в механизме представлений тоже было бы очень мало смысла, потому как представления рассчитывались бы для всей (!) базы при каждом обращении к ним, а значит производительность представлений была бы просто ужасная.

    Итак, как мы видим, механизм JPPD в СУБД реализуется относительно просто, но у этой простоты есть и обратная сторона медали.

    JPPD: Не работает с оконными функциями и рекурсивными CTE


    Допустим, мы хотим получить порядковый номер строки в документе и напишем следующий запрос:

    SELECT id, seqnum
    	FROM 
    		(SELECT id, row_number() OVER (PARTITION BY shipment ORDER BY id) AS seqnum
    			FROM shipmentdetail
    		) t
    	WHERE t.id=6770436
    

    План MS SQL
    Среднее время выполнения: 1.2с
    Rows	Executes  Stmt Text
    1	1	  |--Filter(WHERE:([test].[dbo].[shipmentdetail].[id]=(6770436)))
    10000001	1	       |--Window Aggregate(DEFINE:([Expr1002]=row_number), PARTITION COLUMNS:([test].[dbo].[shipmentdetail].[shipment]), ROWS BETWEEN:(UNBOUNDED, CURRENT ROW))
    10000001	1	            |--Index Scan(OBJECT:([test].[dbo].[shipmentdetail].[shipmentdetail_shipment_fk]), ORDERED FORWARD)
    


    План Oracle
    Среднее время выполнения: 14с
    ----------------------------------------------------------------------------------------------
    | Id  | Operation           | Name           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT    |                |       |       |       | 57555 (100)|          |
    |*  1 |  VIEW               |                |    10M|   247M|       | 57555   (1)| 00:00:03 |
    |   2 |   WINDOW SORT       |                |    10M|   104M|   191M| 57555   (1)| 00:00:03 |
    |   3 |    TABLE ACCESS FULL| SHIPMENTDETAIL |    10M|   104M|       | 13841   (1)| 00:00:01 |
    ----------------------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       1 - filter("T"."ID"=6770436)
    


    Как можно увидеть из плана, SQL сервер рассчитывает номера строк для всех документов в базе и, как следствие, запрос выполняется целую секунду (вместо нескольких миллисекунд).

    JPPD: Низкая эффективность при работе с денормализованными данными


    Допустим, мы хотим получим получить все отгрузки с даты по дату вместе с общими суммами отгрузки клиентам (немного надуманный случай, тут правильнее было бы говорить о, скажем, сумме задолженности клиента, но базовый пример очень простой, а за его пределы выходить не хочется, поэтому будем использовать то, что есть):

    SELECT shipment.id, supplied.quantity
    	FROM shipment
    	JOIN (
    		SELECT customer, SUM(quantity) AS quantity
    			FROM shipmentDetail
    			JOIN shipment ON shipment.id = shipmentDetail.shipment
    			GROUP BY customer
    	) supplied ON supplied.customer = shipment.customer
    	WHERE shipment.date = DATEADD(day, -10, CAST(GETDATE() AS date))
    

    В этом случае SQL сервер будет рассчитывать сумму не для всех различных клиентов, по которым были отгрузки за эти даты, а для всех отгрузок за эти даты, то есть сумма по каждому клиенту может рассчитываться несколько раз.

    Правда, эта проблема проявляется в основном, когда есть корреляция между условием отбора и условием соединения (то есть в этом примере между датой отгрузки и клиентом). На практике такое бывает очень часто (например, многим клиентам отгрузка идет только в течении какого-то периода), но конкретно в тестовой базе этой статьи мы генерировали равномерно распределенные данные, поэтому продемонстрировать описанную проблему на этой базе, к сожалению, не получится.

    JPPD: Поддерживается только в коммерческих СУБД


    Несмотря на простоту реализации, JPPD не поддерживается в PostgreSQL. Даже в самом примитивном случае:

    SELECT income.quantity
    	FROM product
    	JOIN
    		(SELECT product, SUM(quantity) AS quantity
    			FROM shipmentDetail 
    			JOIN shipment ON shipmentDetail.shipment = shipment.id
    			GROUP BY product
    		) income ON income.product = product.id
    	WHERE product."group" = 54
    

    План PostgreSQL
    "Hash Join  (cost=252901.05..254168.34 rows=49 width=32) (actual time=11580.152..11607.399 rows=48 loops=1)"
    "  Hash Cond: (shipmentdetail.product = product.id)"
    "  ->  HashAggregate  (cost=252763.56..253394.04 rows=50439 width=36) (actual time=11579.912..11603.696 rows=50000 loops=1)"
    "        Group Key: shipmentdetail.product"
    "        ->  Hash Join  (cost=2985.02..202764.28 rows=9999855 width=9) (actual time=46.117..5967.219 rows=10000001 loops=1)"
    "              Hash Cond: (shipmentdetail.shipment = shipment.id)"
    "              ->  Seq Scan on shipmentdetail  (cost=0.00..173528.55 rows=9999855 width=13) (actual time=0.017..1158.157 rows=10000001 loops=1)"
    "              ->  Hash  (cost=1735.01..1735.01 rows=100001 width=4) (actual time=45.798..45.798 rows=100001 loops=1)"
    "                    Buckets: 131072  Batches: 1  Memory Usage: 4540kB"
    "                    ->  Seq Scan on shipment  (cost=0.00..1735.01 rows=100001 width=4) (actual time=0.018..19.940 rows=100001 loops=1)"
    "  ->  Hash  (cost=136.88..136.88 rows=49 width=4) (actual time=0.202..0.202 rows=48 loops=1)"
    "        Buckets: 1024  Batches: 1  Memory Usage: 10kB"
    "        ->  Bitmap Heap Scan on product  (cost=4.67..136.88 rows=49 width=4) (actual time=0.045..0.181 rows=48 loops=1)"
    "              Recheck Cond: ("group" = 54)"
    "              Heap Blocks: exact=46"
    "              ->  Bitmap Index Scan on product_group  (cost=0.00..4.66 rows=49 width=0) (actual time=0.025..0.025 rows=48 loops=1)"
    "                    Index Cond: ("group" = 54)"
    "Planning Time: 0.658 ms"
    "Execution Time: 11608.602 ms"
    


    По какой причине так получилось, и почему разработчики PostgreSQL заняты чем угодно, но не решением проблемы, из-за который те же представления использовать практически невозможно, если честно, для меня загадка.

    Возможно проблема в патенте на JPPD, который у Oracle действует аж до 2028 года (они его постоянно продлевают, внося небольшие изменения). Однако, как мы видели выше, Microsoft'у существование такого патента абсолютно не мешает.

    Как видим, все описанные проблемы JPPD хоть и не смертельные, но очень неприятные. Решить их можно, используя более общий механизм JPPD: собирать предикаты из внешнего запроса, группировать их по условиям соединения и полученный запрос добавлять при помощи JOIN внутрь подзапроса.

    Так, например, переписанный запрос оконными функциями будет выглядеть следующим образом:

    Запрос
    SELECT id, seqnum
    	FROM 
    		(SELECT id, row_number() OVER (PARTITION BY shipment ORDER BY id) AS seqnum
    			FROM shipmentdetail
    			JOIN 
    				(SELECT t.shipment AS ps
    					FROM shipmentdetail t 
    					WHERE t.id = 6770436
    			) pushed ON pushed.ps = shipment
    		) t
    	WHERE t.id=6770436
    


    План MS SQL
    Среднее время выполнения: 60мс
    Rows	Executes  Stmt Text
    1	1	  |--Filter(WHERE:([test].[dbo].[shipmentdetail].[id]=(6770436)))
    97	1	       |--Sequence Project(DEFINE:([Expr1003]=row_number))
    97	1	            |--Segment
    97	1	                 |--Nested Loops(Inner Join, OUTER REFERENCES:([t].[shipment]))
    1	1	                      |--Clustered Index Seek(OBJECT:([test].[dbo].[shipmentdetail].[PK__shipment__3213E83F2FF9E454] AS [t]), SEEK:([t].[id]=(6770436)) ORDERED FORWARD)
    97	1	                      |--Index Seek(OBJECT:([test].[dbo].[shipmentdetail].[shipmentdetail_shipment_fk]), SEEK:([test].[dbo].[shipmentdetail].[shipment]=[test].[dbo].[shipmentdetail].[shipment] as [t].[shipment]) ORDERED FORWARD)
    


    План Oracle
    Среднее время выполнения: 30мс
    ---------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                              | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                       |                            |       |       |   106 (100)|          |
    |*  1 |  VIEW                                  |                            |    99 |  2574 |   106   (1)| 00:00:01 |
    |   2 |   WINDOW SORT                          |                            |    99 |  2178 |   106   (1)| 00:00:01 |
    |   3 |    NESTED LOOPS                        |                            |    99 |  2178 |   105   (0)| 00:00:01 |
    |   4 |     TABLE ACCESS BY INDEX ROWID        | SHIPMENTDETAIL             |     1 |    11 |     3   (0)| 00:00:01 |
    |*  5 |      INDEX UNIQUE SCAN                 | SYS_C007881                |     1 |       |     2   (0)| 00:00:01 |
    |   6 |     TABLE ACCESS BY INDEX ROWID BATCHED| SHIPMENTDETAIL             |    99 |  1089 |   102   (0)| 00:00:01 |
    |*  7 |      INDEX RANGE SCAN                  | SHIPMENTDETAIL_SHIPMENT_FK |    99 |       |     2   (0)| 00:00:01 |
    ---------------------------------------------------------------------------------------------------------------------
    


    Predicate Information (identified by operation id):

    1 — filter(«T».«ID»=6770436)
    5 — access(«T».«ID»=6770436)
    7 — access(«T».«SHIPMENT»=«SHIPMENT»)

    План PostgreSQL
    "Subquery Scan on t  (cost=400.40..403.62 rows=1 width=12) (actual time=0.810..0.891 rows=1 loops=1)"
    "  Filter: (t.id = 6770436)"
    "  Rows Removed by Filter: 105"
    "  ->  WindowAgg  (cost=400.40..402.38 rows=99 width=16) (actual time=0.688..0.869 rows=106 loops=1)"
    "        ->  Sort  (cost=400.40..400.65 rows=99 width=8) (actual time=0.651..0.668 rows=106 loops=1)"
    "              Sort Key: shipmentdetail.shipment, shipmentdetail.id"
    "              Sort Method: quicksort  Memory: 29kB"
    "              ->  Nested Loop  (cost=5.63..397.12 rows=99 width=8) (actual time=0.094..0.593 rows=106 loops=1)"
    "                    ->  Index Scan using shipmentdetail_pkey on shipmentdetail t_1  (cost=0.43..8.45 rows=1 width=4) (actual time=0.021..0.023 rows=1 loops=1)"
    "                          Index Cond: (id = 6770436)"
    "                    ->  Bitmap Heap Scan on shipmentdetail  (cost=5.19..387.69 rows=98 width=8) (actual time=0.065..0.520 rows=106 loops=1)"
    "                          Recheck Cond: (shipment = t_1.shipment)"
    "                          Heap Blocks: exact=106"
    "                          ->  Bitmap Index Scan on shipmentdetail_shipment_fk  (cost=0.00..5.17 rows=98 width=0) (actual time=0.029..0.029 rows=106 loops=1)"
    "                                Index Cond: (shipment = t_1.shipment)"
    "Planning Time: 0.671 ms"
    "Execution Time: 0.984 ms"
    


    Переписанный запрос для работы с денормализованными данными:

    Запрос
    SELECT shipment.id, supplied.quantity
    	FROM shipment
    	JOIN (
    		SELECT customer, SUM(quantity) AS quantity
    			FROM shipmentDetail
    			JOIN shipment ON shipment.id = shipmentDetail.shipment
    			JOIN (
    				SELECT customer AS pcust
    					FROM shipment
    					WHERE shipment.date = DATEADD(day, -10, CAST(GETDATE() AS date))
    					GROUP BY customer
    			) pushcust ON pushcust.pcust = customer
    			GROUP BY customer
    	) supplied ON supplied.customer = shipment.customer
    	WHERE shipment.date = DATEADD(day, -10, CAST(GETDATE() AS date))
    


    Ну и наконец, эту технику можно использовать для оптимизации запросов в PostgreSQL. Так, пример в описании JPPD можно переписать в:

    Запрос
    SELECT balance.quantity
    	FROM product
    	JOIN
    		(SELECT product, SUM(quantity) AS quantity
    			FROM
    				(SELECT product, quantity
    				FROM receiptDetail 
    					JOIN receipt ON receiptDetail.receipt = receipt.id
    					JOIN product ON product.id = receiptDetail.product
    					WHERE product.group = 54
    				UNION ALL 
    				SELECT product, -quantity
    					FROM shipmentDetail 
    					JOIN shipment ON shipmentDetail.shipment = shipment.id
    					JOIN product ON product.id = shipmentDetail.product
    					WHERE product.group = 54
    				) details
    		 	GROUP BY product
    		) balance ON balance.product = product.id
    	WHERE product.group = 54
    


    План PostgreSQL
    "Hash Join  (cost=43596.42..43601.45 rows=1 width=32) (actual time=93.861..93.898 rows=48 loops=1)"
    "  Hash Cond: (receiptdetail.product = product.id)"
    "  ->  HashAggregate  (cost=43458.93..43461.43 rows=200 width=36) (actual time=93.685..93.707 rows=48 loops=1)"
    "        Group Key: receiptdetail.product"
    "        ->  Append  (cost=9.54..43405.03 rows=10780 width=34) (actual time=0.056..89.656 rows=10672 loops=1)"
    "              ->  Nested Loop  (cost=9.54..4448.35 rows=980 width=9) (actual time=0.056..7.524 rows=913 loops=1)"
    "                    ->  Nested Loop  (cost=9.26..4151.50 rows=980 width=13) (actual time=0.043..3.946 rows=913 loops=1)"
    "                          ->  Bitmap Heap Scan on product product_1  (cost=4.67..136.88 rows=49 width=4) (actual time=0.017..0.124 rows=48 loops=1)"
    "                                Recheck Cond: ("group" = 54)"
    "                                Heap Blocks: exact=46"
    "                                ->  Bitmap Index Scan on product_group  (cost=0.00..4.66 rows=49 width=0) (actual time=0.011..0.011 rows=48 loops=1)"
    "                                      Index Cond: ("group" = 54)"
    "                          ->  Bitmap Heap Scan on receiptdetail  (cost=4.59..81.72 rows=21 width=13) (actual time=0.017..0.072 rows=19 loops=48)"
    "                                Recheck Cond: (product = product_1.id)"
    "                                Heap Blocks: exact=910"
    "                                ->  Bitmap Index Scan on receiptdetail_product_fk  (cost=0.00..4.58 rows=21 width=0) (actual time=0.012..0.012 rows=19 loops=48)"
    "                                      Index Cond: (product = product_1.id)"
    "                    ->  Index Only Scan using receipt_pkey on receipt  (cost=0.29..0.30 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=913)"
    "                          Index Cond: (id = receiptdetail.receipt)"
    "                          Heap Fetches: 913"
    "              ->  Nested Loop  (cost=10.93..38794.98 rows=9800 width=36) (actual time=0.170..80.715 rows=9759 loops=1)"
    "                    ->  Nested Loop  (cost=10.64..35728.52 rows=9800 width=13) (actual time=0.151..35.242 rows=9759 loops=1)"
    "                          ->  Bitmap Heap Scan on product product_2  (cost=4.67..136.88 rows=49 width=4) (actual time=0.030..0.170 rows=48 loops=1)"
    "                                Recheck Cond: ("group" = 54)"
    "                                Heap Blocks: exact=46"
    "                                ->  Bitmap Index Scan on product_group  (cost=0.00..4.66 rows=49 width=0) (actual time=0.020..0.020 rows=48 loops=1)"
    "                                      Index Cond: ("group" = 54)"
    "                          ->  Bitmap Heap Scan on shipmentdetail  (cost=5.97..724.38 rows=198 width=13) (actual time=0.071..0.678 rows=203 loops=48)"
    "                                Recheck Cond: (product = product_2.id)"
    "                                Heap Blocks: exact=9745"
    "                                ->  Bitmap Index Scan on shipmentdetail_product_fk  (cost=0.00..5.92 rows=198 width=0) (actual time=0.037..0.037 rows=203 loops=48)"
    "                                      Index Cond: (product = product_2.id)"
    "                    ->  Index Only Scan using shipment_pkey on shipment  (cost=0.29..0.31 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=9759)"
    "                          Index Cond: (id = shipmentdetail.shipment)"
    "                          Heap Fetches: 9759"
    "  ->  Hash  (cost=136.88..136.88 rows=49 width=4) (actual time=0.166..0.166 rows=48 loops=1)"
    "        Buckets: 1024  Batches: 1  Memory Usage: 10kB"
    "        ->  Bitmap Heap Scan on product  (cost=4.67..136.88 rows=49 width=4) (actual time=0.041..0.151 rows=48 loops=1)"
    "              Recheck Cond: ("group" = 54)"
    "              Heap Blocks: exact=46"
    "              ->  Bitmap Index Scan on product_group  (cost=0.00..4.66 rows=49 width=0) (actual time=0.028..0.028 rows=48 loops=1)"
    "                    Index Cond: ("group" = 54)"
    "Planning Time: 1.553 ms"
    "Execution Time: 94.071 ms"
    


    Конечно, в таком случае JOIN с product будет выполнен три раза, но это куда меньшее из зол, чем расчет подзапроса для всей базы.

    Разделение логики условий на типы JOIN и WHERE


    Немногие это замечают, но логика, влияющая на то, какие записи окажутся в результирующей таблице в SQL, разделена на 2 части:

    • Тип JOIN, который определяет будут ли выбираться записи из левой таблицы, из правой таблицы, из обеих таблиц или из одной из таблиц.
    • WHERE — логическое выражение с AND, OR и NOT, которое также определяет, какие записи нужно выбрать.

    Что реально непонятно в этом разделении, так это то, почему логику JOIN не включили в WHERE, ведь типы JOIN отлично трансформируются в обычную булеву логику:

    • A INNER JOIN B соответствует IN JOIN A AND IN JOIN B
    • A LEFT JOIN B — IN JOIN A
    • A RIGHT JOIN B — IN JOIN B
    • A FULL JOIN B — IN JOIN A OR IN JOIN B

    Таким образом, запрос:

    SELECT COALESCE(X.id, D.id) 
    	FROM 
    		(SELECT A.id 
    			FROM A 
    			INNER JOIN B ON A.id = B.id 
    			LEFT JOIN C ON C.id = A.id
    		) X 
    	FULL JOIN D ON X.id = D.id
    

    Выглядел бы как:

    SELECT A.id 
    	FROM A 
    	AUTO JOIN B ON A.id = B.id 
    	AUTO JOIN C ON C.id=A.id 
    	AUTO JOIN D ON D.id=A.id 
    	WHERE (IN JOIN A AND IN JOIN B) OR IN JOIN D
    

    Хуже всего в этом разделении на самом деле дела обстоят именно с FULL JOIN, так как для результирующих колонок приходится использовать COALESCE, что сразу убивает возможность PPD оптимизаций. Поэтому на практике вместо FULL JOIN чаще используют UNION, то есть вместо:

    SELECT COALESCE(A.id, B.id) 
    FROM A 
    FULL JOIN B ON A.id=B.id
    

    Пишут:

    SELECT id FROM A 
    UNION 
    SELECT id FROM B
    

    Правда, если нам необходимо рассчитать сумму двух колонок из A и B, все становится заметно хуже (приходится делать несколько LEFT JOIN). Так, запрос:

    SELECT COALESCE(A.id, B.id), COALESCE(A.f,0)+COALESCE(B.f,0) 
    FROM A 
    FULL JOIN B ON A.id=B.id
    

    С UNION выглядит как-то так:

    SELECT A.id, A.f + COALESCE(B.f,0) 
    FROM A 
    LEFT JOIN B ON A.id = B.id
    UNION
    SELECT B.id, B.f + COALESCE(A.f,0) 
    FROM B 
    LEFT JOIN A ON A.id = B.id
    

    А если нам надо из таблиц A, B, C, D выбрать записи по условию (A.f=1 OR B.f=2) AND (C.f=3 OR D.f=4), то все становится совсем печально. Так, с FULL JOIN мы опять-таки несколькими COALESCE убьем все возможные оптимизации, а с UNION разработчику придется самому приводить условие к ДНФ и писать что-то вроде такого:

    SELECT A.id
    	FROM A 
    	JOIN C ON A.id = C.id
    	WHERE A.f=1 AND C.f=3
    UNION
    SELECT A.id
    	FROM A 
    	JOIN D ON A.id = D.id
    	WHERE A.f=1 AND D.f=4
    UNION
    SELECT B.id
    	FROM B 
    	JOIN C ON B.id = C.id
    	WHERE B.f=2 AND C.f=3
    UNION
    SELECT B.id
    	FROM B 
    	JOIN D ON B.id = D.id
    	WHERE B.f=2 AND D.f=4
    

    При этом, если нам надо будет еще вычислить какую то формулу от полей из A, B, C и D, то из-за LEFT JOIN'ов запрос вырастет еще в два раза.

    Тут, конечно, многие скажут: «что это у вас за такая странная физическая модель», но, во-первых, очень часто физическую модель не выбирают (то есть она достается как есть), а во-вторых, как мы увидим в следующем разделе, даже если все эти поля будут в одной таблице, проблемы все равно будут (правда, немного по другой причине).

    В любом случае, если бы тип JOIN задавался логическим предикатом IN JOIN, а особенно, если бы он мог выводится из условия (скажем, из A.f = 1 следует IN JOIN A), то запрос можно было писать как:

    SELECT A.id 
    	FROM A 
    	AUTO JOIN B ON A.id = B.id 
    	AUTO JOIN C ON A.id = C.id 
    	AUTO JOIN D ON A.id = D.id 
    	WHERE (A.f=1 OR B.f=2) AND (C.f=3 OR D.f=4)
    

    И дальше SQL сервер уже сам мог бы решить, как именно разбить этот запрос на UNION / FULL JOIN и какие типы JOIN подставить в полученных подзапросах. Сейчас же эту работу приходится выполнять непосредственно разработчику.

    Плохая оптимизация OR


    Вернемся к примеру из предыдущего раздела. Допустим, у нас не четыре разные таблицы A, B, C, D, а одна таблица mytable и четыре поля A, B, C, D:

    Скрипт по созданию и заполнению таблицы mytable MS SQL
    CREATE TABLE mytable (cid INT, A INT, B INT, C INT , D INT, data VARCHAR(400));
    
    WITH
    numbers ( n ) AS (
       SELECT 1 AS n
       UNION ALL
       SELECT n + 1 AS n
       FROM   numbers
       WHERE  n < 10000000
    )
    INSERT INTO mytable (cid, A, B, C, D, data)
    SELECT n, ABS(checksum(NewId())) % 100 + 1, ABS(checksum(NewId())) % 100 + 1, ABS(checksum(NewId())) % 100 + 1, ABS(checksum(NewId())) % 100 + 1, CONCAT('data',n)  FROM numbers option (maxrecursion 0);
    
    CREATE INDEX AC ON mytable(A,C);
    CREATE INDEX AD ON mytable(A,D);
    CREATE INDEX BC ON mytable(B,C);
    CREATE INDEX BD ON mytable(B,D);
    
    EXEC sp_updatestats;
    


    Скрипт по созданию и заполнению таблицы mytable Oracle
    CREATE TABLE mytable (cid INT, A INT, B INT, C INT , D INT, "data" VARCHAR(400));
    
    INSERT INTO mytable (cid, A, B, C, D, "data")
    WITH
    numbers ( n ) AS (
       SELECT 1 AS n FROM dual
       UNION ALL
       SELECT n + 1 AS n
       FROM   numbers
       WHERE  n < 10000000
    )
    SELECT n, round(dbms_random.value() * 100) + 1, round(dbms_random.value() * 100) + 1, round(dbms_random.value() * 100) + 1, round(dbms_random.value() * 100) + 1, CONCAT('data',n)  FROM numbers;
    
    CREATE INDEX AC ON mytable(A,C);
    CREATE INDEX AD ON mytable(A,D);
    CREATE INDEX BC ON mytable(B,C);
    CREATE INDEX BD ON mytable(B,D);
    
    EXEC DBMS_STATS.GATHER_DATABASE_STATS;
    


    Скрипт по созданию и заполнению таблицы mytable PostgreSQL
    CREATE TABLE mytable (cid INT, A INT, B INT, C INT , D INT, "data" VARCHAR(400));
    
    INSERT INTO mytable (cid, A, B, C, D, "data")
    WITH RECURSIVE
    numbers ( n ) AS (
       SELECT 1 AS n
       UNION ALL
       SELECT n + 1 AS n
       FROM   numbers
       WHERE  n < 10000000
    )
    SELECT n, floor(random() * 100) + 1, floor(random() * 100) + 1, floor(random() * 100) + 1, floor(random() * 100) + 1, CONCAT('data',n)  FROM numbers;
    
    CREATE INDEX AC ON mytable(A,C);
    CREATE INDEX AD ON mytable(A,D);
    CREATE INDEX BC ON mytable(B,C);
    CREATE INDEX BD ON mytable(B,D);
    
    ANALYZE;
    


    Соответственно запрос в этом случает будет выглядеть следующим образом:

    SELECT COUNT(*) FROM mytable WHERE (A=1 OR B=2) AND (C=3 OR D=4)
    

    Чтобы этот запрос выполнялся быстро, при создании таблиц мы добавили индексы по различным комбинациям этих колонок: AC, BC, AD, BD.

    CREATE INDEX AC ON mytable(A,C);
    CREATE INDEX AD ON mytable(A,D);
    CREATE INDEX BC ON mytable(B,C);
    CREATE INDEX BD ON mytable(B,D);
    

    Выполним запрос:

    План Oracle
    Среднее время выполнения: 320мс
    --------------------------------------------------------------------------------------------------------
    | Id  | Operation                            | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                     |         |       |       |       | 12436 (100)|          |
    |   1 |  SORT AGGREGATE                      |         |     1 |    16 |       |            |          |
    |*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| MYTABLE |  3882 | 62112 |       | 12436   (1)| 00:00:01 |
    |   3 |    BITMAP CONVERSION TO ROWIDS       |         |       |       |       |            |          |
    |   4 |     BITMAP OR                        |         |       |       |       |            |          |
    |   5 |      BITMAP CONVERSION FROM ROWIDS   |         |       |       |       |            |          |
    |   6 |       SORT ORDER BY                  |         |       |       |  1568K|            |          |
    |*  7 |        INDEX RANGE SCAN              | AC      |       |       |       |   241   (1)| 00:00:01 |
    |   8 |      BITMAP CONVERSION FROM ROWIDS   |         |       |       |       |            |          |
    |   9 |       SORT ORDER BY                  |         |       |       |  1568K|            |          |
    |* 10 |        INDEX RANGE SCAN              | BC      |       |       |       |   235   (1)| 00:00:01 |
    --------------------------------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       2 - filter(("C"=3 OR "D"=4))
       7 - access("A"=1)
           filter("A"=1)
      10 - access("B"=2)
           filter("B"=2)
    


    План PostgreSQL
    "Aggregate  (cost=81577.07..81577.08 rows=1 width=8) (actual time=255.927..255.928 rows=1 loops=1)"
    "  ->  Bitmap Heap Scan on mytable  (cost=3863.84..81567.12 rows=3980 width=0) (actual time=60.368..255.477 rows=3886 loops=1)"
    "        Recheck Cond: ((a = 1) OR (b = 2))"
    "        Filter: ((c = 3) OR (d = 4))"
    "        Rows Removed by Filter: 195540"
    "        Heap Blocks: exact=68751"
    "        ->  BitmapOr  (cost=3863.84..3863.84 rows=208664 width=0) (actual time=43.859..43.859 rows=0 loops=1)"
    "              ->  Bitmap Index Scan on ad  (cost=0.00..1924.43 rows=103999 width=0) (actual time=32.954..32.954 rows=100496 loops=1)"
    "                    Index Cond: (a = 1)"
    "              ->  Bitmap Index Scan on bd  (cost=0.00..1937.42 rows=104665 width=0) (actual time=10.902..10.902 rows=100014 loops=1)"
    "                    Index Cond: (b = 2)"
    "Planning Time: 0.603 ms"
    "Execution Time: 256.078 ms"
    


    Как видим, SQL сервер даже не попытался поработать с условием, а просто выполнил то, что увидел. То есть взял первую скобку, увидел в ней OR, разбил его на индексы, а вторую скобку просто применил сверху.

    Больше всех, конечно, отличился MS SQL:

    План MS SQL
    Среднее время выполнения: 1.8с
    Rows	Executes  Stmt Text
    0	0	  |--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1006],0)))
    1	1	       |--Stream Aggregate(DEFINE:([Expr1006]=Count(*)))
    3918	1	            |--Table Scan(OBJECT:([test2].[dbo].[mytable]), WHERE:(([test2].[dbo].[mytable].[A]=(1) OR [test2].[dbo].[mytable].[B]=(2)) AND ([test2].[dbo].[mytable].[C]=(3) OR [test2].[dbo].[mytable].[D]=(4))))
    


    Он решил вообще не использовать индексы. Но я проверил, если разновидностей колонок больше, MS SQL умеет строить план с индексами аналогичный Oracle и PostgreSQL, так что спишем это на особенности его настройки.

    При этом даже если раскрыть вторую скобку:

    SELECT COUNT(*) FROM mytable WHERE ((A=1 OR B=2) AND C=3) OR ((A=1 OR B=2) AND D=4)
    

    Это не помогает — планы остаются такими же.

    Единственный вариант, когда этот запрос выполнится нормально, — это преобразовать исходное условие к ДНФ:

    SELECT COUNT(*) FROM mytable WHERE (A=1 AND C=3) OR (B=2 AND C=3) OR (A=1 AND D=4) OR (B=2 AND D=4)
    

    План Oracle
    Среднее время выполнения: 30мс
    -----------------------------------------------------------------------------------------
    | Id  | Operation                        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                 |      |       |       |    18 (100)|          |
    |   1 |  SORT AGGREGATE                  |      |     1 |    16 |            |          |
    |   2 |   BITMAP CONVERSION COUNT        |      |  3010 | 48160 |    18   (0)| 00:00:01 |
    |   3 |    BITMAP OR                     |      |       |       |            |          |
    |   4 |     BITMAP CONVERSION FROM ROWIDS|      |       |       |            |          |
    |*  5 |      INDEX RANGE SCAN            | BC   |       |       |     5   (0)| 00:00:01 |
    |   6 |     BITMAP CONVERSION FROM ROWIDS|      |       |       |            |          |
    |*  7 |      INDEX RANGE SCAN            | BD   |       |       |     5   (0)| 00:00:01 |
    |   8 |     BITMAP CONVERSION FROM ROWIDS|      |       |       |            |          |
    |*  9 |      INDEX RANGE SCAN            | AC   |       |       |     4   (0)| 00:00:01 |
    |  10 |     BITMAP CONVERSION FROM ROWIDS|      |       |       |            |          |
    |* 11 |      INDEX RANGE SCAN            | AD   |       |       |     4   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       5 - access("B"=2 AND "C"=3)
       7 - access("B"=2 AND "D"=4)
       9 - access("A"=1 AND "C"=3)
      11 - access("A"=1 AND "D"=4)
    


    План MS SQL
    Среднее время выполнения: 60мс
    Rows	Executes  Stmt Text
    0	0	  |--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1006],0)))
    1	1	       |--Stream Aggregate(DEFINE:([Expr1006]=Count(*)))
    3918	1	            |--Stream Aggregate(GROUP BY:([Bmk1000]))
    3951	1	                 |--Merge Join(Concatenation)
    2986	1	                      |--Merge Join(Concatenation)
    1978	1	                      |    |--Merge Join(Concatenation)
    941	1	                      |    |    |--Index Seek(OBJECT:([test2].[dbo].[mytable].[AC]), SEEK:([test2].[dbo].[mytable].[A]=(1) AND [test2].[dbo].[mytable].[C]=(3)) ORDERED FORWARD)
    1037	1	                      |    |    |--Index Seek(OBJECT:([test2].[dbo].[mytable].[BC]), SEEK:([test2].[dbo].[mytable].[B]=(2) AND [test2].[dbo].[mytable].[C]=(3)) ORDERED FORWARD)
    1008	1	                      |    |--Index Seek(OBJECT:([test2].[dbo].[mytable].[AD]), SEEK:([test2].[dbo].[mytable].[A]=(1) AND [test2].[dbo].[mytable].[D]=(4)) ORDERED FORWARD)
    965	1	                      |--Index Seek(OBJECT:([test2].[dbo].[mytable].[BD]), SEEK:([test2].[dbo].[mytable].[B]=(2) AND [test2].[dbo].[mytable].[D]=(4)) ORDERED FORWARD)
    


    План PostgreSQL
    "Aggregate  (cost=13171.55..13171.56 rows=1 width=8) (actual time=18.442..18.443 rows=1 loops=1)"
    "  ->  Bitmap Heap Scan on mytable  (cost=93.97..13161.50 rows=4020 width=0) (actual time=3.493..17.545 rows=3886 loops=1)"
    "        Recheck Cond: (((a = 1) AND (c = 3)) OR ((b = 2) AND (c = 3)) OR ((a = 1) AND (d = 4)) OR ((b = 2) AND (d = 4)))"
    "        Heap Blocks: exact=3793"
    "        ->  BitmapOr  (cost=93.97..93.97 rows=4020 width=0) (actual time=1.773..1.774 rows=0 loops=1)"
    "              ->  Bitmap Index Scan on ac  (cost=0.00..22.76 rows=1033 width=0) (actual time=0.516..0.516 rows=993 loops=1)"
    "                    Index Cond: ((a = 1) AND (c = 3))"
    "              ->  Bitmap Index Scan on bc  (cost=0.00..22.83 rows=1040 width=0) (actual time=0.440..0.440 rows=960 loops=1)"
    "                    Index Cond: ((b = 2) AND (c = 3))"
    "              ->  Bitmap Index Scan on ad  (cost=0.00..22.14 rows=971 width=0) (actual time=0.224..0.224 rows=958 loops=1)"
    "                    Index Cond: ((a = 1) AND (d = 4))"
    "              ->  Bitmap Index Scan on bd  (cost=0.00..22.20 rows=977 width=0) (actual time=0.592..0.592 rows=1020 loops=1)"
    "                    Index Cond: ((b = 2) AND (d = 4))"
    "Planning Time: 0.296 ms"
    "Execution Time: 18.539 ms"
    


    Как можно увидеть из этих примеров, SQL сервер особо даже не пытается оптимизировать логические выражения. И это, в общем-то, понятно, так как такая оптимизация — NP-полная задача и возится с ней создателям SQL серверов, видимо, не очень хотелось. Поэтому, как и с типами JOIN, они просто решили переложить эту задачу на разработчика.

    Плохая оптимизация при работе с разреженными данными


    Теперь представим такую ситуацию. У нас есть большая таблица (shipmentdetail) и мы решили добавить туда новую колонку (sid) и индекс по ней (shipment_sd). Эта колонка по умолчанию null и заполнена для очень незначительного процента данных. Нам необходимо найти все дубликаты sid, для этого делаем следующий запрос:

    SELECT COUNT(*) FROM ShipmentDetail s1, ShipmentDetail s2 WHERE s1.sd=s2.sd AND s1.id <> s2.id
    

    План MS SQL
    Среднее время выполнения: 3.5с
    Rows	Executes  Stmt Text
    1	1	  |--Compute Scalar(DEFINE:([Expr1002]=CONVERT_IMPLICIT(int,[Expr1008],0)))
    1	1	       |--Hash Match(Aggregate, HASH:() DEFINE:([Expr1008]=COUNT(*)))
    0	1	            |--Nested Loops(Inner Join, OUTER REFERENCES:([s1].[id], [s1].[sd], [Expr1007]) WITH UNORDERED PREFETCH)
    10000001	1	                 |--Index Scan(OBJECT:([test].[dbo].[shipmentdetail].[shipmentdetail_sd] AS [s1]))
    0	10000001	                 |--Index Seek(OBJECT:([test].[dbo].[shipmentdetail].[shipmentdetail_sd] AS [s2]), SEEK:([s2].[sd]=[test].[dbo].[shipmentdetail].[sd] as [s1].[sd]),  WHERE:([test].[dbo].[shipmentdetail].[id] as [s1].[id]<>[test].[dbo].[shipmentdetail].[id] as [s2].[id]) ORDERED FORWARD)
    


    Как видим, MS SQL, когда пытается бежать по индексу shipmentdetail_sd, не догадывается, что нужно ставить фильтр на IS NOT NULL, как следствие, производительность этого запроса очень низкая.

    Если же добавить явные условия на то, что s1.sd и s2.sd IS NOT NULL картина резко улучшается:

    SELECT COUNT(*) FROM ShipmentDetail s1, ShipmentDetail s2 WHERE s1.sd=s2.sd AND s1.id <> s2.id AND s1.sd IS NOT NULL AND s2.sd IS NOT NULL
    

    План MS SQL
    Среднее время выполнения: 100мс
    Rows	Executes  Stmt Text
    0	0	  |--Compute Scalar(DEFINE:([Expr1002]=CONVERT_IMPLICIT(int,[Expr1005],0)))
    1	1	       |--Stream Aggregate(DEFINE:([Expr1005]=Count(*)))
    0	1	            |--Nested Loops(Inner Join, OUTER REFERENCES:([s1].[id], [s1].[sd]))
    100	1	                 |--Index Seek(OBJECT:([test].[dbo].[shipmentdetail].[shipmentdetail_sd] AS [s1]), SEEK:([s1].[sd] IsNotNull) ORDERED FORWARD)
    0	100	                 |--Index Seek(OBJECT:([test].[dbo].[shipmentdetail].[shipmentdetail_sd] AS [s2]), SEEK:([s2].[sd]=[test].[dbo].[shipmentdetail].[sd] as [s1].[sd]),  WHERE:([test].[dbo].[shipmentdetail].[id] as [s1].[id]<>[test].[dbo].[shipmentdetail].[id] as [s2].[id] AND [test].[dbo].[shipmentdetail].[sd] as [s2].[sd] IS NOT NULL) ORDERED FORWARD)
    


    С PostgreSQL все сложнее, базовый запрос у него работает и работает хорошо, но не потому что он добавляет фильтр, а потому что использует merge join и, видимо, умеет пропускать в нем NULL значения (тут странно, что если MS SQL добавить соответствующие хинты, это не помогает):

    План PostgreSQL
    "Aggregate  (cost=1216467.81..1216467.82 rows=1 width=8) (actual time=1.021..1.021 rows=1 loops=1)"
    "  ->  Merge Join  (cost=0.87..1216467.81 rows=1 width=0) (actual time=1.017..1.017 rows=0 loops=1)"
    "        Merge Cond: (s1.sd = s2.sd)"
    "        Join Filter: (s1.id <> s2.id)"
    "        Rows Removed by Join Filter: 100"
    "        ->  Index Scan using shipmentdetail_sd on shipmentdetail s1  (cost=0.43..583234.26 rows=9999855 width=8) (actual time=0.047..0.136 rows=101 loops=1)"
    "        ->  Index Scan using shipmentdetail_sd on shipmentdetail s2  (cost=0.43..583234.26 rows=9999855 width=8) (actual time=0.015..0.757 rows=101 loops=1)"
    "Planning Time: 0.332 ms"
    "Execution Time: 1.089 ms"
    


    Хотя, если посмотреть на estimate и cost, то видно, что PostgreSQL реально планирует пробежать по всем записям таблицы. Поэтому, если чуть-чуть изменить запрос и добавить, скажем, условие — найти дубликаты с количеством > 7

    SELECT COUNT(*) FROM shipmentDetail s1, shipmentDetail s2 WHERE s1.sd=s2.sd AND s1.id <> s2.id AND s1.quantity > 7 AND s2.quantity > 7;
    

    То получим ту же проблему, что и в MS SQL:

    План PostgreSQL
    "Aggregate  (cost=1165667.15..1165667.16 rows=1 width=8) (actual time=5203.586..5203.586 rows=1 loops=1)"
    "  ->  Merge Join  (cost=1128001.01..1165667.15 rows=1 width=0) (actual time=5203.580..5203.580 rows=0 loops=1)"
    "        Merge Cond: (s1.sd = s2.sd)"
    "        Join Filter: (s1.id <> s2.id)"
    "        Rows Removed by Join Filter: 29"
    "        ->  Sort  (cost=564000.51..571533.73 rows=3013290 width=8) (actual time=2802.648..2802.658 rows=30 loops=1)"
    "              Sort Key: s1.sd"
    "              Sort Method: external merge  Disk: 41120kB"
    "              ->  Seq Scan on shipmentdetail s1  (cost=0.00..198528.19 rows=3013290 width=8) (actual time=0.051..2193.193 rows=2999089 loops=1)"
    "                    Filter: (quantity > '7'::numeric)"
    "                    Rows Removed by Filter: 7000912"
    "        ->  Materialize  (cost=564000.51..579066.96 rows=3013290 width=8) (actual time=2400.884..2400.894 rows=30 loops=1)"
    "              ->  Sort  (cost=564000.51..571533.73 rows=3013290 width=8) (actual time=2400.876..2400.882 rows=30 loops=1)"
    "                    Sort Key: s2.sd"
    "                    Sort Method: external merge  Disk: 41120kB"
    "                    ->  Seq Scan on shipmentdetail s2  (cost=0.00..198528.19 rows=3013290 width=8) (actual time=0.033..1833.562 rows=2999089 loops=1)"
    "                          Filter: (quantity > '7'::numeric)"
    "                          Rows Removed by Filter: 7000912"
    "Planning Time: 0.398 ms"
    "Execution Time: 5233.873 ms"
    


    которая лечится все тем же добавлением явных условий на то, что s1.sd и s2.sd IS NOT NULL:

    SELECT COUNT(*) FROM shipmentDetail s1, shipmentDetail s2 WHERE s1.sd=s2.sd AND s1.id <> s2.id AND s1.quantity > 7 AND s2.quantity > 7 AND s1.sd IS NOT NULL AND s2.sd IS NOT NULL
    

    План PostgreSQL
    "Aggregate  (cost=16.93..16.94 rows=1 width=8) (actual time=4.624..4.624 rows=1 loops=1)"
    "  ->  Nested Loop  (cost=0.87..16.93 rows=1 width=0) (actual time=4.617..4.617 rows=0 loops=1)"
    "        Join Filter: ((s1.id <> s2.id) AND (s1.sd = s2.sd))"
    "        Rows Removed by Join Filter: 841"
    "        ->  Index Scan using shipmentdetail_sd on shipmentdetail s1  (cost=0.43..8.46 rows=1 width=8) (actual time=0.055..0.201 rows=29 loops=1)"
    "              Index Cond: (sd IS NOT NULL)"
    "              Filter: (quantity > '7'::numeric)"
    "              Rows Removed by Filter: 71"
    "        ->  Index Scan using shipmentdetail_sd on shipmentdetail s2  (cost=0.43..8.46 rows=1 width=8) (actual time=0.013..0.142 rows=29 loops=29)"
    "              Index Cond: (sd IS NOT NULL)"
    "              Filter: (quantity > '7'::numeric)"
    "              Rows Removed by Filter: 71"
    "Planning Time: 0.570 ms"
    "Execution Time: 4.705 ms"
    


    Oracle — единственный, кто не сплоховал в данном случае и догадался добавить такие предикаты сам:

    План Oracle
    Среднее время выполнения: 30мс
    ------------------------------------------------------------------------------------------------------------
    | Id  | Operation                              | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                       |                   |       |       |   201 (100)|          |
    |   1 |  SORT AGGREGATE                        |                   |     1 |    16 |            |          |
    |   2 |   NESTED LOOPS                         |                   |    99 |  1584 |   201   (0)| 00:00:01 |
    |   3 |    NESTED LOOPS                        |                   |   100 |  1584 |   201   (0)| 00:00:01 |
    |   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| SHIPMENTDETAIL    |   100 |   800 |   101   (0)| 00:00:01 |
    |*  5 |      INDEX FULL SCAN                   | SHIPMENTDETAIL_SD |   100 |       |     1   (0)| 00:00:01 |
    |*  6 |     INDEX RANGE SCAN                   | SHIPMENTDETAIL_SD |     1 |       |     0   (0)|          |
    |*  7 |    TABLE ACCESS BY INDEX ROWID         | SHIPMENTDETAIL    |     1 |     8 |     1   (0)| 00:00:01 |
    ------------------------------------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       5 - filter("S1"."SD" IS NOT NULL)
       6 - access("S1"."SD"="S2"."SD")
           filter("S2"."SD" IS NOT NULL)
       7 - filter("S1"."ID"<>"S2"."ID")
    


    Плохая оптимизация при работе с последними значениями


    Самые часто используемые агрегирующие функции в группирующих запросах в OLTP бизнес-приложениях — это сумма и последнее значение (например, в 1С это регистры накопления и сведений соответственно). С суммой все более-менее понятно, а вот с последним значением есть много вопросов, как в плане поддержки в SQL серверах такого функционала вообще, так и его дальнейшей оптимизации.

    Итак, допустим, мы хотим получить для товара последнюю его отгрузку. Для начала будем считать, что последнюю отгрузку надо искать по внутреннему номеру этой отгрузки (а не, скажем, по дате). Первое, что нам предложит поиск по stackoverflow — это запрос:

    SELECT MAX(shipment) AS ls FROM ShipmentDetail s WHERE s.product = 123
    

    Чтобы быстро выполнить верхний запрос, очевидно напрашивается индекс по product, shipment, и действительно, если мы его построим и выполним наш запрос, получим достаточно красивый и эффективный план выполнения (что удивительно, даже в PostgreSQL):

    План в MSSQL
    Среднее время выполнения: 70мс
    Rows	Executes  Stmt Text
    1	1	  |--Stream Aggregate(DEFINE:([Expr1001]=MAX([test].[dbo].[shipmentdetail].[shipment] as [s].[shipment])))
    1	1	       |--Top(TOP EXPRESSION:((1)))
    1	1	            |--Index Seek(OBJECT:([test].[dbo].[shipmentdetail].[shipmentdetail_p_s] AS [s]), SEEK:([s].[product]=(123)) ORDERED BACKWARD)
    


    План в Oracle
    Среднее время выполнения: 30мс
    ---------------------------------------------------------------------------------------------------
    | Id  | Operation                    | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |                    |       |       |     3 (100)|          |
    |   1 |  SORT AGGREGATE              |                    |     1 |    10 |            |          |
    |   2 |   FIRST ROW                  |                    |     1 |    10 |     3   (0)| 00:00:01 |
    |*  3 |    INDEX RANGE SCAN (MIN/MAX)| SHIPMENTDETAIL_P_S |     1 |    10 |     3   (0)| 00:00:01 |
    ---------------------------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       3 - access("S"."PRODUCT"=123)
    


    План в PostgreSQL
    "Result  (cost=4.47..4.48 rows=1 width=4) (actual time=0.173..0.174 rows=1 loops=1)"
    "  InitPlan 1 (returns $0)"
    "    ->  Limit  (cost=0.43..4.47 rows=1 width=4) (actual time=0.165..0.166 rows=1 loops=1)"
    "          ->  Index Only Scan Backward using shipmentdetail_p_s on shipmentdetail s  (cost=0.43..808.43 rows=200 width=4) (actual time=0.163..0.163 rows=1 loops=1)"
    "                Index Cond: ((product = 123) AND (shipment IS NOT NULL))"
    "                Heap Fetches: 1"
    "Planning Time: 0.949 ms"
    "Execution Time: 0.241 ms"
    


    В частности, в этом плане все SQL сервера догадываются вставить проверку, что достаточно считать ровно один ряд (FIRST ROW — Oracle, LIMIT — MS SQL и PostgreSQL).

    А теперь попробуем чуть усложнить запрос и выполнить его не для одного товара, а для множества товаров, например, всех товаров, начинающихся на Product 86 (PostgreSQL сразу выбывает из гонки, так как JPPD не поддерживает в принципе).

    Начнем с Oracle.

    SELECT SUM(cc.ls) 
            FROM Product pr
            LEFT JOIN (SELECT MAX(shipment) AS ls, s.product
                FROM shipmentDetail s
                GROUP BY s.product) cc ON cc.product=pr.id
            WHERE pr.name LIKE 'Product 86%';
    

    План в Oracle
    Среднее время выполнения: 60мс
    --------------------------------------------------------------------------------------------
    | Id  | Operation             | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |                    |       |       |   120 (100)|          |
    |   1 |  SORT AGGREGATE       |                    |     1 |    13 |            |          |
    |   2 |   VIEW                | VM_NWVW_1          |  4898 | 63674 |   120   (2)| 00:00:01 |
    |   3 |    HASH GROUP BY      |                    |  4898 |   157K|   120   (2)| 00:00:01 |
    |   4 |     NESTED LOOPS      |                    |  4898 |   157K|   118   (0)| 00:00:01 |
    |*  5 |      TABLE ACCESS FULL| PRODUCT            |    25 |   575 |    68   (0)| 00:00:01 |
    |*  6 |      INDEX RANGE SCAN | SHIPMENTDETAIL_P_S |   198 |  1980 |     2   (0)| 00:00:01 |
    --------------------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       5 - filter("PR"."NAME" LIKE 'Product 86%')
       6 - access("S"."PRODUCT"="PR"."ID")
    


    Запрос по прежнему выполняется быстро, но настораживает тот факт, что проверка на один ряд исчезла, а это очень важно, когда записей станет много.

    Ну а теперь выполним запрос для всех товаров.

    SELECT MAX(cc.ls) 
            FROM Product pr
            LEFT JOIN (SELECT MAX(shipment) AS ls, s.product
                FROM shipmentDetail s
                GROUP BY s.product) cc ON cc.product=pr.id
    

    План в Oracle
    Среднее время выполнения: 2.6с
    -------------------------------------------------------------------------------------------------------
    | Id  | Operation                | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT         |                    |       |       |       | 22222 (100)|          |
    |   1 |  SORT AGGREGATE          |                    |     1 |    31 |       |            |          |
    |   2 |   NESTED LOOPS SEMI      |                    | 50001 |  1513K|       | 22222   (2)| 00:00:01 |
    |   3 |    VIEW                  |                    | 50536 |  1283K|       | 22219   (2)| 00:00:01 |
    |   4 |     HASH GROUP BY        |                    | 50536 |   493K|   191M| 22219   (2)| 00:00:01 |
    |   5 |      INDEX FAST FULL SCAN| SHIPMENTDETAIL_P_S |    10M|    95M|       |  7871   (1)| 00:00:01 |
    |*  6 |    INDEX UNIQUE SCAN     | SYS_C007890        | 49472 |   241K|       |     0   (0)|          |
    -------------------------------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       6 - access("CC"."PRODUCT"="PR"."ID")
    


    Как видим, Oracle, не догадавшись вставить проверку на один ряд, вообще переходит на Hash Group By без JPPD (что, впрочем, логично без проверки на один ряд) и выполняет этот запрос уже несколько секунд. В то же время, если переписать этот запрос на subquery expressions с ORDER BY и rownum=1 (то есть по сути явно указав Oracle, что нужно выбирать одну запись), план становится значительно лучше:

    SELECT MAX(l) FROM (SELECT (SELECT shipment FROM (SELECT shipment FROM ShipmentDetail s WHERE s.product = pr.id ORDER BY s.shipment DESC) s WHERE rownum = 1) AS l FROM product pr)
    

    План в Oracle
    Среднее время выполнения: 300мс
    ----------------------------------------------------------------------------------------------------
    | Id  | Operation                     | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT              |                    |       |       |   139K(100)|          |
    |*  1 |  COUNT STOPKEY                |                    |       |       |            |          |
    |   2 |   VIEW                        |                    |     2 |    26 |     3   (0)| 00:00:01 |
    |*  3 |    INDEX RANGE SCAN DESCENDING| SHIPMENTDETAIL_P_S |   198 |  1980 |     3   (0)| 00:00:01 |
    |   4 |  SORT AGGREGATE               |                    |     1 |     5 |            |          |
    |   5 |   INDEX FAST FULL SCAN        | SYS_C007890        | 50001 |   244K|    27   (0)| 00:00:01 |
    ----------------------------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       1 - filter(ROWNUM=1)
       3 - access("S"."PRODUCT"=:B1)
    


    И запрос выполняется за несколько сотен миллисекунд, то есть в 10 раз быстрее. Почему Oracle по разному оптимизируют запросы, когда у него в предикате равенства константа задана явно и когда она приходит из JPPD — загадка. Но на практике разработчику для нормальной производительности придется вставлять вот такие вот костыли как в запросе выше. Причем, если в базе нет нужного индекса или база «пустая» (то есть когда отгрузок еще нет, а товары есть и их много), запрос с такими костылями будет выполняться гораздо хуже, по сравнению с базовым запросом и тем, как его выполняет Oracle. То есть по хорошему такая оптимизация должна быть решением самой СУБД, а не разработчика, который может не знать ни статистики, ни того, какие индексы есть в системе.

    Что касается MS SQL, то если в прошлом разделе у MS SQL была проблема, а у Oracle нет, то здесь все наоборот. MS SQL догадывается вставлять Top 1 в план:

    План MS SQL
    Среднее время выполнения: 300мс
    Rows	Executes  Stmt Text
    1	1	  |--Hash Match(Aggregate, HASH:() DEFINE:([Expr1005]=MAX([Expr1003])))
    50001	1	       |--Nested Loops(Left Outer Join, OUTER REFERENCES:([pr].[id]))
    50001	1	            |--Index Scan(OBJECT:([test].[dbo].[product].[product_group] AS [pr]))
    0	0	            |--Compute Scalar(DEFINE:([Expr1003]=[test].[dbo].[shipmentdetail].[shipment] as [s].[shipment]))
    50000	50001	                 |--Top(TOP EXPRESSION:((1)))
    50000	50001	                      |--Index Seek(OBJECT:([test].[dbo].[shipmentdetail].[shipmentdetail_p_s] AS [s]), SEEK:([s].[product]=[test].[dbo].[product].[id] as [pr].[id]) ORDERED BACKWARD)
    


    И выполняет этот запрос так как надо. Правда, если заменить JOIN с LEFT на INNER, то Top 1 магическим образом пропадает (хотя непонятно в чем разница, и зачем SQL серверу может понадобится больше чем одна запись), и мы имеем ту же проблему, что и в Oracle:

    План MS SQL
    Среднее время выполнения: 2.8с
    Rows	Executes  Stmt Text
    1	1	  |--Hash Match(Aggregate, HASH:() DEFINE:([Expr1003]=MAX([Expr1002])))
    50000	1	       |--Stream Aggregate(GROUP BY:([s].[product]) DEFINE:([Expr1002]=MAX([test].[dbo].[shipmentdetail].[shipment] as [s].[shipment])))
    10000001	1	            |--Index Scan(OBJECT:([test].[dbo].[shipmentdetail].[shipmentdetail_p_s] AS [s]), ORDERED FORWARD)
    


    То есть фактически в MS SQL все INNER JOIN подзапросов с MAX необходимо преобразовывать в LEFT JOIN. Но это все же меньшее из зол по сравнению с Oracle (где, как мы видели, все JOIN подзапросов с MAX надо преобразовывать в subquery expression с rownum=1).

    В любом случае, несмотря на описанные выше оптимизации, у подхода с MAX / MIN есть два очень существенных недостатка. А именно, непонятно что делать:

    • когда нужно вернуть не последнее значение, а другое поле записи с последним значением (скажем поставку последнюю по дате)
    • когда нужно найти последнюю запись сразу по нескольким полям: то есть сначала по одному полю, а если это поле совпадает, то по второму (например по дате, а уже потом по внутреннему идентификатору).

    В этом случае есть два варианта:

    • использовать оконные функции, как предлагает нам тот же stackoverflow, что уже не очень удобно, когда надо получить значения именно для группы
    • создать custom aggregate функцию LAST, что тоже не очень удобно, особенно в том же MS SQL, где это надо делать на C#, с необходимостью последующей компиляции созданного файла перед его загрузкой на сервер (но это было давно, возможно, в последних версиях в этом плане что-то изменилось)

    Но самое главное, что у обоих подходов будут большие проблемы с производительностью. Так, для оконных функций JPPD не поддерживается в принципе. А с custom aggregate функциями не будет использоваться описанная выше оптимизация FIRST ROW / LIMIT 1. Конечно, проблема ее отсутствия может показаться несколько преувеличенной, но на самом деле ее нельзя недооценивать. Эта проблема становится особенно важной по мере роста количества данных. То есть после месяца работы системы все может работать хорошо, но когда пройдет пять-десять лет, при выполнении запроса вычисления последнего значения каких-нибудь часто изменяющихся данных (например приходов какой-нибудь позиции популярного молока, которая приходит каждый день) СУБД придется обращаться к очень старым данным, что, во-первых, создаст дополнительную нагрузку не только на процессор, но и на СХД, а во-вторых, может приводить к ротации кэшей данных на SQL сервере, а это, в свою очередь, может значительно повлиять на общую производительность системы.

    Проблема N+1


    Есть распространенное мнение, что если система написана на SQL (а точнее его расширениях PL/SQL, T-SQL и т.п.), то она автоматически не имеет проблемы с многократным выполнением одних и тех же запросов, но с разными параметрами. А это, скажем так, не совсем верно. По большому счету в расширениях SQL проблема N+1 не сильно отличается от аналогичной проблемы в тех же ORM-фреймворках.

    Например, у нас есть хранимая процедура:

       --Создаем процедуру
       CREATE PROCEDURE TestProcedure 
       (
            --Входящие параметры
            @CategoryId INT,
            @ProductName VARCHAR(100),
            @Price MONEY = 0
       )
       AS
       BEGIN
            --Инструкции, реализующие Ваш алгоритм
            
            --Обработка входящих параметров
            --Удаление лишних пробелов в начале и в конце текстовой строки
            SET @ProductName = LTRIM(RTRIM(@ProductName));
            
            --Добавляем новую запись
            INSERT INTO TestTable(CategoryId, ProductName, Price)
                    VALUES (@CategoryId, @ProductName, @Price)
    
            --Возвращаем данные
            SELECT * FROM TestTable
            WHERE CategoryId = @CategoryId
       END
    
       GO
    

    А теперь нам нужно выполнить эту процедуру для 1000 записей. И тут у нас два варианта:

    • запустить хранимую процедуру в цикле и получить 1000 INSERT и 1000 SELECT.
    • сделать еще одну процедуру, но с использованием временных таблиц / табличных переменных и тем самым потратить на это драгоценное время разработчика, а заодно нарастить технический долг, нарушив DRY принцип.

    Понятно, что можно все хранимые процедуры проектировать таким образом, что все параметры будут передаваться во временных таблицах / табличных переменных, но это, мягко говоря, не сильно удобно.

    Однако если с хранимыми процедурами еще есть какой-то workaround, то с триггерами во многих SQL серверах все еще хуже. Здесь мы не знаем, когда триггер будет вызван и, соответственно, переписывать нечего. Теоретически для решения проблемы N+1 в SQL серверах есть триггеры per statement (в противовес per row), но:

    • В Oracle и PostgreSQL в них нельзя обращаться к новым и предыдущим значениям, то есть такие триггеры бесполезны чуть меньше чем полностью.
    • В MS SQL в per statement триггерах есть виртуальные таблицы inserted и deleted, где можно узнать, что именно изменилось. Но с точки зрения проблемы N+1, это поможет, только если изменения первоначально будут выполняться одной командой.

    Хотя, справедливости ради, если в MS SQL все триггеры делать с использованием inserted и deleted, и везде следовать правилу, что все изменения одной таблицы должны выполняться ровно одной командой (правда, непонятно, как это можно обеспечить в контексте описанной выше проблемы N+1 с хранимыми процедурами), то можно практически полностью избавиться от проблемы N+1 и обеспечить поддержку всех существующих в системе бизнес-правил минимальным количеством SQL-запросов. И в этом смысле MS SQL дает фору Oracle (честно говоря, я так и не понял, в чем проблема у Oracle была поддержать аналогичные виртуальные таблицы).

    UPD: Еще одна подсказка из зала, в PostgreSQL начиная с 10 версии есть так называемые transition таблицы (new_table и old_table), функционал которых аналогичен inserted и deleted в MS SQL.

    Высокая цена ошибки при проектировании БД


    Если показать большинство описанных выше проблем разработчику БД, первое, что вы, скорее всего, услышите в ответ будет: «да у вас неправильная модель БД». Причем будет предполагаться, что эта «неправильная модель» одновременно и причина, и средство решения всех ваших проблем.

    Если посмотреть на «неправильную модель» как на причину всех проблем, то тут вообще непонятно использование термина «неправильная». Обычно при создании системы невозможно предугадать не то что, какая там будет статистика, а какой в принципе будет функционал этой системы через пять-десять лет. Постоянно меняться — одна из ключевых особенностей любого бизнеса (особенно на конкурентных рынках), а вместе с самим бизнесом необходимо изменять и его ИТ-системы (как зеркало этого бизнеса). И то, что было правильно сначала, может быстро стать неправильным потом. Так что, если кто-то вас упрекнет в том, что вы неправильно спроектировали БД, можете смело кидать в этого человека камень. Уверен, что при изменении требований к его БД нужным образом (а это неизбежно) его модель тоже будет неправильной.

    С «неправильной моделью» как средством решения проблем все еще сложнее. Как мы увидели в первом разделе, нормализация / денормализация БД в современных SQL-серверах — не такой уж простой и прозрачный процесс. От слова совсем. Но даже если вам надо просто переместить несколько полей из разных таблиц в одну таблицу или наоборот разложить некоторые поля одной таблицы по разным таблицам, вы, скорее всего, столкнетесь с не меньшими трудностями и вам, возможно, придется переписать довольно значительное число запросов, как записи, так и чтения. Тут, конечно, вам на помощь, скорее всего, придут все те же представления, но, как мы видели в остальных разделах, представления поддерживают далеко не все то, что поддерживают таблицы (в частности, ограничения, триггеры и индексы), плюс имеют проблемы с производительностью при использовании FULL JOIN и т.п. Поэтому так просто взять и заменить таблицу на представление, скорее всего, не получится.

    Так что, как говорится, работа разработчика БД и опасна и трудна, и требует не только хорошее аналитическое мышление, но и развитые экстрасенсорные способности, так как любая ошибка в проектировании БД может впоследствии очень дорого стоить.

    Непредсказуемая оптимизация при работе с большим количеством JOIN


    В современных SQL серверах для построения планов запросов (в частности, определения порядков JOIN) используется так называемый Cost-Based Optimizer (CBO). Часто его преподносят как очень сложный и умный механизм, и он действительно внутри учитывает огромное количество информации, от индексов по функциям до партиционирования таблиц и индексов, но именно это количество информации и играет с ним злую шутку — алгоритмически это не более чем обычный перебор. А значит, как и в любом переборе, сложность работы CBO растет экспоненциально от количества join'ов. Более того, так как это перебор перестановок, а не подмножеств, сложность у этого перебора вообще космическая — O(n!). То есть даже для двенадцати join'ов вариантов их перестановок будет около 48 миллионов. Понятно, что у алгоритма поиска планов есть отсечения, но с таким количеством вариантов даже они не помогут. Поэтому почти все SQL сервера при большом количестве join'ов переходят на различные эвристики. И вот тут у некоторых SQL серверов начинаются проблемы. Так, в PostgreSQL GEQO алгоритм умудряется пропускать чересчур очевидные варианты. К примеру, в запросе может быть одна единственная маленькая таблица, которая находится в запросе на расстоянии больше 8 join'ов от таблицы, с которой у нее общее условие, и при этом в результирующем плане она будет соединяться в самом конце.

    Вообще, опыт показал, что при работе с большим количеством join эффективнее всего следующий подход: жадняком с минимальным lookahead'ом определить самый очевидный порядок join, после чего перебор в cost-based оптимизации начинать именно с этого порядка join (а не порядка join, заданного в запросе). Возможно, MS SQL и Oracle так и делают, но найти какую-то конкретную информацию по используемым ими эвристикам очень тяжело (в отличии от PostgreSQL). Везде написано что-то в стиле:
    This heuristic uses sophisticated methods for instantaneously finding particular
    plans in the search space which are likely to be nearly optimal or, at least, very
    good execution plans.
    Вживую, к сожалению, протестировать эти сценарии в MS SQL и Oracle пока не удалось (так как эти сценарии требуют одновременно и большое количество данных, и сложную логику), но в будущем, я надеюсь, это все-таки удастся сделать и тогда я дополню статью этой информацией.

    Отсутствие наследования и полиморфизма


    Наследование и полиморфизм (здесь и далее речь пойдет о subtype полиморфизме) появились задолго до появления SQL и, за счет возможности эффективно декомпозировать задачи, а также не наращивать технический долг по мере роста сложности системы, совершили небольшую революцию в программировании, позволив создавать системы значительно более сложные, чем существовавшие ранее (такую же революцию в программировании, кстати, в свое время совершило абстрагирование, дав миру, в частности, структурное программирование).

    И если в простом проекте польза этих двух механизмов не настолько очевидна, то по мере роста этого проекта, именно наследование и полиморфизм являются одними из основных барьеров от превращения его кода в один большой «спагетти-код».

    Посмотрим, что в этом плане умеют современные SQL сервера.

    У MS SQL все просто — они даже не пытались поддержать ни наследование, ни тем более полиморфизм.

    В PostgreSQL формально наследование таблиц есть, но не более того. А учитывая, что смысла в наследовании без полиморфизма нет практически никакого, зачем в PostgreSQL наследование вообще добавляли — неясно.

    Вообще, если провести аналогию со структурным программированием, полиморфизм в SQL, по идее, должен был выглядеть как возможность создания абстрактного представления, в который можно добавлять различные UNION'ы в качестве реализации, то есть что-то вроде:

    CREATE ABSTRACT VIEW detail (document LONG, quantity NUMBER);
    
    EXTEND VIEW Detail
    	SELECT receipt AS document, quantity FROM receiptDetail;
    …
    EXTEND VIEW X
    	SELECT shipment AS document, quantity FROM shipmentDetail;
    

    Но такой возможности ни в PostgreSQL, ни в других SQL серверах нет.

    В Oracle решили скрестить «ежа с ужом»:

    • добавить в язык классическое ООП с классами, объектами и инкапсуляцией
    • сделать возможность создавать таблицы как списки объектов заданного класса.

    Получилось, на мой взгляд, очень неоднозначно. Во-первых, в методах (MEMBER FUNCTION / PROCEDURE) мы имеем проблему N+1. Во-вторых, непонятно, что делать с таблицами с несколькими ключами (например, Товар и Склад). Можно, конечно, создавать суррогатные классы вроде ТоварСклад, но тогда непонятно, что делать с их наследованием. Создавать классы ТоварМагазин и ТоварРЦ? Тогда с классами в отсутствии generics вообще неразбериха получится, так как при связывании Магазин и ТоварСклад, должен получиться ТоварМагазин, а как об этом должен узнать SQL сервер — неясно. В общем, в получившемся механизме вопросов больше, чем ответов. Как следствие, с типами и их наследованием в Oracle ситуация примерно такая же, как и с материализованными представлениями — много кто слышал, но использовали на практике очень немногие и то в частных случаях.

    Избыточно низкий уровень абстрагирования


    Как известно, SQL — язык реляционной алгебры, то есть язык работы с таблицами. При этом абсолютное большинство таблиц в БД, как правило, находятся во второй нормальной форме (имеют ключи), а значит, любую таблицу можно рассматривать как множество функций, то есть отображений ключей (параметров функции) на колонки (значения функции). При этом все операции над таблицами все равно выполняются в реляционной алгебре, то есть при помощи операций соединения и объединения, создавая тем самым дополнительную ненужную сложность. К примеру, операция соединения — это декартово произведение двух таблиц, что, скажем так, не слишком очевидно для обычного человека (во всяком случае, в жизни люди с такой операцией практически не сталкиваются). И я много раз пытался объяснять людям, не имевшим опыта работы с SQL, эту операцию формально, но безуспешно. В итоге все сводилось к «обезьянному методу» обучения: надо получить такую информацию — используй вот такой запрос, другую информацию — другой запрос и так далее. Впрочем, справедливости ради, даже если меня спросить что будет, если сделать LEFT JOIN таблицы и указать в условии соединения не все ключи, мне придется серьезно поломать мозг. То есть построить таблицу я смогу, но логически для меня это будут бессмысленные данные. Как говорилось в одном культовом сериале: «She understands, she doesn't comprehend». А у меня опыт работы с SQL, скажем так, не самый маленький, мне приходилось работать с настолько сложными запросами, что они приводили к багам в PostgreSQL, причем таким, что меня даже лично упоминали в Release Notes (потому как запросы были просто из join'ов и =, то есть баг центральнее некуда)

    Альтернативой, позволяющей решить проблему избыточной сложности, было бы использование в SQL не реляционной алгебры — с таблицами, соединениями, а функциональной — с функциями и композициями. Впрочем, эта тема уже подробно разбиралась в отдельной статье, поэтому здесь я приведу лишь пример задачи с 2* (для наглядной демонстрации различия этих двух подходов)

    Функциональная алгебра

    SELECT Person a, Person b, Person c WHERE 
        likes(a, c) AND NOT friends(a, c) AND 
        (friends(a, b) OR friends(b, a)) AND 
        (friends(b, c) OR friends(c, b));
    

    Реляционная алгебра

    WITH PersonRelationShipCollapsed AS (
      SELECT pl.PersonAID
            ,pl.PersonBID
            ,pl.Relation 
      FROM #PersonRelationShip      AS pl 
      
      UNION 
    
      SELECT pl.PersonBID AS PersonAID
            ,pl.PersonAID AS PersonBID
            ,pl.Relation
      FROM #PersonRelationShip      AS pl 
    )
    SELECT 
       pl.PersonAID
      ,pf.PersonBID
      ,pff.PersonBID
    FROM #Persons                      AS p
    --Лайки                      
    JOIN PersonRelationShipCollapsed  AS pl ON pl.PersonAID = p.PersonID
                                     AND pl.Relation  = 'Like'                                  
    --Друзья                          
    JOIN PersonRelationShipCollapsed  AS pf ON pf.PersonAID = p.PersonID 
                                     AND pf.Relation = 'Friend'
    --Друзья Друзей                   
    JOIN PersonRelationShipCollapsed  AS pff ON pff.PersonAID = pf.PersonBID
                                     AND pff.PersonBID = pl.PersonBID
                                     AND pff.Relation = 'Friend'
    --Ещё не дружат                   
    LEFT JOIN PersonRelationShipCollapsed AS pnf ON pnf.PersonAID = p.PersonID
                                       AND pnf.PersonBID = pff.PersonBID
                                       AND pnf.Relation = 'Friend'
    WHERE pnf.[PersonAID] IS NULL 
    

    А уже читатель сам может решить, какой из этих подходов проще.

    Адаптивная оптимизация(AO)


    Одним из ключевых условий построения эффективных планов выполнения является правильная оценка статистики результатов соединения таблиц / подзапросов. Так, если в обеих соединяемых таблицах мало записей, или их мало в одной из таблиц, а во второй есть индекс по условию соединения, для соединения этих таблиц можно (и нужно) использовать цикл (nested loop join), в остальных случаях, как правило, эффективнее использовать hash или merge join. Но что будет, если планировщик думал, что записей мало, а их на самом деле оказалось много? В общем-то, ничего хорошего. Причем самые разрушительные последствия будут в случае, если планировщик думал что в обеих таблицах мало записей, а оказалось что в каждой из них, к примеру по 10к записей. В этом случае в процессе выполнения запроса SQL серверу придется пробежаться по 100млн записей (или даже построить такую таблицу), что создаст большую нагрузку как на процессор (особенно если включен параллелизм), так и на память (в том числе постоянную, то есть СХД). А ошибаться со статистикой SQL сервера могут во многих случаях, самыми распространенными из которых являются неравномерная статистика и корреляция данных. Конечно с этими явлениями SQL сервера борются как могут (так, к примеру, они хранят наиболее часто встречающиеся значения, cross-column статистику, всяческие гистограммы и т.п.), но даже со всеми этими техниками при наличии в запросе большого количества таблиц вероятность ошибки в статистике все равно достаточно высока.

    Чтобы сделать сервер более устойчивым к таким ошибкам, SQL сервера используют следующую технику: если прогнозируемое количество записей меньше некоторого порога, и, по идее, должен был бы использоваться nested loop join, они вставляют в план альтернативную ветку (так называемый adaptive join), которая активируется, если реальное количество записей значительно превысит прогнозируемое. В частности, такая техника позволяет исключить упомянутый выше самый «разрушительный» сценарий — соединения двух огромных таблиц при помощи nested loop join. Но, как и у JPPD, у AO есть ряд недостатков.

    AO: Поддерживаются только в коммерческих СУБД


    Этот механизм не поддерживается в PostgreSQL. Собственно, даже в MSSQL его поддержка появилась всего 2 года назад, поэтому ожидать его появление в PostgreSQL в ближайшее время не приходится. Усугубляет ситуацию еще то, что PostgreSQL редкостный оптимист. Так, если PostgreSQL не знает selectivity, то считает ее равной 0.3 (там реально в коде такая константа захардкожена), плюс, если он ничего не знает про два условия соединения, то считает их некоррелированными и просто перемножает selectivity. Как следствие, он очень часто думает, что в промежуточной таблице будет одна запись, и если, например, запрос содержит несколько подзапросов (информация о которых как раз часто попадает в класс «неизвестной»), стреляет себе в ногу с завидной регулярностью.

    С другой стороны, с учетом отсутствия JPPD и непредсказуемостью GEQO, возможно изначально и не планировалось, что PostgreSQL должен эффективно работать со сложными запросами. Так что отсутствие AO, как и излишний оптимизм отлично вписываются в эту парадигму.

    AO: Вероятность ошибки все равно достаточно высока


    Описанная техника адаптивных join'ов устраняет крайние случаи, но все равно обладает определенными уязвимостями:
    This means that a threshold depends on the estimates, so accurate estimates are still important.
    То есть если оптимизатор ошибется не в нижнюю, а в верхнюю сторону, то адаптивный join не создастся, а значит SQL сервер, к примеру, может не использовать индекс, когда это было нужно.

    Также при такой схеме возможна ситуация, когда join с ошибочно маленькой статистикой будет неправильно поставлен в начало, и тем самым воспрепятствует попаданию в начало «правильного» join. При этом, когда SQL сервер обнаружит ошибку, будет уже поздно:
    Adaptive plans do not allow the join order to be altered, so even if a better join method or parallel distribution method is used, the execution plan may still be sub-optimal.
    Для хотя бы частичного решения этих проблем в Oracle помимо adaptive join также поддерживается так называемая адаптивная статистика (adaptive statistics). Она позволяет собирать для выполняемого запроса реальную статистику каждого join, а затем использовать ее при следующем выполнении этого же запроса, при необходимости перепланировав его. Впрочем, у этого механизма тоже есть ряд проблем:

    • Он включается только при втором выполнении запроса (при этом, если в запросе будет, к примеру, использована другая временная таблица, то он будет считаться другим запросом, а значит, весь этот механизм не сработает)
    • Так как нужно собирать дополнительную статистику для каждого выполняющегося в системе запроса (а с 99% из них обычно проблем нет), у него достаточно большой оверхед. Причем настолько большой, что сам Oracle по умолчанию его выключает и вообще не рекомендует использовать в OLTP системах.
      The reasoning for this is many of these optimizations are more appropriate for data warehousing, where there optimization time is is a small proportion of the query runtime. In OLTP environments, where SQL runtime is typically smaller, the additional optimization time may become a significant part of the elapsed time, for little extra benefit.

    В то же время есть более простая и во многом даже более эффективная техника адаптивной оптимизации. Ее смысл заключается в том, чтобы определить общее предполагаемое время выполнения запроса (с определенным запасом), и если оно будет превышено, отменить запрос, материализовать в нем часть подзапросов (скорингом определив наиболее подходящие), а затем попытаться выполнить этот запрос еще раз. Так как подзапросы будут сохранены во временные таблицы и по ним будет достаточно подробная статистика, оптимизатору будет гораздо проще построить эффективный план по сравнению с базовым запросом. Естественно, всю информацию о новом времени выполнения, материализация каких подзапросов помогла быстро выполнить запрос и т.п. необходимо кэшировать и использовать при последующих выполнениях.

    Тут, правда, надо сказать, что такой подход требует выполнения нескольких дополнительных условий:

    • Перед материализацией подзапроса для него нужно выполнить «JPPD в общем случае» (то есть со сбором проталкиваемых предикатов верхнего запроса, их группировкой и так далее), иначе подзапрос материализуется для всей базы, а не только для тех данных, которые нужны верхнему запросу. Соответственно, необходимо как минимум поддерживать возможность такого JPPD.
    • Архитектура системы должна поддерживать отмену запроса, а значит и, возможно, всей транзакции, с последующим ее перезапуском. Впрочем, поддержка такой операции нужна, в том числе, для работы с update conflict и dead lock, так что реализовывать ее все равно придется (если вам, конечно, не хочется мучаться с ручными блокировками, но об этом в следующей статье).

    Отметим, что описанная выше техника позволяет уточнять статистику именно подзапросов. Ее, конечно, можно пытаться использовать и для уточнения статистики промежуточных результатов соединения join, но это будет существенно сложнее и менее надежно. Тем более, что чаще всего SQL сервера, ошибаются именно в статистике подзапросов, и, как показала практика, материализация подзапросов, как механизм адаптивной оптимизации, работает и работает очень даже хорошо. С другой стороны, в связке с адаптивными join'ами этот механизм работает еще лучше, а лучшее, как известно, враг хорошего.

    Заключение


    Подводя итог, коммерческие SQL сервера поддерживают достаточно много сложного функционала и оптимизаций, но практически всегда это делают в таком виде, что использовать эти возможности на практике — себе дороже. Выстрелить в ногу можно практически на каждом шагу, а использование некоторых оптимизаций напоминает анекдот про установку вируса под Linux — там где эти оптимизации должны подстраховывать разработчика, нужно наоборот заставлять их работать.

    PostgreSQL же не поддерживает даже базовые возможности по оптимизации (в частности JPPD), поэтому использовать его «как есть» для разработки основной части бизнес-логики, как это часто делают с MS SQL и Oracle, затея, скажем прямо, весьма трудоемкая и рискованная. Еще более забавно, когда кто-то заявляет о кроссплатформенности по СУБД (включая туда PostgreSQL), потому как тут два варианта:

    • СУБД используется просто как подложка под ORM, и тогда да, фиолетово, какая СУБД будет, система будет тормозить на больших объемах на любой из них.
    • на PostgreSQL все работает, пока там в каждой таблице до 100к записей, а когда данных становится больше, заказчику говорится: “ну а что вы хотели от бесплатной СУБД, покупайте MS SQL или Oracle”. Из-за этого, кстати, сформировался набор достаточно забавных стереотипов. Например, у нас была ситуация, когда мы переводили одного заказчика с Oracle на PostgreSQL и у нас требования к серверу были меньше чем у них было с Oracle (при гораздо большем функционале). И этот заказчик нас еще долго мучал вопросом: «Как это? Вы же бесплатный PostgreSQL предлагаете использовать, а значит нужен сервер раз в пять мощнее»

    И это все, если смотреть на проблему с позиции разработчика. С точки зрения DBA все еще хуже — изменять физическую модель (то есть нормализовать / денормализовать данные, переносить данные между таблицами) самостоятельно он не может. Максимум, что может DBA — это достроить дополнительные индексы, в остальном ему нужно обращаться к разработчику, а, учитывая, что у последнего другие KPI и вообще «он все правильно написал, а это ваш MS SQL/Oracle тормозит, настройте его как-нибудь» ситуация очень быстро становится тупиковой. И в лучшем случае приводит к скандалу, а в худшем — к смене / переписыванию всей системы.

    Здесь конечно может возникнуть вопрос, к чему вообще эта статья, критиковать может любой дурак. И, если честно, я не писал бы эту статью, если бы не знал, что все описанные проблемы можно решить. Да, непросто, я бы даже сказал, очень и очень непросто, но ведь за коммерческими СУБД стоят одни из крупнейших в мире корпораций. И у них вроде как работают «лучшие в мире инженеры». Поэтому, когда приходится решать проблемы за них, невольно ловишь себя на мысли, что «что-то здесь не так». Но оставим это на их совести.

    А теперь, как говорится, минутка рекламы. Все вышеизложенные проблемы удалось решить в lsFusion, причем сделать это в общем случае. Прозрачная материализация (денормализация) данных, ограничения и триггеры на любые вычисляемые данные, JPPD в общем случае, динамическая физическая модель, компактный и одновременно быстрый оптимизатор булевой логики, компиляция циклов в запросы и наоборот, оптимизация работы с разреженными данными и последними значениями, множественные наследование и полиморфизм — и это только вершина айсберга. Помимо всего этого есть еще логика представлений и множество других чисто языковых возможностей, но это все к SQL напрямую не относится и будет рассмотрено в следующей статье.

    Нас, кстати, постоянно упрекают, почему мы lsFusion сравниваем в том числе с DBMS, и, я надеюсь, эта статья дала хотя бы часть ответов на этот вопрос. Да, последнюю милю (определение типов выполнения JOIN — loop/hash/merge, поддержку ACID и т.п.) lsFusion не закрывает и использует для этого RDBMS, но по большому счету это особенности реализации lsFusion, разработчик этого не видит (если не хочет, конечно). Плюс, важным моментом является то, что, так как ответственность за оптимизацию всех запросов лежит на lsFusion, в качестве RDBMS можно использовать самую примитивную из них — PostgreSQL. И в этом смысле lsFusion можно рассматривать как своеобразный костюм железного человека для PostgreSQL, дающий ему «суперспособности» MS SQL и Oracle, во всяком случае, в плане оптимизации запросов. При этом, если последние в нормальных редакциях стоят по 14 тысяч долларов за ядро, то связка lsFusion + PostgreSQL абсолютно бесплатна. Впрочем, у этого факта есть и обратная сторона, мы так и не убедили ни одного заказчика поставить себе MS SQL или Oracle в промышленную эксплуатацию, поэтому многие вещи в коммерческих СУБД проверялись исключительно на тестовых стендах, а значит, вполне возможно, в статье могут быть некоторые ошибки. Так что если кто-нибудь где-нибудь такую ошибку найдет, просьба написать про нее в комментариях, и мы ее обязательно исправим.
    lsFusion
    106,36
    Не очередной язык программирования
    Поделиться публикацией

    Комментарии 176

      +1
      Начал читать, дошел до этого пункта и возникли вопросы.
      View: В параметризованные представления во FROM можно передавать только константы

      SELECT shipmentDetail.id, b.quantity
      	FROM shipmentDetail 
      	JOIN shipment ON shipmentDetail.shipment = shipment.id
      	JOIN balance(shipment.date) b ON b.stock = shipment.stock AND b.product = shipmentDetail.product 
      	WHERE shipmentDetail.quantity = 5

      Это можно реализовать на MS SQL с использованием CROSS APPLY (нет блока ON придется функцию с 3 параметрами делать), на PostgreSQL LATERAL в помощь.
      Дальше читать не стал, может быть большая часть и имеет место быть.
        0
        Спасибо, что-то про CROSS APPLY с LATERAL я забыл написать, сейчас дополню статью. Хотя с CROSS APPLY все равно проблемы будут, потому как если вы сделаете функцию от трех параметров, как тогда скажем вернуть остатки на дату по всем товарам / складам? То есть:
        SELECT FROM balance('01.01.2019')

        Получается что это уже не виртуальная таблица (представление), а именно функция будет.
          0
          Как вариант — передавать в качестве товара / склада NULL и проверять xxx = xxx or xxx is null.
            0
            Я слабо представляю, как при этом надо будет переписать balance. А еще непонятнее как это все будет выполняться, учитывая что СУБД все эти OR'ы и IS NULL / IS NOT NULL не очень то любят (только в этой статье два примера есть).
              0
              Тут все правильно сказано, на Null проверяется не поле а переменная, с индексами все ок будет. Но есть одно но, не знаю не тестировал с функциями, но с процедурами обязательно WITH RECOMPILE, если переменная может быть Null, т.к. план на 1 значение переменной и на весь диапазон будет абсолютно противоволожный (Scan vs Seek).
                +1
                Мне лень лезть в структуру таблиц, выглядеть будет примерно так
                CREATE FUNCTION balance (
                    @date DATE
                	, @stockid INT
                	, @productid INT
                )
                RETURNS TABLE
                AS
                RETURN
                SELECT stock, product, SUM(quantity) AS quantity
                	FROM
                		(SELECT receipt.stock, product, quantity
                		FROM receiptDetail 
                			JOIN receipt ON receiptDetail.receipt = receipt.id
                			WHERE receipt.date < @date and (receipt.stock = @stockid or @stockid is null) and (product = @productid or @productid is null)
                		UNION ALL 
                		SELECT shipment.stock, product, -quantity
                			FROM shipmentDetail 
                			JOIN shipment ON shipmentDetail.shipment = shipment.id
                			WHERE receipt.date < @date and (shiopment.stock = @stockid or @stockid is null) and (product = @productid or @productid is null)
                		) details
                	GROUP BY stock, product

                В SQL Server эти OR'ы не помешают использованию индекса, он поймёт что от него хотят.
                Вообще, меня удивил выбор СУБД — SQL Server 2019 — это даже не RTM, его ещё пилят во всю.
                  0
                  В принципе вы правы с параметрами IS NULL он разберется наверное.

                  Но тут конечно нюанс не начнет ли он CROSS APPLY безусловно nested loop'ом выполнять.

                  То есть если вы сделаете:

                  SELECT FROM Product pr CROSS APPLY balance('01.01.2019',pr.id,NULL).

                  Не начнет ли он бежать по Product и рассчитывать подзапрос для каждого товара, сумеет ли он из product=@productid hash probe сделать. Надо будет потестировать.

                  Но в любом случае вы же понимаете, что это жесткий хак и чтобы его нормально использовать, вам придется все представления такими хаками делать. Плюс возможно с WITH RECOMPILE, чтобы MS SQL не дурел с планами.
                    0
                    Вообще, меня удивил выбор СУБД — SQL Server 2019 — это даже не RTM, его ещё пилят во всю.


                    Ну это чтобы не было комментариев: «в последней версии это скорее всего исправили» :) Просто у Oracle 19c у MS SQL тоже хотелось 19.

                    Про остальное сверху вроде ответили.

                      0
                      Ну да, у Оракла релиз, у МС — CTP, красивые цифры, грамотный выбор. И не то, чтобы я был уверен, что в релизных версиях точно всё будет иначе, просто это выглядит странно.
                      Выше есть одна здравая мысль — надо протестировать. Пожалуйста, тестируйте.
                      Остальное (как, например, функцию с WITH RECOMPILE) я комментировать там не буду, чтобы остаться в рамках приличий.
                        0
                        Выше есть одна здравая мысль — надо протестировать. Пожалуйста, тестируйте.

                        Протестировал и изменил статью. Сможете прокомментировать? Вообще как я понял CROSS APPLY это практически всегда Nested Loop. То есть план с Hash построить SQL сервера не могут, и на таких запросах сразу кладут всю базу. Или я что-то не так делаю.
                          0
                          Пока не могу прокомментировать, статья слишком большая, я уже путаюсь.
                          Покажите, пожалуйста, запрос в комментариях — с cross apply и без.
                            0
                            Вот ссылка на раздел.

                            Там внутри есть UPD выделен жирным там и запрос и план MS SQL есть в частности (хотя исходный запрос чуть выше UPD).

                            Чуть ниже под спойлером запрос с ручным JPPD. Вот его план (выполняется 12 секунд без параллелизма).

                            952139	1	  |--Compute Scalar(DEFINE:([Expr1021]=CASE WHEN [Expr1059]=(0) THEN NULL ELSE [Expr1060] END))
                            952139	1	       |--Hash Match(Aggregate, HASH:([test].[dbo].[shipmentdetail].[id]) DEFINE:([Expr1059]=COUNT_BIG([Union1016]), [Expr1060]=SUM([Union1016])))
                            10948565	1	            |--Hash Match(Inner Join, HASH:([test].[dbo].[shipment].[stock], [test].[dbo].[shipmentdetail].[product])=([Union1013], [Union1014]), RESIDUAL:([Union1013]=[test].[dbo].[shipment].[stock] AND [Union1014]=[test].[dbo].[shipmentdetail].[product] AND [Union1015]<[test].[dbo].[shipment].[date]))
                            999730	1	                 |--Hash Match(Inner Join, HASH:([test].[dbo].[shipment].[id])=([test].[dbo].[shipmentdetail].[shipment]))
                            100001	1	                 |    |--Hash Match(Inner Join, HASH:([test].[dbo].[shipment].[date])=([test].[dbo].[shipment].[date]), RESIDUAL:([test].[dbo].[shipment].[date]=[test].[dbo].[shipment].[date]))
                            300	1	                 |    |    |--Hash Match(Aggregate, HASH:([test].[dbo].[shipment].[date]), RESIDUAL:([test].[dbo].[shipment].[date] = [test].[dbo].[shipment].[date]))
                            999730	1	                 |    |    |    |--Hash Match(Inner Join, HASH:([test].[dbo].[shipment].[id])=([test].[dbo].[shipmentdetail].[shipment]))
                            100001	1	                 |    |    |         |--Clustered Index Scan(OBJECT:([test].[dbo].[shipment].[PK__shipment__3213E83F4B7B4D07]))
                            999730	1	                 |    |    |         |--Clustered Index Scan(OBJECT:([test].[dbo].[shipmentdetail].[PK__shipment__3213E83F996CFFF4]),  WHERE:([test].[dbo].[shipmentdetail].[quantity]=(5.000)))
                            100001	1	                 |    |    |--Clustered Index Scan(OBJECT:([test].[dbo].[shipment].[PK__shipment__3213E83F4B7B4D07]))
                            999730	1	                 |    |--Clustered Index Scan(OBJECT:([test].[dbo].[shipmentdetail].[PK__shipment__3213E83F996CFFF4]),  WHERE:([test].[dbo].[shipmentdetail].[quantity]=(5.000)))
                            11000002	1	                 |--Concatenation
                            1000001	1	                      |--Hash Match(Inner Join, HASH:([test].[dbo].[receipt].[id])=([test].[dbo].[receiptdetail].[receipt]))
                            10001	1	                      |    |--Clustered Index Scan(OBJECT:([test].[dbo].[receipt].[PK__receipt__3213E83FE2F580DF]))
                            1000001	1	                      |    |--Clustered Index Scan(OBJECT:([test].[dbo].[receiptdetail].[PK__receiptd__3213E83FE8063B8C]))
                            10000001	1	                      |--Hash Match(Inner Join, HASH:([test].[dbo].[shipment].[id])=([test].[dbo].[shipmentdetail].[shipment]))
                            100001	1	                           |--Clustered Index Scan(OBJECT:([test].[dbo].[shipment].[PK__shipment__3213E83F4B7B4D07]))
                            10000001	1	                           |--Compute Scalar(DEFINE:([Expr1012]= -[test].[dbo].[shipmentdetail].[quantity]))
                            10000001	1	                                |--Clustered Index Scan(OBJECT:([test].[dbo].[shipmentdetail].[PK__shipment__3213E83F996CFFF4]))
                            
                              0
                              Ваш запрос из текста
                              SELECT shipmentDetail.id, b.quantity
                              	FROM shipmentDetail 
                              	JOIN shipment ON shipmentDetail.shipment = shipment.id
                              	JOIN (SELECT stock, product, dates.date, SUM(quantity) AS quantity
                              		FROM
                              			(SELECT receipt.stock, product, receipt.date, quantity
                              			FROM receiptDetail 
                              				JOIN receipt ON receiptDetail.receipt = receipt.id
                              			UNION ALL 
                              			SELECT shipment.stock, product, shipment.date, -quantity
                              				FROM shipmentDetail 
                              				JOIN shipment ON shipmentDetail.shipment = shipment.id
                              			) details
                              		JOIN 
                              			(SELECT shipment.date
                              				FROM shipmentDetail 
                              				JOIN shipment ON shipmentDetail.shipment = shipment.id
                              				WHERE shipmentDetail.quantity = 5
                              				GROUP BY shipment.date
                              			) dates ON details.date < dates.date
                              		GROUP BY stock, product, dates.date
                              	) b ON b.stock = shipment.stock AND b.product = shipmentDetail.product AND b.date = shipment.date
                              	WHERE shipmentDetail.quantity = 5 


                              Мой запрос с CROSS APPLY
                              SELECT shipmentDetail.id, b.quantity	
                              	FROM shipmentDetail 
                              	JOIN shipment ON shipmentDetail.shipment = shipment.id
                              	cross apply
                              	(
                              	SELECT SUM(quantity) AS quantity
                              	FROM
                              		(SELECT date, stock, product, quantity
                              		FROM receiptDetail 
                              			inner JOIN receipt ON receiptDetail.receipt = receipt.id
                              			WHERE  (receipt.stock = shipment.stock) and (product = shipmentDetail.product) 
                              		UNION ALL 
                              		SELECT date, stock, product, -quantity
                              			FROM shipmentDetail sd
                              			inner JOIN shipment s ON sd.shipment = s.id
                              			WHERE  (s.stock = shipment.stock) and (sd.product = shipmentDetail.product) 
                              		) details
                              		join 
                              		(SELECT ss.date
                              				FROM shipmentDetail  ssd
                              				JOIN shipment ss ON ssd.shipment = ss.id
                              				WHERE ss.date = shipment.date
                              				GROUP BY ss.date
                              		) dates ON details.date < dates.date
                              		group by stock, product, dates.date
                              	
                              	) b
                              	WHERE shipmentDetail.quantity = 5


                              План выполнения вашего запроса, выполненного на вашем сервере (строка подключения указана в статье):


                              План выполнения моего запроса, выполненного на вашем сервере (строка подключения указана в статье):


                              Проверить планы и данные:
                              
                              SELECT shipmentDetail.id, b.quantity
                              	into #t1
                              	FROM shipmentDetail 
                              	JOIN shipment ON shipmentDetail.shipment = shipment.id
                              	JOIN (SELECT stock, product, dates.date, SUM(quantity) AS quantity
                              		FROM
                              			(SELECT receipt.stock, product, receipt.date, quantity
                              			FROM receiptDetail 
                              				JOIN receipt ON receiptDetail.receipt = receipt.id
                              			UNION ALL 
                              			SELECT shipment.stock, product, shipment.date, -quantity
                              				FROM shipmentDetail 
                              				JOIN shipment ON shipmentDetail.shipment = shipment.id
                              			) details
                              		JOIN 
                              			(SELECT shipment.date
                              				FROM shipmentDetail 
                              				JOIN shipment ON shipmentDetail.shipment = shipment.id
                              				WHERE shipmentDetail.quantity = 5
                              				GROUP BY shipment.date
                              			) dates ON details.date < dates.date
                              		GROUP BY stock, product, dates.date
                              	) b ON b.stock = shipment.stock AND b.product = shipmentDetail.product AND b.date = shipment.date
                              	WHERE shipmentDetail.quantity = 5 
                              
                              SELECT shipmentDetail.id, b.quantity
                              	into #t2
                              	FROM shipmentDetail 
                              	JOIN shipment ON shipmentDetail.shipment = shipment.id
                              	cross apply
                              	(
                              	SELECT SUM(quantity) AS quantity
                              	FROM
                              		(SELECT date, stock, product, quantity
                              		FROM receiptDetail 
                              			inner JOIN receipt ON receiptDetail.receipt = receipt.id
                              			WHERE  (receipt.stock = shipment.stock) and (product = shipmentDetail.product) 
                              		UNION ALL 
                              		SELECT date, stock, product, -quantity
                              			FROM shipmentDetail sd
                              			inner JOIN shipment s ON sd.shipment = s.id
                              			WHERE  (s.stock = shipment.stock) and (sd.product = shipmentDetail.product) 
                              		) details
                              		join 
                              		(SELECT ss.date
                              				FROM shipmentDetail  ssd
                              				JOIN shipment ss ON ssd.shipment = ss.id
                              				WHERE ss.date = shipment.date
                              				GROUP BY ss.date
                              		) dates ON details.date < dates.date
                              		group by stock, product, dates.date
                              	
                              	) b
                              	WHERE shipmentDetail.quantity = 5
                              
                              
                              select *
                              from #t1 t1
                              full join  #t2 t2 on t1.id = t2.id
                              where t1.quantity <> t2.quantity or t1.id is null or t2.id is null or t1.quantity is null or t2.quantity is null
                              
                              drop table #t1
                              drop table #t2


                              «хак» с таблицей дат крутой, признаю.
                              Теоретически, с inline UDF результат должен быть тот же самый, но прав на создание UDF у гостевой учётки нет :)
                                0
                                «хак» с таблицей дат крутой, признаю.

                                Не, я не спорю что с этим «хаком» и CROSS APPLY он выполнится нормально. Вопрос догадается ли SQL сервер этот хак сделать самостоятельно, если ему дать ему базовый запрос (с balance, я такой view уже создал на тестовой базе). Как я проверил нет, можете тоже проверить, если хотите (balance там ровно как с статье). А UDF с этим хаком не имеет смысла, так как там внутри по сути контест внешнего запроса.

                                Вообще этот хак, это не более чем «JPPD в общем случае» контурно описанный в статье.
                                  0
                                  APPLY — это практически гарантировано nested loop join (вариант с hash join SQL сервера даже не рассматривают)

                                  Вроде ваши слова в статье, нет?
                                  И почему такая UDF не будет иметь смысла? Вы сможете передавать ей нужные параметры и использовать её как «представление», если нужно. Как это сделать писал выше.
                                    0
                                    Я про то что UDF возвращает просто остатки. А смысл хака внутрь группировки добавить JOIN с контекстом запроса (в данном случае shipmentDetail.quantity = 5), но этот контекст запроса может быть любым (например product.lastSuppliedDate WHERE product.name LIKE 'dfDFFD'). То есть непонятно какую UDF вообще создавать.
                                      0
                                      В «моём» запросе quantity внутрь CROSS APPLY не передаётся, внутри только таблица дат.
                                        0
                                        Так у вас подзапрос тогда для всех дат выполняется, а не только для тех где quantity=5 будет выполняться. Но не суть. Вы то все равно контекст внутрь протолкнули (пусть и его часть)
                                        (SELECT ss.date
                                        				FROM shipmentDetail  ssd
                                        				JOIN shipment ss ON ssd.shipment = ss.id
                                        				WHERE ss.date = shipment.date
                                        				GROUP BY ss.date
                                        		)

                                        А в другом запросе в контексте могут совсем другие таблицы быть (например product). Какую UDF для остатков на дату соответственно создавать?
                                          0
                                          Я протолкнул внутрь дату и только.
                                          Вы написали, что при CROSS APPLY «вариант с hash join SQL сервера даже не рассматривают». Я показал, что это не так на вашем же примере.
                                          Если выполнить:
                                          это
                                          SET STATISTICS TIME, IO ON;
                                          
                                          SELECT shipmentDetail.id, b.quantity
                                          	FROM shipmentDetail 
                                          	JOIN shipment ON shipmentDetail.shipment = shipment.id
                                          	JOIN (SELECT stock, product, dates.date, SUM(quantity) AS quantity
                                          		FROM
                                          			(SELECT receipt.stock, product, receipt.date, quantity
                                          			FROM receiptDetail 
                                          				JOIN receipt ON receiptDetail.receipt = receipt.id
                                          			UNION ALL 
                                          			SELECT shipment.stock, product, shipment.date, -quantity
                                          				FROM shipmentDetail 
                                          				JOIN shipment ON shipmentDetail.shipment = shipment.id
                                          			) details
                                          		JOIN 
                                          			(SELECT shipment.date
                                          				FROM shipmentDetail 
                                          				JOIN shipment ON shipmentDetail.shipment = shipment.id
                                          				WHERE shipmentDetail.quantity = 5
                                          				GROUP BY shipment.date
                                          			) dates ON details.date < dates.date
                                          		GROUP BY stock, product, dates.date
                                          	) b ON b.stock = shipment.stock AND b.product = shipmentDetail.product AND b.date = shipment.date
                                          	WHERE shipmentDetail.quantity = 5 
                                          
                                          SELECT shipmentDetail.id, b.quantity	
                                          	FROM shipmentDetail 
                                          	JOIN shipment ON shipmentDetail.shipment = shipment.id
                                          	cross apply
                                          	(
                                          	SELECT  SUM(quantity) AS quantity
                                          	FROM
                                          		(SELECT date, stock, product, quantity
                                          		FROM receiptDetail 
                                          			inner JOIN receipt ON receiptDetail.receipt = receipt.id
                                          			WHERE  (receipt.stock = shipment.stock) and (product = shipmentDetail.product) 
                                          		UNION ALL 
                                          		SELECT date, stock, product, -quantity
                                          			FROM shipmentDetail sd
                                          			inner JOIN shipment s ON sd.shipment = s.id
                                          			WHERE  (s.stock = shipment.stock) and (sd.product = shipmentDetail.product) 
                                          		) details
                                          		join 
                                          		(SELECT ss.date
                                          				FROM shipmentDetail  ssd
                                          				JOIN shipment ss ON ssd.shipment = ss.id
                                          				WHERE ss.date = shipment.date
                                          				GROUP BY ss.date
                                          		) dates ON details.date < dates.date
                                          		group by stock, product, dates.date
                                          	
                                          	) b
                                          	WHERE shipmentDetail.quantity = 5 
                                          
                                          SET STATISTICS TIME, IO OFF;


                                          увидите что-то вроде:
                                          этого
                                          (затронуто строк: 952139)
                                          Table 'shipmentdetail'. Scan count 9, logical reads 183601, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
                                          Table 'shipment'. Scan count 9, logical reads 4125, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
                                          Table 'receiptdetail'. Scan count 3, logical reads 5517, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
                                          Table 'receipt'. Scan count 3, logical reads 136, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
                                          Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

                                          (затронута одна строка)

                                          SQL Server Execution Times:
                                          CPU time = 11750 ms, elapsed time = 14761 ms.

                                          (затронуто строк: 952139)
                                          Table 'shipmentdetail'. Scan count 9, logical reads 144521, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
                                          Table 'shipment'. Scan count 9, logical reads 4125, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
                                          Table 'receiptdetail'. Scan count 3, logical reads 5517, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
                                          Table 'receipt'. Scan count 3, logical reads 136, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
                                          Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
                                          Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

                                          (затронута одна строка)

                                          SQL Server Execution Times:
                                          CPU time = 11240 ms, elapsed time = 17012 ms.


                                          и можете оценить эффективность, она примерно одинакова (примерно одинаковый CPU time, логических чтений меньше у CROSS APPLY, на elapsed time особого внимания обращать внимания не стоит, тут сеть очень может повлиять).
                                          Сейчас вы придумываете ситуации и хотите чтобы я их опроверг. А мне уже дальше не интересно, извините.
                                          ИМХО, было бы по-честному с вашей стороны исправить своё описание CROSS APPLY, но, в общем-то ваше дело. Описание вы и без меня исправили, спасибо.
                                            0
                                            Да, я это исправил сразу, когда сообразил, что JOIN это по большому счету частный случай APPLY, и фраза действительно странная.

                                            А речь в разделе шла именно о случае, когда есть параметр и внутри нет ни одного предиката эквивалентности с ним, а только >, < (когда эквивалентность есть, можно обойтись обычными VIEW и JOIN). И у SQL сервер два варианта либо nested loop, либо JPPD в общем случае («хак»), который он делать не умеет.
            +4

            Шикарная статья, спасибо! Но я бы всё-таки различал три вещи: абстрактная реляционная модель, язык запросов к ней, реализация всего этого в современных СУБД. Из названия статьи хочется заподозрить, что речь пойдет о втором пункте, но на самом деле речь идёт о всех трёх. Как-то бы переименовать статью, быть может (но не знаю как).

              +1
              Спасибо. Да вы правы, наверное можно было бы так разделить, но это имело бы смысл, если бы какие-то из этих трех вещей могли использоваться друг без друга.

              На практике же дело приходится иметь с ними всеми вместе взятыми + на самом деле обертками еще из логики представлений, ORM. То есть и так общей картины у большинства людей нет, а если еще раздробить информацию, то совсем какой-то информационный шум получится.
            • НЛО прилетело и опубликовало эту надпись здесь
                0
                И ещё важная проблема — не все базы хорошо переводятся на «кластерный» способ работы, как в NoSQL.

                Кстати это модный тренд сейчас — Distributed SQL с ACID. Google Spanner, Yugabyte'ы всякие. Но они к сожалению даже window функций вроде не поддерживают, не то что рекурсивных CTE. Но надеюсь это пока.
                  0

                  Есть еще, например, Exasol, у них довольно сильная поддержка стандарта. Не уверен, правда, насчет рекурсивных CTE.

                    0
                    Я у них ACID'а что-то не вижу, а учитывая, что они позиционируются как analytics database, то возможно его там и нет. А я имел ввиду Distributed SQL сервера именно с ACID.
                      0

                      Что касается Exasol… Тут граница проходит не по ACID, а по типу нагрузки. ACID в Exasol идеальный, более того, уровень изоляции транзакций serializable. Но база адаптирована под OLAP профиль (редкие большие записи и много сложных вычиток), а не под OLTP, когда идёт высоко-конкуррентное чтение-изменение данных в одной таблице.

                0
                Del
                  0

                  О да, с "проталкиванием" параметров во VIEW буквально на днях столкнулся в полный рост.


                  Есть, скажем, таблица с историей статусов, и на ее основе создана VIEW, показывающая последний статус для каждой сущности. И если запрашивать данные по ID, план получается хороший, с index scan, запрос быстрый:


                  select v.last_status from status_view v where v.id = 123

                  Но если это же условие применяется в join-е, и даже если запись, к которой происходит join, будет строго одна в силу других условий, планировщик не парится, и делает full table scan, по всем миллионам записей:


                  select v.status 
                  from entity e
                  inner join status_view v on v.id = e.id
                  where e.number = 'xxx'

                  Получается, что VIEW призвана абстрагировать разработчика от особенностей хранения данных, но, как и многие другие абстракции, норовит "протечь", заставляя разработчика идти на разнообразные ухищрения.

                    0
                    Вообще чаще всего, если представления простые, обычно проблемы со статистикой. Тот же Oracle очень чувствителен к ней (и к примеру при добавлении индекса, не всегда пересчитывает ее по этому индексу, это вообще крышу сносит). Но да если у вас там внутри функции от колонок, громоздкие Or'ы, аналитические функции и хоть что-то нестандартное, готовьтесь к удару по iowait вашего сервера.
                      0

                      VIEW простая, в ней "всего лишь" фильтрация по результату оконной функции.


                      Что-то вроде


                      select * from (
                        select id, status, date, rank() over (partition by id order by date desc) r
                        from raw_statuses
                      ) t
                      where r = 1

                      Как же не хватает агрегатных функций типа FIRST и LAST. Все способы обойти это выглядят костылями в той или иной степени...

                        +1
                        Это да. Я сам 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 если честно не помню.
                          0
                          А можно немного подробнее? Что должны делать LAST и FIRST? И где в SQL Server нет ORDER?
                          Вообще, там есть FIRST_VALUE/LAST_VALUE — они делают что-то другое?
                            0

                            Это оконные функции, а речь про агрегатные. Они немного по-разному работают и не всегда взаимозамеяемы.

                              +1
                              Ну, т.е., условно, смысл в том, чтобы написать
                              select e, last(status)
                              from t
                              group by e
                              и получить последний по какому-то принципу в таблице t статус? А как должен определяться этот принцип?
                                +1

                                Да нет, я прекрасно понимаю, почему их нет. Просто неоднократно сталкивался с тем, что их наличие теоретически могло бы сделать запрос намного проще/нагляднее/понятнее.
                                Про отсутствие STRING_AGG до версии 2017 и связанные с этим костыли даже упоминать не хочется...

                              +1
                              Дело не в том. В Postgres можно создать функцию LAST как я приводил выше и сделать что-то вроде:

                              SELECT LAST(id ORDER BY date,id) FROM shipment

                              В MSSQL ЕМНИП WITHIN GROUP есть но у конкретных агрегирующих функций. У custom aggregate нет. Во всяком случае лет 5 назад вроде так было.

                              Про FIRST_VALUE и LAST_VALUE уже ответили. Это оконные функции.
                            0
                            Разобраться бы и посмотреть планы.
                            «абстрагировать разработчика от особенностей хранения данных» — sql-разработчик должен понимать, что под капотом view — сканы или поиски по индексу.
                            В вашем случае вместо тяжелой оконной конструкции почему бы не применить cross apply или табличную функцию —
                            select o.ObjectID, x.LastStatus
                              from MyObjects o
                              cross apply ( select top 1 s.Status as LastStatus
                                                    from MyObjectStatuses s 
                                                    where s.ObjectID = o.ObjectID 
                                                    order by s.Date desc -- любое нужное условие
                                               ) x
                              0
                              «абстрагировать разработчика от особенностей хранения данных» — sql-разработчик должен понимать, что под капотом view — сканы или поиски по индексу.

                              Так, а зачем тогда view вообще нужен?
                                0
                                Для уменьшения размера кода.
                          0

                          В оракле такие штуки можно решить хинтами. Чтобы сработал пуш предиката, нужно, чтобы джоин был методом nested loops, и это можно сделать примерно так:


                          select /*+ leading(t) use_nl(v) */
                                 v.*
                            from table1 t,
                                 view1 v
                           where t.some_id = 123
                             and t.another_id = v.another_id;

                          То, что идет после слова select – это хинт. Он говорит ораклу, начинать джоины с таблицы t, а при присоединении представления v, использовать nested loops.


                          Таким же образом можно поступать, если мы из ведущей таблицы берем поле, которое в ведомой является ключом секционирования. Чтобы в таком случае не перебирать все партиции, нужен опять же nested loops. Такое не-перебирание партиций называется partition pruning.

                            –1
                            Классно. Платить огромное количество денег за Oracle, а потом еще подсказывать ему, как выполнять запросы в примитивных случаях.
                              +1

                              Как раз вот в примитивных случаях ничего подсказывать не нужно — большая вероятность сделать только хуже.

                        • НЛО прилетело и опубликовало эту надпись здесь
                            +1
                            Эм, columnstore индексы там придумали для другого — больше для поддержки здоровенных аналитических запросов в DWH.
                            Для того, что описано в цитате намного больше подходят filtered индексы.
                            • НЛО прилетело и опубликовало эту надпись здесь
                              0
                              И у них вроде как работают «лучшие в мире инженеры».

                              Из свежего…
                              Две структуры. В одной 3 параметра VARCHAR2, в другой два.
                              Использование не той Array структур (Oracle) в параметре — выдает четкое предупреждение, что не те параметры.


                              Использование не той Array структур внутри другой структуры как параметра приводит к неопределенному поведение хоста Oracle (стек или память портится на ХОСТОВОЙ части) и любимой ошибке ORA-00600: internal error code или вообще Listener не отвечает Jdbc.
                              Из за одной опечатки в коде пол дня потрачено на поиск. Причем на на разных версиях Oracle проявлялось по разному.
                              Чем, кстати, не дырка для атаки на хостовую часть...


                              Ну и еще до кучи всякого в реализации JDBC встречалось… Лень находить артефакты для комментария. И код JDBC при трассировке оставляет странное впечатление (возможно он еще с java 1.4… но стиль написания забавен)

                                0
                                Ну как пишут Oracle это известная штука. Гулял с год назад соответствующий пост.

                                Собственно, я когда тестировал матпредставления, если честно так и не смог их заставить работать с UPDATE при FAST REFRESH ON COMMIT. При INSERT обновляются, а при UPDATE нет. Но я не стал уже это в статье писать, уверен что где-то есть секретное знание, как это нужно делать. Просто мне не удалось его найти.

                                Ну и вообще если погуглить, про проблемы с производительностью FAST REFRESH матпредставлений, там такие веселые костыли выплывают, но я с Oracle в какой-то момент уже перестал чему-то удивляться.
                                +2
                                Впечатление от статьи — по верхушкам.
                                1. Зачем делать материализованное представление, чтоб потом упереться в его ограничения. Материализованное представление — самый негибкий способ поддержки синхронности исходных и вычисленных данных, используется в простейших случаях. В приближенных к жизни сценариях синхронизация делается отдельным кодом, не обязательно синхронно с изменениями исходной таблицы.
                                2. Какие-то странные выводы про неудобства табличных функций в cross apply — вполне можно и параметры функции использовать, и далее во where накладывать условия — inline-функция раскроется в запросе, и все условия сработают.
                                3. Проблема N+1 в данном случае высосана из пальца. Вопрос привычки и опыта.
                                4. Триггеры уровня оператора в postgreSQL вполне себе поддерживают получение старых и измененных данных — иначе, действительно, зачем они нужны, этакий чемодан без ручки.
                                  0
                                  Зачем делать материализованное представление, чтоб потом упереться в его ограничения. Материализованное представление — самый негибкий способ поддержки синхронности исходных и вычисленных данных, используется в простейших случаях. В приближенных к жизни сценариях синхронизация делается отдельным кодом, не обязательно синхронно с изменениями исходной таблицы.

                                  Так, а как поддерживать остатки в таком случае? Ну и разработчик может изначально не знать, нужно ли материализовывать данное представление или нет.
                                  Какие-то странные выводы про неудобства табличных функций в cross apply — вполне можно и параметры функции использовать, и далее во where накладывать условия — inline-функция раскроется в запросе, и все условия сработают.

                                  Это как? Можно пример?
                                  Проблема N+1 в данном случае высосана из пальца. Вопрос привычки и опыта.

                                  Так а что делать если у вас написана хранимка, и ее надо для 1000 записей вызвать?
                                  Триггеры уровня оператора в postgreSQL вполне себе поддерживают получение старых и измененных данных — иначе, действительно, зачем они нужны, этакий чемодан без ручки.

                                  Тут вопрос есть на stackoverflow.
                                  NEW

                                  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, но это как я понял не совсем то. Во всяком случае примеры там какие то совсем странные.
                                    0
                                    Так а что делать если у вас написана хранимка, и ее надо для 1000 записей вызвать?
                                    Я тоже не понял, в чем заключается проблема N+1.
                                    1. Если у вас есть хранимка, и ее надо один раз выполнить для 1000 записей, то берете и выполняете.
                                    2. Если у вас была хранимка, которая выполнялась для одной записи, а теперь нужно будет регулярно выполнять ее для 1000 записей, значит, у вас изменились требования и код надо дописывать/переписывать. А раз все равно надо переписывать, то переписываем хранимку так, чтобы ее можно было выполнить для любого числа записей от 1 до 1000 (в идеале — до плюс бесконечности).
                                      0
                                      Ну я это и написал, что надо переписывать. А мысль была в том, что SQL сам мог бы компилировать циклы в запросы. Ну теоретически в идеальном мире. Мы, к примеру, это реализовали во всяком случае.
                                        0
                                        А мысль была в том, что SQL сам мог бы компилировать циклы в запросы.
                                        В SQL нет никаких циклов. Циклы есть в процедурных расширениях, которые у каждой СУБД свои и никак абсолютно не стандартизированы. А внутри процедуры может твориться что угодно, и, в общем случае, задача догадаться, что из происходящего внутри можно превратить в запрос, выглядит совершенно не решаемой.
                                          0
                                          Строго говоря и хранимок в SQL нет. Это тоже процедурные решения.

                                          Понятно, что если есть какие-то сложные рекурсивные зависимости: когда команда изменяет данные, которые читает следующая команда в процедуре, тогда да такую, штуку тяжело оптимизировать. Но очень большое количество простых процедур (без таких зависимостей) можно и нужно оптимизировать. Проверено на практике.
                                          +1
                                          SQL декларативен, какие циклы? Те же полиморфизмы и наследования из той же оперы. Зачем они там?
                                            0
                                            В пункте Проблема N+1 есть уточнение про расширения вроде.

                                            А полиморфизм с наследованием это очень даже декларативные концепции. Я бы даже сказал более декларативные чем SQL, если декларативностью вообще меряться можно. И я в статье написал зачем:

                                            возможности эффективно декомпозировать задачи, а также не наращивать технический долг по мере роста сложности системы
                                              0
                                              В пункте Проблема N+1 есть уточнение про расширения вроде.
                                              Они не входят в стандарт, про что вам сразу и сказали. И более того они не декларативны по своей сути.

                                              А полиморфизм с наследованием это очень даже декларативные концепции.

                                              В каком месте и каким боком они к SQL подходят и как они туда ложатся? Единственное применение наследование которое я использовал в том же PostgreSQL это для секционирования таблиц, для другого оно как-то плохо применимо. Приведите хоть один вариант использования когда это прям дает какой-то внятный профит. Я вот не припомню ни одного применения где полиморфизм и наследование давали какой-то внятный эффект. Более того чем дальше тем больше в нем разочаровываются и откатываются к функцинальщине.
                                                0
                                                Они не входят в стандарт, про что вам сразу и сказали. И более того они не декларативны по своей сути.


                                                Стандарт SQL вообще очень забавная штука. Они UPDATE и агрегирующие функции стандартизировать не могут. Ну я нигде и не говорил про стандарт, это чисто практическая статья, а хранимки и триггеры при разработке на SQL используются очень часто.

                                                В каком месте и каким боком они к SQL подходят и как они туда ложатся? Единственное применение наследование которое я использовал в том же PostgreSQL это для секционирования таблиц, для другого оно как-то плохо применимо. Приведите хоть один вариант использования когда это прям дает какой-то внятный профит. Я вот не припомню ни одного применения где полиморфизм и наследование давали какой-то внятный эффект. Более того чем дальше тем больше в нем разочаровываются и откатываются к функцинальщине.

                                                Функциональщина перпендикулярна наследованию и полиморфизму. А вообще возьмите крупный проект на C++ или Java и посчитайте там количество extends /: и abstract / virtual.
                                                  +1
                                                  Стандарт SQL вообще очень забавная штука. Они UPDATE и агрегирующие
                                                  функции стандартизировать не могут.

                                                  update и агрегирующие функции хотя бы ложатся в канву.

                                                  А вообще возьмите крупный проект на C++ или Java и посчитайте там количество extends /: и abstract / virtual.

                                                  Угу весьма здорово привести ООП языки как довод. Давайте уж тогда ORM еще припомним и что уши N+1 оттуда торчат.
                                                    +1
                                                    А вообще возьмите крупный проект на C++ или Java и посчитайте там количество extends /: и abstract / virtual.

                                                    Зачем полиморфизм в Java мы и так знаем. А вот в SQL он зачем? Какую задачу в SQL вы хотите решить при помощи полиморфизма, которая без полиморфизма решается плохо или не решается, например, нормализацией?

                                                      0
                                                      Затем же зачем и в Java. Модульность и расширяемость. То есть нужен новый функционал, добавляем новые классы, наследуем, добавляем в абстрактные представления реализацию для этих классов. Иначе спагетти-код как в 1С.

                                                      А можно все сделать нормально классами, наследованием и полиморфизмом. Вот пример кусочка дерева классов например:


                                                        0

                                                        А какой ещё, простите, функционал вы хотели бы добавить к объекту "Запись Таблицы SKU"? Других-то объектов в большинстве RDBMS нет, потому что именно тип "Запись Таблицы<T>" лучше всего решает основную задачу базы данных — хранения данных/фактов о <T>.


                                                        Полиморфизм самих данных в духе


                                                        class Location {}
                                                        class Airport extends Location {}

                                                        решается нормализацией данных — имеем отдельно таблицы Location и Airport, и в Airport также имеем foreign key к Location. Просто, оптимизируемо, и — внимание! — логически верно, так как


                                                        NOT(IS_A(Airport, Location)) && HAS_A(Airport, Location)

                                                        Или вы бы хотели хранить как значение произвольные объекты? Ну так и этого у DBMS тоже есть, и даже с полиморфизмом объектов (и иногда с неявным приведением типов, как в Oracle).


                                                        UPD: Пригляделся к вашей картинке. Судя по названиям, неправильно вы домен готовите. Если заменить наследование композицией, иерархия будет заметно проще. Всем этим документам всего-лишь нужно обладать некой общей частью, а выглядит будто вы заставляете их ещё и вести себя одинаково только по факту наличия этой общей части, но не по факту действительного наличия общего поведения. Сужу исключительно по наличию нескольких типов с одинаковым названием.

                                                          0
                                                          решается нормализацией данных — имеем отдельно таблицы Location и Airport, и в Airport также имеем foreign key к Location. Просто, оптимизируемо, и — внимание! — логически верно, так как

                                                          Так Airport это не Location. А вот Capital это City. А Роза — цветок.

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

                                                          То почему произвольными объектами плохо я писал в статье в разделе про наследование и полиморфизм. И как бы я хотел тоже там писал. И уже несколько раз тут отвечал, почему наследование таблиц не подходит.
                                                          Сужу исключительно по наличию нескольких типов с одинаковым названием.

                                                          В lsFusion просто множественное наследование есть. Это одинаковые классы. Просто так граф в виде дерева выглядит.
                                                            0

                                                            И всё же, так и нет ответа на исходный вопрос: какие задачи вы хотите решить полиморфизмом в RDBMS? Какие части реляционной модели полиморфизм улучшит?


                                                            А вот Capital это City. А Роза — цветок.

                                                            А в чём функциональное различие capital и city, кроме единственного флага, который, к тому же, меняет своё значение во времени? Какие свойства есть у столицы? Почему, вы считаете, недостаточно сделать
                                                            type Capital {CityId, ...},
                                                            или даже вообще по-рабоче-крестьянски
                                                            type City { bool IsCapital, ... },
                                                            а вот обязательно надо иметь
                                                            type Capital extends City?
                                                            Что улучшится при втором подходе? Как при наследовании быть если столицу перенесли? Будем удалять город? Или всё-таки только статус предадим от одного города другому?


                                                            То же самое с розами. При моделировании растений вообще намного проще иметь Traits, потому что на практически любое свойство роз, кроме ботанического определения (которое есть натуральный ключ), найдётся вид-другой роз, у которых это свойство отсутствует.


                                                            вы считаете что наследование не нужно и всегда можно обойтись композицией

                                                            С точки зрения (под)системы хранения данных — да, определённо. В общем для ООП — зависит от доменной модели.

                                                              0
                                                              То что вы говорите, это отдельный вопрос Наследование vs Композиция. И напрямую к теме не относится.

                                                              Я лично без наследования и полиморфизма не могу. Но есть к примеру 1С: УТ, где весь код привет 70-е и ничего люди покупают и дописывают.
                                                                0

                                                                Я так-то вообще всего лишь пытаюсь выяснить, для чего вам в SQL (или PL/SQL?) понадобился полиморфизм, и полиморфизм чего именно вы вообще имеете в виду.
                                                                А вместо этого вижу то столицы, то розы, то код из семидесятых.

                                                                  0
                                                                  в SQL

                                                                  Чтобы работало быстро
                                                                  понадобился полиморфизм, и полиморфизм чего именно вы вообще имеете в виду

                                                                  Полиморфизм вычислений (в SQL читай представлений).

                                                                  Чтобы если мне нужно было добавить новый вид цветка, я бы добавил класс Роза, унаследовал от класса Цветок, во все необходимые абстрактные представления добавил реализации (подзапросы) для этого класса. И все запросы с цветами начали бы учитывать эти подзапросы.
                                                                    0
                                                                    Полиморфизм… представлений

                                                                    Ну смотрите:


                                                                    with raw_data (id, type, quantity) as (
                                                                      select
                                                                        shipment_id, 'Shipment', shipped_quantity
                                                                      from shipment
                                                                    
                                                                      union all
                                                                    
                                                                      select
                                                                        receipt_id, 'Receipt', received_quantity
                                                                      from receipt
                                                                    )
                                                                    select * from raw_data

                                                                    В данном случае подзапрос определяет подтип строки таблицы, выглядящий примерно как


                                                                    type raw_data_type is (
                                                                      id number, type varchar, quantity number
                                                                    )

                                                                    И обе части UNION'a приводятся к этому типу, в итоге основное тело запроса работает с супертипом, а не с каждым подтипом отдельно. Вот это — вполне полиморфизм, с точки зрения хранилища фактов, коим SQL является, но вы же вроде бы чего-то другого добиваетесь, не так ли? Вот и объяснили бы, чего. Эти вот притянутые примеры, которыми вы продолжаете кидаться, картину не проясняют, в них слишком мало анализа с вашей стороны недостатков вашего подхода и преимуществ других подходов. Например, в чём конкретно преимущество подхода, когда вы храните все розы отдельно от остальных цветов, так что вам приходится объединять подклассы вручную через представления?


                                                                    … во все необходимые абстрактные представления добавил реализации (подзапросы) для этого класса

                                                                    Так а где тут полиморфизм-то? Так вы в этих представлениях по сути наплодите в итоге кучу тех самых if-ов (подзапросов, если точнее, но не суть), о которых так нелестно отзываетесь в соседней ветке комментариев. И после каждого нового класса будете плодить ещё больше "if-ов".
                                                                    Тогда как при решении композицией у вас для вашей розы будет самодостаточная запись каталога цветов и рядом вспомогательная таблица атрибутов именно роз, и никаких абстрактных представлений переписывать будет не нужно. Хотите цветы — читаете из цветов. Хотите розы — читаете из роз, объединяете с цветами (и вот для этого-то какое-нибудь представление прямо идеально подходит).

                                                        0

                                                        В SQL не хватает наследования (Правда в определенном виде он есть в PostgreSQL)
                                                        А так таблицы-это классы, а их строки-экземпляры.
                                                        Сложный тип реализуется через внешние ключи.
                                                        Не хватает возможности встраивания кусков кода в виде например функций для множественного использования с разными входными параметрами в разных местах вызовов. Вроде в MS SQL Server 2019 обещают это сделать.
                                                        А вообще с выходом 2019 скуля что-то затянули

                                                          0

                                                          Под встраиванием кусков кода вы имеете в виду хранение функций в виде данных, и в каждой строке таблицы иметь возможность хранить свою функцию?
                                                          Какую задачу вы бы хотели так решить?


                                                          Правда в определенном виде он есть в PostgreSQL

                                                          В Postgres больше смахивает на синтаксический сахар над теми же внешними ключами. По-прежнему не могу понять, чем эти протекающие на раз-два абстракции лучше простого и честного разделения на таблицы с честными же связями.

                                                            0

                                                            под встраивание я имею в виду использование одного кода в разных конструкциях, т е те же функции, но которые работают нормально в плане оптимизации, а не как сейчас (хранимки не подойдут-они так легко не встраиваются)

                                                              +1
                                                              под встраивание я имею в виду использование одного кода в разных конструкциях

                                                              Не понял. Разных — это каких? Предикат, проекция, CASE, другая процедура — это разные конструкции? Или вы имеете в виду разные конструкции в форме разграничения statement/expression? Если же вы под встраиванием пытаетесь выразить inline-включение тела функции в тело главного запроса, то этого вы никаким наследованием не добьётесь. Рискну предположить, что это вообще силами SQL невозможно для общего случая, потому что предполагает слишком сильное переписывание и запроса, и самой функции. Ну не сможет никто, кроме вас, чисто преобразовать


                                                              function get_customer_name(id number) as begin
                                                                  return select customer_name from customer
                                                                         where customer_id = id;
                                                              end;
                                                              
                                                              select
                                                                purchase_date,
                                                                get_customer_name(customer_id) customer_name
                                                              from purchases

                                                              В


                                                              select
                                                                purchase_date,
                                                                customer_name
                                                              from purchases
                                                                left join customer using(customer_id)

                                                              хранимки не подойдут

                                                              Мне кажется, вы не совсем верно понимаете чем ограничены оптимизации функций. Это зависит вовсе не от принадлежности "хранимке", а от детерминированности самой функции (в терминах ФП — от чистоты). Но при этом сам вызов особо не оптимизировать. Если детерминирована — то вызов можно мемоизировать, только и всего.

                                            0
                                            www.postgresql.org/docs/10/plpgsql-trigger.html
                                            посмотрите пример 42.7 про триггер уровня оператора. Да, transition tables, все работает.
                                              0
                                              Ок, спасибо, сейчас дополню статью.
                                              0
                                              Так, а как поддерживать остатки в таком случае?


                                              Например, так. Нужен журнал операций, отдельный код асинхронно «бежит» по журналу и по операциям «материализует» остатки в отдельной таблице.
                                                0
                                                Ну то есть остатки будут приблизительные? Интересно что скажут пользователи на такое? Особенно, если их надо в транзакции использовать.
                                                  +1
                                                  Если надо в транзакциях использовать, можно сразу в транзакциях и считать остатки, триггерами или их аналогами.
                                                    0
                                                    Так и как именно это делать? Например даже в таком простом случае с остатками как в статье. На какие таблицы и какие триггеры делать?
                                                      0
                                                      Есть реестр остатков, он напрямую из бизнес-логики не меняется. Есть журнал проводок. Остатки меняются только через добавление записей в таблицу проводок, в проводке ссылка на документ-основание.
                                                      Изменение реестра остатков делается либо триггером на таблице проводок, либо в API функциях, которые выполняют запись в таблицу проводок
                                                        0
                                                        А таблицу проводок кто и какими триггерами меняет? И что в ней лежит?

                                                        Я так понимаю вы 1Скую схему продвигаете (я знаю как она работает). Вы же в курсе, что они редактирование по сути удалением \ добавлением предполагают делать (что я собственно и писал в статье)?
                                                          0
                                                          Я об устройстве 1С уже довольно смутно помню. Но терминология устоявшаяся, понятная широким массам.

                                                          В таблице проводок каждая запись — это: артикул, количество-дельта изменения остатка, ссылка на документ-основание.

                                                          Суммирование всех записей в таблице проводок с group by по артикулу приводит к получению актуальных остатков. Но делать это никогда не приходится, так как эта информация хранится в реестре остатков — таблице, которая в общем по структуре напоминает матвью такого запроса.

                                                          Сам факт изменения остатков (в плюс или минус) фиксируется добавлением записи в таблицу проводок. Как и откуда это инициируется — более широкая тема. Ну допустим, в терминах 1С при «проведении документа-основания». Но ничто не мешает делать это индивидуально по каждой строке документа. В зависимости от правил системы можно запретить на таблице любые dml кроме insert: откат проводок только через сторнирование. А можно и разрешить удаление и редактирование. В любом случае, в триггере уровня записи на таблице проводок мы знаем без лишних запросов, какой update выполнить на реестр, чтобы поддержать там актуальные остатки.

                                                          Ну и естественно, в эти таблицы можно добавить внешний ключ на склады, типы остатков или другие разрезы, кому что необходимо в учете
                                                            0
                                                            Ну то есть, вы немного декомпозировали проблему, сделав таблицу проводок, и тем самым чуть-чуть упростив базовую проблему. Теперь у нас есть проблема обновления таблицы проводок. Она по вашему сильно проще?

                                                            Ну и плюс вы возможно создали лишнюю материализацию (таблицу).
                                                              0
                                                              Вчера в 17:18 вы спросили: «Как именно это делать?»
                                                              Я привел рабочую схему. И да, я сделал материализацию, которая НА ПРАКТИКЕ решает проблемы с производительностью, которые вызывает ваша очень теориетически правильная вьюха остатков.
                                                              Не вижу никакой проблемы с обовлением таблицы проводок. Блокиурется запись реестра, выполняется добаление/изменение/удаление проводки, снимается блокировка с записи реестра.
                                                                0
                                                                проблемы с производительностью, которые вызывает ваша очень теориетически правильная вьюха остатков.

                                                                Как раз теоретически правильная никаких проблем с производительностью не вызывает.
                                                                Не вижу никакой проблемы с обовлением таблицы проводок. Блокиурется запись реестра, выполняется добаление/изменение/удаление проводки, снимается блокировка с записи реестра.

                                                                Это только если количество меняется к примеру. А если к примеру у вас внутреннее перемещение и в нем меняется склад прихода, то вам нужно руками к примеру найти и удалить все приходные и расходные проводки старого склада прихода и добавить все приходные и расходные проводки нового склада прихода. И как вы вообще собираетесь определять ситуации изменения и что именно там изменилось (количество или склад)? Триггерами?
                                                                Плюс если усложнить логику остатков (скажем двойным учетом) или логику каких-нибудь задолженностей взять (со всякими отсрочками). Там знаете сколько таких сценариев будет (например когда тип договора меняется)?
                                                                  0
                                                                  >И как вы вообще собираетесь определять ситуации изменения и что именно там изменилось (количество или склад)? Триггерами?
                                                                  Именно. В триггере из :old берем старую строку реестра по ключам артикул/склад, вычитаем старое кол-во. Из :new берем новую строку и увеличиваем количество.
                                                                  Но вообще такая операция, как изменение склада в «выполненном» документе (документе в финальном статусе, в терминах 1С можно сказать в проведенном) — недопустима. Так как на разных складах разные мат.ответственные лица. И это должно делаться в два этапа: откат документа из финального статуса делает мат отвестветвенный старого склада, а проставление нового склада и перевод в финальный статус — мат.ответственный нового склада. Таким образом с точки зрения СУБД все сводится у удалению старых проводок и созданию новых. Изменение проводок не требуется.
                                                                  По поводу двойного учета и у типа договора не готов дискутировать, потому что не понимаю, какая предметная область и как реализовано. На первый взгляд, изменение типа договора вообще не должно влиять на реестр остатков, так как договор не является документом товародвижения, то есть основанием для проводок.
                                                                    0
                                                                    Именно. В триггере из :old берем старую строку реестра по ключам артикул/склад, вычитаем старое кол-во. Из :new берем новую строку и увеличиваем количество.

                                                                    Не, это как по таблице проводок остатки обновлять. Это отдельная дополнительная задача (хотя тут даже INDEXED VIEW в MS SQL возможно справится).

                                                                    Вопрос как по тем же документам проводки обновлять и как изменения в документе отслеживать? Там же нужно изменения и shipmentDetail и shipment и receiptDetail и receipt отслеживать.
                                                                    Но вообще такая операция, как изменение склада в «выполненном» документе (документе в финальном статусе, в терминах 1С можно сказать в проведенном) — недопустима. Так как на разных складах разные мат.ответственные лица

                                                                    А если остатки например в разрезе отделов магазина. Там одинаковые ответственные. И они тоже люди и тоже ошибаются. Или они должны дрожащими руками каждое действие делать?
                                                                    И это должно делаться в два этапа: откат документа из финального статуса делает мат отвестветвенный старого склада, а проставление нового склада и перевод в финальный статус — мат.ответственный нового склада.

                                                                    И ввод тоже? Но опять таки это уже вопрос конкретного бизнеса, у всех бизнесов разные процессы. А вы сейчас задачу под ответ подгоняете.
                                                                    так как договор не является документом товародвижения, то есть основанием для проводок.

                                                                    А причем тут товародвижение, я говорил про представление (регистр) текущей задолженности поставщика (по аналогии с остатками, только в деньгах)
                                                                0

                                                                Так вроде смотрим как работают АБС… Есть накопленный итог и хвост проводок, отсчитать можно от любого момента, более того, другого способа для транзакционной системы что-то не припомню (кромки блокировки на запись).

                                                0
                                                Какие-то странные выводы про неудобства табличных функций в cross apply — вполне можно и параметры функции использовать, и далее во where накладывать условия — inline-функция раскроется в запросе, и все условия сработают.

                                                Кстати изучаю эту тему сейчас. С CROSS APPLY (вместо INNER JOIN) действительно вроде прокатывает (даже вроде не руша JPPD), а что с OUTER APPLY (вместо LEFT JOIN) делать, там то условие соединения в WHERE не перенесешь.
                                                +2
                                                Касательно структуры статьи:
                                                1. Лучше бы разбили статью на несколько отдельных частей по разным пунктам, т.к. сейчас получится каша с кучей комментариев к разным частям вразброс,
                                                2. Еще лучше было бы разбить и по разным СУБД, т.к. уже вижу кучу неточностей по Oracle.

                                                Далее постараюсь (насколько будет хватать времени и не будет мешать лень) прокомментировать по пунктам:

                                                1. View: Материализация представлений поддерживается в очень частных случаях
                                                Это очевидная неточность. Материализация представление поддерживается всегда, но не всегда поддерживаются те или иные опциональные возможности, как например FAST REFRESH.
                                                2. Касательно FAST REFRESH: если вы подумаете и сами попробуете проанализировать как можно реализовать инкрементальные обновления, то поймете, что список ограничений абсолютно адекватен текущей сложности SQL.
                                                3. «вас будет ждать еще один неприятный сюрприз: материализованное представление обновляется только в самом конце транзакции.»
                                                Сюрприз?! При создании мвью вы сами выбираете «on commit», так что должны знать, что это происходит при коммите.
                                                4. «абсолютно непонятно, как в принципе получить актуальные данные для материализованного представления внутри транзакции» — вы прямо напрашиваетесь на холивар об актуальности «грязных чтений». Не путайте функциональность и целевое назначение view и mview. MView, в первую очередь, это таблицы со всеми сопутствующими свойствами.
                                                5. «один из достаточно авторитетных экспертов Oracle Donald Burleson в одной из своих книг.» — Что?! На таком серьезном ресурсе как хабр и упоминать бурлесоновщину?!

                                                6. «View: В параметризованные представления во FROM можно передавать только константы»
                                                В Oracle можно использовать контексты, функции, можно создать FGAC и тд и тп.

                                                7. «В MS SQL для решения таких задач есть так называемые table inlined функции, в них можно объявить параметры и использовать их внутри запроса» — в оракле тоже есть и, кроме того, туда спокойно можно передавать параметры из других таблиц

                                                8. «JPPD: Не работает с оконными функциями и рекурсивными CTE»
                                                Работает, просто оконные функции вы не умеете готовить:
                                                вы указываете «row_number() OVER (PARTITION BY shipment ORDER BY id)» — то есть сами инструктируете СУБД сначала посчитать ROW_NUMBER для всего дата сета из таблицы, а фильтруете на другом уровне, после этого подсчета. Если СУБД сначала отфильтровала по вашему предикату, то результат ROW_NUMBER был бы неверным.

                                                9. «JPPD: Низкая эффективность при работе с денормализованными данными»
                                                Ничего не понятно… Касательно выбранных планов оптимизатора, надо приводить точные данные и, например для Оракла, трассировку 10053. Тут важно даже умение собирать статистику.

                                                10. «Так, например, переписанный запрос оконными функциями будет выглядеть следующим образом» — что вы тут хотели и почему запросы не эквивалентны? Вообще во всех указанных СУБД есть LATERALS/CROSS APPLY, которые легко решают проблемы с JPPD

                                                11. «Разделение логики условий на типы JOIN и WHERE» — кстати, советую прочитать про Oracle vs ANSI синтаксис, и пре- и пост- предикаты в отличной книге «The Power of Oracle SQL» habr.com/en/post/461971

                                                12. «Плохая оптимизация при работе с последними значениями»
                                                Оракл может построить хороший план:
                                                SQL> explain plan for
                                                  2  SELECT SUM(cc.ls)
                                                  3          FROM Product pr
                                                  4          LEFT JOIN (SELECT MAX(shipment) AS ls, s.product
                                                  5              FROM shipmentDetail s
                                                  6              GROUP BY s.product) cc ON cc.product=pr.id
                                                  7          WHERE pr.name LIKE 'Product 86%';
                                                
                                                Explained.
                                                
                                                Plan hash value: 2212025625
                                                
                                                ----------------------------------------------------------------------------------------------------------------------
                                                | Id  | Operation                                | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
                                                ----------------------------------------------------------------------------------------------------------------------
                                                |   0 | SELECT STATEMENT                         |                           |     1 |    78 |    69   (0)| 00:00:01 |
                                                |   1 |  SORT AGGREGATE                          |                           |     1 |    78 |            |          |
                                                |   2 |   NESTED LOOPS                           |                           |     1 |    78 |    69   (0)| 00:00:01 |
                                                |*  3 |    TABLE ACCESS FULL                     | PRODUCT                   |     1 |    65 |    68   (0)| 00:00:01 |
                                                |   4 |    VIEW PUSHED PREDICATE                 |                           |     1 |    13 |     1   (0)| 00:00:01 |
                                                |*  5 |     FILTER                               |                           |       |       |            |          |
                                                |   6 |      SORT AGGREGATE                      |                           |     1 |    26 |            |          |
                                                |   7 |       TABLE ACCESS BY INDEX ROWID BATCHED| SHIPMENTDETAIL            |     1 |    26 |     1   (0)| 00:00:01 |
                                                |*  8 |        INDEX RANGE SCAN                  | SHIPMENTDETAIL_PRODUCT_FK |     1 |       |     1   (0)| 00:00:01 |
                                                ----------------------------------------------------------------------------------------------------------------------
                                                
                                                Outline Data
                                                -------------
                                                
                                                  /*+
                                                      BEGIN_OUTLINE_DATA
                                                      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$639F1A6F" "S"@"SEL$2")
                                                      INDEX_RS_ASC(@"SEL$639F1A6F" "S"@"SEL$2" ("SHIPMENTDETAIL"."PRODUCT"))
                                                      USE_NL(@"SEL$B9D46A48" "CC"@"SEL$1")
                                                      LEADING(@"SEL$B9D46A48" "PR"@"SEL$1" "CC"@"SEL$1")
                                                      NO_ACCESS(@"SEL$B9D46A48" "CC"@"SEL$1")
                                                      FULL(@"SEL$B9D46A48" "PR"@"SEL$1")
                                                      OUTLINE(@"SEL$1")
                                                      OUTLINE(@"SEL$3")
                                                      ANSI_REARCH(@"SEL$1")
                                                      OUTLINE(@"SEL$8812AA4E")
                                                      ANSI_REARCH(@"SEL$3")
                                                      OUTLINE(@"SEL$E8571221")
                                                      MERGE(@"SEL$8812AA4E" >"SEL$E8571221")
                                                      OUTLINE(@"SEL$776AA54E")
                                                      OUTLINE(@"SEL$2")
                                                      OUTER_JOIN_TO_INNER(@"SEL$776AA54E" "CC"@"SEL$1")
                                                      OUTLINE_LEAF(@"SEL$B9D46A48")
                                                      PUSH_PRED(@"SEL$B9D46A48" "CC"@"SEL$1" 2)
                                                      OUTLINE_LEAF(@"SEL$639F1A6F")
                                                      ALL_ROWS
                                                      DB_VERSION('18.1.0')
                                                      OPTIMIZER_FEATURES_ENABLE('18.1.0')
                                                      IGNORE_OPTIM_EMBEDDED_HINTS
                                                      END_OUTLINE_DATA
                                                  */
                                                
                                                Predicate Information (identified by operation id):
                                                ---------------------------------------------------
                                                
                                                   3 - filter("PR"."NAME" LIKE 'Product 86%')
                                                   5 - filter(COUNT(*)>0)
                                                   8 - access("S"."PRODUCT"="PR"."ID")
                                                

                                                Проверьте свои актуальность и свойства статистик по этим таблицам.

                                                … /// to be continued…
                                                  0
                                                  Это очевидная неточность. Материализация представление поддерживается всегда, но не всегда поддерживаются те или иные опциональные возможности, как например 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 выполняется максимум) и выполнить запрос.
                                                  0
                                                  Разделение логики условий на типы JOIN и WHERE

                                                  Немногие это замечают, но логика, влияющая на то, какие записи окажутся в результирующей таблице в SQL, разделена на 2 части
                                                  По-моему, как раз наоборот. ВСЕ замечают, что логика разделена на две части, но не все сразу умеют правильно её готовить. Я поначалу тоже писал всё в WHERE:

                                                  select ...
                                                    from a, b, c, d, e
                                                   where a.column1 = b.column1
                                                     and b.column2 = c.column2
                                                     and c.column3 = d.column3
                                                     and d.column4 = e.column4
                                                     and a.column123 = 456
                                                     and b.column987 = 'ququ'
                                                     and ... -- и еще 100500 условий

                                                  А потом оказалось, что сложные запросы, написанные кем-то другим, а то и самим собой, но очень давно, (ВНЕЗАПНО!) нужно отлаживать и рефакторить. И вот в длинной трехстраничной портянке начинаешь разбираться — где условия соединения, где логика, где чё. Потом условия соединения сами собой кучкуются вместе, а потом уползают в секцию FROM, чтобы не мешались.

                                                  select ...
                                                    from a
                                                        join b on a.column1 = b.column1
                                                        join c on b.column2 = c.column2
                                                        join d on c.column3 = d.column3
                                                        join e on d.column4 = e.column4
                                                   where a.column123 = 456
                                                     and b.column987 = 'ququ'
                                                     and ... -- и еще 100500 условий


                                                  Потом появляется вишенка на торте. Оказывается, что записей какое-то не такое количество, поэтому нужно закомментить все, кроме одной таблицы, а потом приджойнивать остальные по одной. И оказывается, что если все условия соединения описаны во FROM, то решается эта проблема исключительно просто:

                                                  select ...
                                                    from a
                                                       -- join b on a.column1 = b.column1
                                                       -- join c on b.column2 = c.column2
                                                       -- join d on c.column3 = d.column3
                                                       -- join e on d.column4 = e.column4
                                                   where a.column123 = 456
                                                     and b.column987 = 'ququ'
                                                     and ... -- и еще 100500 условий
                                                  То есть добавить/убрать таблицу к запросу — это всего один комментарий. Сравните с тем, что будет, когда все условия идут вперемешку в WHERE.

                                                  Короче, я лично транклюкирую ту гадину, которая такую возможность у меня отнимет [сатанинский хохот за кадром]
                                                    0
                                                    Этот пункт не про разделение на JOIN и WHERE. Это пункт про разделение типов JOIN и WHERE. То есть INNER, LEFT, RIGHT и FULL и WHERE.

                                                    В остальном согласен условия соединения в WHERE на сложных запросах — это ад.
                                                    0
                                                    1.
                                                    SET @ProductName = LTRIM(RTRIM(@ProductName));

                                                    Начиная с MSSQL 2017 наконец-то присутствует функция TRIM

                                                    2.
                                                    В Oracle и PostgreSQL в них нельзя обращаться к новым и предыдущим значениям, то есть такие триггеры бесполезны чуть меньше чем полностью.


                                                    В PostgreSQL можно начиная с версии 10 (клауза REFERENCING)
                                                    Единственное ограничение — триггер не должен быть мультисобытийным
                                                      0
                                                      Начиная с MSSQL 2017 наконец-то присутствует функция TRIM

                                                      Я этот пример, если честно в гугле нашел.
                                                      В PostgreSQL можно начиная с версии 10 (клауза REFERENCING)

                                                      Да, уже подсказали выше. Спасибо. Сейчас подправлю статью.
                                                      0
                                                      Отличная статья! Мир несовершенен.

                                                      Мне не хватает еще возможность передачи параметра во FROM
                                                      Например:
                                                      SELECT id
                                                      FROM :tableName


                                                      Хорошо бы добавить возможность получить N строк просто для запроса.
                                                      SELECT n
                                                      FROM someSystemTable
                                                      WHERE someSystemTable.start=42 and someSystemTable.end=142


                                                      Добавить бы ограничения на группу. Скажем запрос «Топ 3 самых дорогих товара для всех категорий» это тот еще квест.

                                                      Нарастающий итого (running total) еще один вызов. Сделать то можно, но всегда непросто. Сильно не хватате обращений к предыдущей и следующей строке.
                                                        0
                                                        Без упоминания конкретной СУБД непонятно в чём проблема. Насколько я понимаю, во всех трёх СУБД из статьи проблем быть не должно.
                                                        передачи параметра во FROM

                                                        dynamic sql
                                                        Хорошо бы добавить возможность получить N строк просто для запроса.

                                                        fetch...offset
                                                        Топ 3 самых дорогих товара для всех категорий

                                                        select c.name, tt.name from category c cross apply (select top 3 name from product p where p.category_id = c.id order by price desc) tt

                                                        running total

                                                        sum(x) over(order by ...)
                                                        Сильно не хватате обращений к предыдущей и следующей строке.
                                                        PRECEDING / FOLLOWING
                                                          0
                                                          Это вынесено но уровень надстроек — в каждой СУБД свое (а в некоторых и нету).
                                                        0
                                                        Проблема N+1
                                                        А теперь нам нужно выполнить эту процедуру для 1000 записей.

                                                        Это не проблема N+1. Она ведь не просто так так называется. Попробуйте определить, где тут N, а где 1.

                                                          0
                                                          1 есть неявно. Когда вы цикл будете делать, там вам запрос для получения 1000 записей скорее всего понадобится (FOR row IN (SELECT ...)). Но да, базовая проблема вообще из области ORM.

                                                          Другое дело, это самая близкая аналогия из всех существующих, и конечные последствия у нее такие же.
                                                            0

                                                            Из области ORM, и ORM же ее и решает. Она связана с чтением. Насколько я знаю, вставку 1000 записей не называют N+1. При работе с базой из приложения, в тех местах, где она может возникнуть, она легко оптимизируется до массовых вставок.


                                                            В примере все параметры вводятся извне и добавляются в таблицу при вызове процедуры, поэтому непонятно, откуда там может что-то выбираться до вызова.

                                                              0
                                                              Из области ORM, и ORM же ее и решает.

                                                              Ну как решает, пытается решить. Точнее разработчик должен это делать. Но это в следующих статьях про ORM/ERP фреймворки.
                                                              При работе с базой из приложения, в тех местах, где она может возникнуть, она легко оптимизируется до массовых вставок.

                                                              Что значит легко? Переписыванием процедуры по сути. Или дублированием что еще хуже.
                                                              В примере все параметры вводятся извне и добавляются в таблицу при вызове процедуры, поэтому непонятно, откуда там может что-то выбираться до вызова.

                                                              Там имелось ввиду что нужно вызвать эту процедуру для всех записей какой-то таблицы.
                                                                –1
                                                                Ну как решает, пытается решить. Точнее разработчик должен это делать.

                                                                Все нормально решается, автоматически делается второй запрос с WHERE foreign_key IN (...) по ключам из основного запроса. Разработчик только указывает что-то типа Entity::find()->where(...)->with('relationship').


                                                                Что значит легко? Переписыванием процедуры по сути.

                                                                При работе с базой из приложения таких процедур вообще не будет. И аналогов тоже не будет. Изменение параметров сущностей отдельно, сохранение отдельно. Вызываем логику для каждой сущности, обработанные сущности накапливаем в массив, по достижении N записей сохраняем и массив сбрасываем. Потребность в массовых вставках обычно возникает при каких-нибудь выгрузках/загрузках, которые делаются консольными приложениями и к работе пользователей не имеют отношения, а значит на соответствующий код не влияют.

                                                                  0
                                                                  Разработчик только указывает что-то типа Entity::find()->where(...)->with('relationship').

                                                                  Ну так я и говорю разработчик делает, а не ORM.
                                                                  Вызываем логику для каждой сущности

                                                                  Ну так вы когда вызываете логику каждой сущности у вас N+1 и будет.

                                                                  Или еще хуже вам эту логику дублировать придется. Сначала чтобы избежать проблемы N+1 докинуть в первый запрос все нужные данные, а потом еще раз повторить эту логику в ORM с обработкой этих данных (дублирование конечно не полное будет, но процентов на 80). Ну и вариантов выстрелить в ногу, если забыли, не те данные считали и т.п.

                                                                  Но вообще конкретно эта статья не про ORM, тут как раз про то когда бизнес-логику на SQL делают — в банках и других финансах, ритейле это очень распространено.
                                                                    0
                                                                    Ну так я и говорю разработчик делает, а не ORM.

                                                                    Так она сама и не может знать, какие части из всего графа сущностей надо загрузить из базы. Написать вызов одной функции это не какая-то большая сложность.


                                                                    Ну так вы когда вызываете логику каждой сущности у вас N+1 и будет.

                                                                    Ага, только не запросов к базе, а вызовов функций внутри приложения. Это будет примерно то же самое, что будет делать база для каждой строки.


                                                                    Или еще хуже вам эту логику дублировать придется. Сначала чтобы избежать проблемы N+1 докинуть в первый запрос все нужные данные

                                                                    Ничего не надо дублировать, и первый запрос менять не надо. Зачем? Логика находится в одном месте, ее и вызываем.


                                                                    Но вообще конкретно эта статья не про ORM, тут как раз про то когда бизнес-логику на SQL делают

                                                                    Вот и надо говорить о проблемах и альтернативах. Возможно для кого-то некоторое снижение производительности будет приемлемо для уменьшения вероятности ошибки и увеличения удобности разработки.

                                                                      0
                                                                      Так она сама и не может знать, какие части из всего графа сущностей надо загрузить из базы. Написать вызов одной функции это не какая-то большая сложность.

                                                                      Ну как бы из кода обработки могла бы догадаться. Ну и там может быть много данных использоваться, так что функция не одна будет.
                                                                      Ага, только не запросов к базе, а вызовов функций внутри приложения. Это будет примерно то же самое, что будет делать база для каждой строки.

                                                                      Подразумевалось, если вы предварительно не укажите какие данные считать (то что вы писали сверху).
                                                                      Ничего не надо дублировать, и первый запрос менять не надо. Зачем? Логика находится в одном месте, ее и вызываем.

                                                                      Имелось ввиду, допустим вам надо в строку записать текущую цену по товару, ну и надбавку в зависимости от классификатора. Вам надо сначала orm'у сказать что их надо предварительно загрузить, а потом в логике обработки опять к ним же обратиться и выполнить непосредственно обработку. Хотя ORM'у что-то предварительно говорить это monkey job (а точнее ручная оптимизация, то есть accidental complexity).
                                                                        0
                                                                        Ну как бы из кода обработки могла бы догадаться.

                                                                        Нет. Данные из одной и той же таблицы могут использоваться в разных частях приложения, в коде одной обработки надо загружать связанные сущности, в коде другой нет.


                                                                        Ну и там может быть много данных использоваться, так что функция не одна будет.

                                                                        Ага, ->with('relationship1')->with('relationship2')->with('relationship3'), очень сложный код)


                                                                        Это я имел ввиду

                                                                        Ну тогда и ваше решение от N+1 не избавляет, и вообще от нее избавиться нельзя. База же для всех N строк делает обработку.


                                                                        Я имел ввиду, допустим вам надо в строку записать текущую цену по товару, ну и надбавку в зависимости от классификатора. Вам надо сначала orm'у сказать что их надо предварительно загрузить, а потом в логике обработки опять к ним же обратиться и выполнить непосредственно обработку.

                                                                        Все равно никакого дублирования логики нет.


                                                                        $skus = Sku::find()->where(...)->limit($offset, $limit)->with('category');
                                                                        
                                                                        foreach ($skus as $sku) {
                                                                            $sku->calculateNewPrice();
                                                                        }
                                                                        
                                                                        $this->bulkInsert($skus);
                                                                        
                                                                        class Sku
                                                                        {
                                                                            public function calculateNewPrice()
                                                                            {
                                                                                if ($this->category->param1 == 'value1') {
                                                                                    $this->price = 100;
                                                                                } elseif ($this->category->param2 == 'value2') {
                                                                                    $this->price = 200;
                                                                                }
                                                                            }
                                                                        }
                                                                        

                                                                        Логика может находиться и не в Sku, но все равно в одном месте.


                                                                        Хотя ORM'у что-то предварительно говорить это monkey job

                                                                        В таком общем виде это утверждение неверно. Вы о каких-то своих представлениях говорите, и скорее всего неправильных.

                                                                          0
                                                                          Смотрите, у вас просто достаточно примитивные примеры получаются. Например текущая цена она для склада, товара. Склад лежит в документе, товар в строке документа, текущая цена это представление — SELECT LAST (price ORDER BY date,id) FROM prices GROUP BY product, store.
                                                                          Как это все будет выглядеть?
                                                                            0

                                                                            Код обновления тогда тоже напишите.

                                                                              –1

                                                                              Если я правильно понял связи, можно вот так сделать. Не уверен, что из коробки заработает в Yii (я в примерах его подразумеваю), но в целом это решаемо.


                                                                              $products = Product::find()
                                                                                  ->joinWith('documentRow')
                                                                                  ->joinWith('documentRow.document')
                                                                                  ->joinWith('lastPrice')
                                                                                  ->where(...)->limit($offset, $limit);
                                                                              
                                                                              foreach ($products as $product) {
                                                                                  $product->price = $product->lastPrice->price;
                                                                              }
                                                                              
                                                                              $this->bulkInsert($products);
                                                                              
                                                                              ...
                                                                              
                                                                              class Product
                                                                              {
                                                                                  public function getDocumentRow()
                                                                                  {
                                                                                      $this->hasOne(DocumentRow::class, ['id', 'document_row_id']);
                                                                                  }
                                                                              
                                                                                  public function getLastPrice()
                                                                                  {
                                                                                      $this->hasOne(LastPrice::class, [['product_id', 'store_id'], ['id', 'store_id']]);
                                                                                  }
                                                                              }
                                                                              
                                                                              class DocumentRow
                                                                              {
                                                                                  public function getDocument()
                                                                                  {
                                                                                      $this->hasOne(Document::class, ['id', 'document_id']);
                                                                                  }
                                                                              }
                                                                              
                                                                              class LastPrice
                                                                              {
                                                                                  public function table()
                                                                                  {
                                                                                      return 'prices';
                                                                                  }
                                                                              
                                                                                  public function find()
                                                                                  {
                                                                                      $subQuery = parent::find()->select('LAST(price)')
                                                                                          ->groupBy(['product_id', 'store_id'])
                                                                                          ->orderBy(['date', 'id']);
                                                                                      return (new Query())->select('*')->from($subQuery);
                                                                                  }
                                                                              }

                                                                              Но такая архитектура с обновлением из истории выглядит странно. Я бы сделал обновление цены там же, где добавляется строка в таблицу prices. Если надо строго раз в сутки цены менять, то можно добавлять в отдельную таблицу new_prices (product_id, price) и обновлять только нужные строки по id, не перелопачивая при этом всю историю изменения цен на всех складах.

                                                                                0
                                                                                Я имел ввиду вот что:

                                                                                lastPrice = GROUP LAST price(Price p) BY product(p), stock(p);
                                                                                WHEN CHANGED(product(DocumentDetail d)) DO
                                                                                     price(d) <- lastPrice(product(d), stock(d)) * (100 + markup(product(d), stock(d))) / 100;

                                                                                Ну или можно так:

                                                                                WHEN CHANGED(product(DocumentDetail d)) AND Product pr = product(d) AND Stock s = stock(d) DO
                                                                                     price(d) <- (GROUP LAST price(Price p) IF product(p) = pr AND stock(p) = pr ORDER date(p),p) * (100 + markup(p, s)) / 100;

                                                                                У вас я так и не понял откуда она relation lastPrice возьмет (ну и вы код с описанием этих relation'ов не привели). Хотя видимо по самой задаче мы друг друга не допоняли.
                                                                                  0

                                                                                  Весь код для описания связей там есть. joinWith('lastPrice') обращается к функции getLastPrice(), которая возвращает настроенный объект Query. После запроса заполняются все указанные связи, во всех $product будет заполнен $product->lastPrice.


                                                                                  price(d) <- lastPrice(product(d), stock(d)) * (100 + markup(product(d), stock(d))) / 100;

                                                                                  Почему изменение цены товара не попадает в таблицу изменения цен?


                                                                                  Ну я там наценку не добавил, да, там будет так:


                                                                                  $product->price =
                                                                                      $priceManager->calculatePriceWithMarkup(
                                                                                          $product->lastPrice->price,
                                                                                          $product->documentRow->document->stock_id
                                                                                      );

                                                                                  А можно просто $product передавать.

                                                                                    0
                                                                                    А где привязка что product именно по product_id, а не store_id. Ну и тоже самое про documentRow.
                                                                                    Почему изменение цены товара не попадает в таблицу изменения цен?

                                                                                    Так тут цена просто отгрузки из текущей цены заполняется. Или я чего-то не понял в вопросе.
                                                                                    Ну я там наценку не добавил, да, там будет так:

                                                                                    Так а как будет выглядеть код по его предварительному чтению?
                                                                                      0
                                                                                      А где привязка что product именно по product_id, а не store_id.

                                                                                      Код при джойне превращается в такой SQL:


                                                                                      // code
                                                                                      (LastPrice::class, [['product_id', 'store_id'], ['id', 'store_id']])
                                                                                      
                                                                                      // SQL
                                                                                      lastPrice.product_id = id AND lastPrice.store_id = store_id

                                                                                      store_id берется из document, без джойнов естественно работать не будет. Возможно там надо будет указать джойн в самой связи и поля с названиями таблиц, сейчас точно не скажу, но принцип будет такой же.


                                                                                      А, и в LastPrice у меня только select('LAST(price)'), полей product_id и store_id нет, надо брать select('LAST(id)') и джойнить полную запись.


                                                                                      Так а как будет выглядеть код по его предварительному чтению?

                                                                                      По чтению кого? Все чтение это результат запроса в первой строчке. Читаются товары и их связи, причем так как там джойны, то вроде бы даже запросов с IN не будет, связи заполнятся из данных основного запроса.


                                                                                      Так тут цена просто отгрузки из текущей цены заполняется.

                                                                                      Разговор же был про цену товара, причем тут цена отгрузки?


                                                                                      В общем получается так, у вас в коде названия не соответствуют терминам предметной области, архитектура тоже выглядит слегка странно. Можем обсудить подробнее, как это делается при бизнес-логике в приложении, если напишете исходные требования к системе на русском языке, типа небольшое ТЗ на пару абзацев. Код на вашем языке для меня не настолько понятный.

                                                                                        0
                                                                                        store_id берется из document, без джойнов естественно работать не будет. Возможно там надо будет указать джойн в самой связи и поля с названиями таблиц, сейчас точно не скажу, но принцип будет такой же.

                                                                                        А, и в LastPrice у меня только select('LAST(price)'), полей product_id и store_id нет, надо брать select('LAST(id)') и джойнить полную запись.

                                                                                        Так я так и не понял. То что сверху это весь код или нет?
                                                                                        По чтению кого? Все чтение это результат запроса в первой строчке. Читаются товары и их связи, причем так как там джойны, то вроде бы даже запросов с IN не будет, связи заполнятся из данных основного запроса.

                                                                                        Я про calculatePriceWithMarkup, ему же тоже нужны данные из базы. Если написать так как вы сделали, и в
                                                                                        $products = Product::find()
                                                                                        ->joinWith('documentRow')

                                                                                        ничего не добавить, то она же N запросов делает.
                                                                                        Разговор же был про цену товара, причем тут цена отгрузки?

                                                                                        надо в строку записать текущую цену по товару, ну и надбавку в зависимости от классификатора

                                                                                        В строку документа (отгрузки) имелось ввиду записать цену товара с надбавкой.
                                                                                        Код на вашем языке для меня не настолько понятный.

                                                                                        Так если его буквально с английского переводить будет:
                                                                                        Когда изменится товар в строке документа:
                                                                                        Записать в цену строки документа, текущую цену товара этой строки документа, склада этого документа с добавленной надбавкой для товара, склада.
                                                                                        Этот код и есть по сути ТЗ.
                                                                                          0
                                                                                          То что сверху это весь код или нет?

                                                                                          Это весь код, но я его не запускал. В реальном приложении могут быть незначительные отличия, не влияющие на общую концепцию, но возможно вызывающие непонимание сейчас, поэтому я пишу уточнения.


                                                                                          Я про calculatePriceWithMarkup, ему же тоже нужны данные из базы.
                                                                                          она же N запросов делает

                                                                                          У вас в примере туда передается только товар и склад. В моем коде все они достаются одним запросом. Откуда там N запросов, если данные уже загружены из базы?


                                                                                          Так если его буквально с английского переводить будет:
                                                                                          Когда изменится товар в строке документа:
                                                                                          Записать в цену строки документа, текущую цену товара этой строки документа, склада этого документа с добавленной надбавкой для товара, склада.

                                                                                          В данном тексте нет термина "отгрузка". Поэтому это никакое не ТЗ, это просто описание работы вашего кода. Что за документы, какие документы, при каких условиях изменение появляется, откуда тут 1000 запросов возьмется.

                                                                                            0
                                                                                            Это весь код, но я его не запускал. В реальном приложении могут быть незначительные отличия, не влияющие на общую концепцию, но возможно вызывающие непонимание сейчас, поэтому я пишу уточнения.

                                                                                            Так в том то и вопрос, что непонятно насколько они небольшие будут. У вас и так уже мягко говоря не мало кода. А вы еще говорите будет что-то.
                                                                                            У вас в примере туда передается только товар и склад. В моем коде все они достаются одним запросом. Откуда там N запросов, если данные уже загружены из базы?

                                                                                            Так в одном документе много товаров. А у вас код считает наценку для одного товара.
                                                                                            В данном тексте нет термина «отгрузка». Поэтому это никакое не ТЗ, это просто описание работы вашего кода. Что за документы, какие документы, при каких условиях изменение появляется, откуда тут 1000 запросов возьмется.

                                                                                            Ну в данном случае я имел ввиду, что документ — это документ отгрузки. В любом случае можно считать что это просто документ. Давайте упростим задачу и забудем про изменение — то есть просто пересчитать для всех строк документа:
                                                                                            lastPrice = GROUP LAST price(Price p) ORDER date(p),p BY product(p), stock(p);
                                                                                            FOR document(DocumentDetail d) = myDocument DO
                                                                                                 price(d) <- lastPrice(product(d), stock(d)) * (100 + markup(product(d), stock(d))) / 100;
                                                                                            
                                                                                              0
                                                                                              Когда изменится товар в строке документа:
                                                                                              Записать в цену строки документа, текущую цену товара этой строки документа

                                                                                              Это кстати уже UPDATE, а не INSERT, который был в примере про N+1.

                                                                                                0
                                                                                                Если документ новый, то это будет INSERT, если старый — то UPDATE. Но разработчику это фиолетово на самом деле в lsFusion.
                                                                                                  0
                                                                                                  если старый — то UPDATE

                                                                                                  И как вы предлагаете делать массовый UPDATE? Мы же про N+1 говорим.


                                                                                                  У вас и так уже мягко говоря не мало кода.

                                                                                                  Код, относящийся к обсуждаемой логике находится до многоточия. Остальное это аналог объявления внешних ключей в CREATE TABLE, и этот код общий для всего приложения. Вы вообще код функций не привели, только запрос.


                                                                                                  Так в том то и вопрос, что непонятно насколько они небольшие будут.

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


                                                                                                  Так в одном документе много товаров. А у вас код считает наценку для одного товара.

                                                                                                  Ну так и у вас код считает наценку только для одного товара. Наценка зависит от одного товара товара и одного склада, а не от всех товаров в документе.


                                                                                                  price(d) <- lastPrice(product(d), stock(d)) * (100 + markup(product(d), stock(d))) / 100;


                                                                                                  У меня товары уже загружены из базы в количестве $limit штук, для каждого также загружены связи "строка документа -> документ -> склад". Для всех делаем расчет, потом все сохраняем в базу. Во время расчетов никаких запросов в базу не идет.


                                                                                                  Давайте упростим задачу и забудем про изменение — то есть просто пересчитать для всех строк документа:

                                                                                                  Я говорю о том, что делать так не надо. Не надо ничего пересчитывать и перезаписывать. Это требуется крайне редко и обычно запускается по расписанию, а не по событию. Чтобы разобраться, как правильно, я предложил обсудить исходные требования.

                                                                                                    0
                                                                                                    И как вы предлагаете делать массовый UPDATE? Мы же про N+1 говорим.

                                                                                                    А в чем проблема? UPDATE с запросами тоже работает. То есть мой пример можно скомпилировать в один запрос.
                                                                                                    Ну так и у вас код считает наценку только для одного товара.

                                                                                                    Нет сверху FOR идет. И lsFusion скомпилирует его в
                                                                                                    price(DocumentDetail d) <- lastPrice(product(d), stock(d)) * (100 + markup(product(d), stock(d))) / 100 WHERE document(d) = myDocument
                                                                                                    

                                                                                                    Который в свою очередь скомпилируется в один SQL MERGE запрос.
                                                                                                    У меня товары уже загружены из базы в количестве $limit штук, для каждого также загружены связи «строка документа -> документ -> склад».

                                                                                                    Так а с prices и markup что будет? По сути вы будете эту логику на SQL (а точнее на функциональном псевдо-SQL) писать, а обработку в ORM?
                                                                                                    Я уж молчу про то, что данные зачем то гоняются туда- сюда, с сервера БД на сервер приложений, а потом обратно.
                                                                                                    Это требуется крайне редко и обычно запускается по расписанию, а не по событию

                                                                                                    В ERP/CRM/BPM, банковских системах и вообще любых сложных системах это вообще-то стандартный кейс, их тысячи там. И там не по расписанию, а в процессе ввода это все запускается.
                                                                                                      0
                                                                                                      UPDATE с запросами тоже работает.

                                                                                                      А, ну да, я про значения извне думал.


                                                                                                      В моем коде все они достаются одним запросом. Откуда там N запросов, если данные уже загружены из базы?
                                                                                                      Так в одном документе много товаров. А у вас код считает наценку для одного товара.
                                                                                                      Ну так и у вас код считает наценку только для одного товара.
                                                                                                      Нет сверху FOR идет. И lsFusion скомпилирует его в… Который в свою очередь скомпилируется в один SQL MERGE запрос.

                                                                                                      У меня код "считает наценку только для одного товара" ровно в той же мере, в какой движок базы "считает эту наценку для одной строки" при выполнении вашего запроса.
                                                                                                      У меня тоже один запрос, а не N. Ну вернее 2, выборка и сохранение.


                                                                                                      Так а с prices и markup что будет? По сути вы будете эту логику на SQL (а точнее на функциональном псевдо-SQL) писать, а обработку в ORM?

                                                                                                      А что с ними не так? Последняя цена тоже загружена через связи, через JOIN с подзапросом. markup, как я понимаю, содержит алгоритм наценки, который рассчитывается по product и stock, у меня он находится в calculatePriceWithMarkup(). В SQL ничего не пишется, вся логика находится в приложении.


                                                                                                      Я уж молчу про то, что данные зачем то гоняются туда-сюда, с сервера БД на сервер приложений, а потом обратно.

                                                                                                      Затем, что так удобнее управлять кодом. Нет тех проблем, которые вы пытаетесь решить, используя логику в хранимых процедурах.


                                                                                                      В ERP/CRM/BPM, банковских системах и вообще любых сложных системах это вообще-то стандартный кейс, их тысячи там.

                                                                                                      Я вообще-то про то и говорю, что это стандартный кейс в первую очередь потому что "бизнес-логику на SQL делают". Если бы логика была в приложении, в большинстве случаев это было бы не нужно.

                                                                                                        0
                                                                                                        Затем, что так удобнее управлять кодом. Нет тех проблем, которые вы пытаетесь решить, используя логику в хранимых процедурах.

                                                                                                        Так подождите, вы же по сути и пишете на SQL только в специфичной синтаксической обертке (с JOIN'ами, GROUP BY только в три раза длиннее) и с суррогатными классами (вроде LastPrice). И чем тогда удобнее управлять кодом? При этом данные гоняются туда-сюда.
                                                                                                          –1

                                                                                                          На каком SQL? На SQL я достаю данные из базы и сохраняю обратно, это протокол общения с хранилищем, без него в принципе невозможно обойтись. А вся логика находится в приложении, написана на языке программирования, разбита на классы и методы, которые соответствуют терминам предметной области, можно использовать средства переиспользования кода (наследование, композиция, трейты), подключать любые сторонние технологии (кеши, хранилища), запускать многопоточную обработку. Ничего из этого в SQL нет, либо довольно сложно использовать.

                                                                                                            0
                                                                                                            На каком SQL?

                                                                                                                public function getLastPrice()
                                                                                                                {
                                                                                                                    $this->hasOne(LastPrice::class, [['product_id', 'store_id'], ['id', 'store_id']]);
                                                                                                                }
                                                                                                            

                                                                                                                public function find()
                                                                                                                {
                                                                                                                    $subQuery = parent::find()->select('LAST(price)')
                                                                                                                        ->groupBy(['product_id', 'store_id'])
                                                                                                                        ->orderBy(['date', 'id']);
                                                                                                                    return (new Query())->select('*')->from($subQuery);
                                                                                                                }
                                                                                                            

                                                                                                            Вы же понимаете, что это обычные Join'ы и Group by, только в таком весьма вывернутом синтаксисе. И в нормальной жизни, если бы не SQL, так бы на PHP никто не писал.

                                                                                                            С наследованием, вы уверены что joinWith работает работает с абстрактными методами. Ну и со всякими классами в стиле LastPrice, будут проблемы, я в статье приводил пример когда писал про наследование в оракл
                                                                                                              –1
                                                                                                              Вы же понимаете, что это обычные Join'ы и Group by, только в таком весьма вывернутом синтаксисе.

                                                                                                              Нет, это обычные foreign key в синтаксисе ORM. В join их превращает ORM, может в отдельный запрос в IN превратить, может использовать эту информацию при выводе интерфейса, или при обработке запроса к API. Это просто связи между сущностями, отражение связей предметной области.


                                                                                                              groupBy да, это часть запроса, но без него нельзя получить данные, с которыми работает логика. И как я уже сказал, он там нужен только потому что вы сделали такую таблицу.


                                                                                                              И в нормальной жизни, если бы не SQL, так бы на PHP никто не писал.

                                                                                                              Еще раз, PHP это не хранилище данных, и без обращений к хранилищу данных работать с данными принципиально нельзя. Если бы не SQL, писали бы на каком-нибудь другом синтаксисе, начиная с банального fread/fwrite по нужным смещениям.


                                                                                                              С наследованием, вы уверены что joinWith работает работает с абстрактными методами.

                                                                                                              joinWith и with просто вызывают функцию по имени, добавляя 'get' к аргументу. Если метод не будет реализован, будет соответствующая ошибка в рантайме.


                                                                                                              Ну и со всякими классами в стиле LastPrice, будут проблемы, я в статье приводил пример

                                                                                                              Вы не приводили пример, вы сказали, что это почему-то суррогатные классы, хотя эта связь многие-ко-многим явно присутствует в бизнес-логике, что их почему-то надо наследовать, и что будут какие-то проблемы. Тем более что LastPrice не является такой связью, соответственно и описанных там проблем с ней не будет. Тем более что LastPrice есть у вас в системе в виде представления, и непонятно, почему в приложении где никаких представлений нет оно не может быть в виде класса, как и любая другая таблица. Тем более что я бы сделал без нее, но по другим причинам.


                                                                                                              Наследование бизнес-сущностей это вообще отдельный вопрос. Я согласен, что это было бы неплохо, но для контроля типов в этом случае нужна особая поддержка на уровне языка, которая выходит за рамки данного обсуждения. В пределах текущих возможностей языков это крайне спорное решение, обычно можно сделать по-другому.

                                                                                                                +1
                                                                                                                foreign key

                                                                                                                Foreign key логически и есть join, то есть таблица, ключевое поле и поле связывания.
                                                                                                                Еще раз, PHP это не хранилище данных, и без обращений к хранилищу данных работать с данными принципиально нельзя. Если бы не SQL, писали бы на каком-нибудь другом синтаксисе, начиная с банального fread/fwrite по нужным смещениям.

                                                                                                                Это SQL замаскированный под PHP. А тощая корова — не газель.
                                                                                                                хотя эта связь многие-ко-многим явно присутствует в бизнес-логике

                                                                                                                Я вообще не понимаю этих суррогатов многие-ко-многим. А если четыре ключа в таблице, то это многие-ко-многим-ко-многим-ко-многим?
                                                                                                                где никаких представлений нет оно не может быть в виде класса, как и любая другая таблица

                                                                                                                Потому что логически такого объекта как последняя цена нет. Есть функция последняя цена от двух объектов (склада и товара).
                                                                                                                обычно можно сделать по-другому

                                                                                                                Это всегда можно было делать по другому. С тех пор как появились If'ы. Но если все делать If'ами получается адский лапше-код.
                                                                                                                  0
                                                                                                                  Foreign key логически и есть join, то есть таблица, ключевое поле и поле связывания.

                                                                                                                  Нет. Логически foreign key это связь между сущностями. Это даже по последнему слову в вашей фразе заметно. Оно может использоваться для присоединения, а может не использоваться. Для контроля целостности ключей например используется.


                                                                                                                  Это SQL замаскированный под PHP.

                                                                                                                  Нет. Никто ничего не маскирует, запрос это явное обращение к хранилищу. Ну невозможно обрабатывать данные, не имея данных. Значит их надо как-то получить. Может через SQL, может через протокол MongoDB, может через URL-запрос из внешнего API. Логика их обработки останется та же самая.


                                                                                                                  Я вообще не понимаю этих суррогатов многие-ко-многим. А если четыре ключа в таблице, то это многие-ко-многим-ко-многим-ко-многим?

                                                                                                                  Эм, связь многие-ко-многим это вообще-то один из стандартных терминов теории баз данных.


                                                                                                                  Потому что логически такого объекта как последняя цена нет. Есть функция последняя цена от двух объектов (склада и товара).

                                                                                                                  Ну как это нет, если вы его используете. Оно даже в вашей терминологии существительное. И это не техническая терминология, я терминология предметной области.


                                                                                                                  На всякий случай еще раз повторю — в приложении я сделал такой класс исключительно потому что у вас в базе есть такое представление. Мне он не нужен, я бы сделал по-другому. ORM это mapping, вот ваше представление мапится на этот класс, а строки из этого представления на объекты этого класса, так же как таблица products мапится на класс Product. В базе есть встроенные типы "таблица" и "представление", а в языке программирования для создания пользовательских типов данных нет ничего кроме классов. Если вы считаете, что это лишняя сущность, уберите ее из вашей архитектуры, тогда и в приложении ее не будет.


                                                                                                                  Но если все делать If'ами получается адский лапше-код.

                                                                                                                  Так и не надо все делать if-ами, есть другие способы. Композиция называется. Связь один-к-одному вполне нормально работает.

                                                                                                                    0
                                                                                                                    Нет. Логически foreign key это связь между сущностями.

                                                                                                                    Так и join это логически связь между таблицами (сущностями в вашей терминологии).
                                                                                                                    может через протокол MongoDB

                                                                                                                    А вот тут можно поподробнее. Как это join'ы и group by в MongoDB, это вроде как «документная» база.
                                                                                                                    Эм, связь многие-ко-многим это вообще-то один из стандартных терминов теории баз данных.

                                                                                                                    Да я знаю, и она очень дырявая на мой взгляд.
                                                                                                                    Ну как это нет, если вы его используете. Оно даже в вашей терминологии существительное. И это не техническая терминология, я терминология предметной области.

                                                                                                                    Под объектом я имею ввиду бизнес-объект. То, что явно создается при помощи new, имеет идентификатор (один) и т.п.
                                                                                                                    Мне он не нужен, я бы сделал по-другому

                                                                                                                    И как бы вы сделали?
                                                                                                                    Композиция называется

                                                                                                                    Вообще основная и практически единственная функция наследования это использование в механизмах полиморфизма (именно полиморфизм это замена if, такой «модульный» if). И композиция вам тут никак не поможет.
                                                                                                                      0
                                                                                                                      Так и join это логически связь между таблицами (сущностями в вашей терминологии).

                                                                                                                      Нет. Джойн это соединение двух множеств по некоторому условию. Он может быть по foreign key, а может по другому условию. И наоборот, связь между сущностями есть, а никаких джойнов может не быть.


                                                                                                                      Как это join'ы и group by в MongoDB, это вроде как «документная» база.

                                                                                                                      А причем тут именно join'ы и group by? Они мне сами по себе не нужны, мне нужны данные из хранилища. Я уже несколько раз повторил — это просто способ получения данных. Будет другое хранилище, будет другой протокол их получения.


                                                                                                                      Да я знаю, и она очень дырявая на мой взгляд.

                                                                                                                      Какая разница, дырявая она или нет. Термин "многие-ко-многим" обозначает определенный тип взаимосвязей между сущностями. Как правило при хранении данных в базе для такой связи требуется промежуточная таблица. Когда говорят этот термин, подразумевают эти особенности. Всё, причем тут какие-то суррогаты?


                                                                                                                      То, что явно создается при помощи new, имеет идентификатор (один)

                                                                                                                      Вот есть у вас тип "Склад", и есть более конкретный тип "Магазин". Вы когда "Склад" создаете при помощи new, у вас создается экземпляр сущности "Магазин"? Вот так и тут, вы создаете объект, обозначающий изменение цены, и он же становится экземпляром более конкретного типа "Текущая цена". Это кстати частично связано с наследованием, я там даже хотел унаследовать от класса Price, но потом решил попроще сделать.


                                                                                                                      Один идентификатор есть только у самостоятельных сущностей, у связей между ними идентификаторов как минимум 2, потому что у связи между чем-то и чем-то как минимум 2 конца. Связи это тоже полноценные понятия предметной области. Если вы будете заметать их под ковер, у вас могут быть проблемы с архитектурой. Возможно вы с ними не сталкивались, потому что в движке базы данных уже есть особые типы данных "таблица", "представление", и остальные, поэтому вам свои классы для них создавать не надо.


                                                                                                                      И композиция вам тут никак не поможет.

                                                                                                                      Класс Client, таблица client. Класс PhysicalClient с дополнительными полями и специфичным методами, таблица physical_client, в классе Client свойство physicalClient, которое может быть равно null. Там может будет пара if где-нибудь в начале обработки на сравнение с null, ну и всё.


                                                                                                                      И как бы вы сделали?

                                                                                                                      Описывал тут, последний абзац.

                                                                                                                        0
                                                                                                                        Вы когда «Склад» создаете при помощи new, у вас создается экземпляр сущности «Магазин»?

                                                                                                                        Нет. Не создается. Это объект класса Магазин является объектом класса Склад. Но не наоборот.
                                                                                                                        Один идентификатор есть только у самостоятельных сущностей,

                                                                                                                        Вот я про эти самостоятельные сущности и говорю. Это объекты и классы. А «несамостоятельные» сущности это — костыли ORM.
                                                                                                                        Там может будет пара if где-нибудь в начале обработки на сравнение с null, ну и всё.

                                                                                                                        Их пара, когда у вас два класса. Будет больше, будет винегрет if'ов, как собственно и было до появления ООП.
                                                                                                                        Описывал тут, последний абзац.

                                                                                                                        То есть ручной материализацией (триггерами по сути). Кстати, а куда бы вы это поле последней цены добавили и как таблицу назвали?
                                                                                                                          0
                                                                                                                          Нет. Не создается. Это объект класса Магазин является объектом класса Склад. Но не наоборот.

                                                                                                                          Откуда же он тогда взялся? Мы же объект "Склад" создавали.


                                                                                                                          Вот я про эти самостоятельные сущности и говорю. Это объекты и классы. А «несамостоятельные» сущности это — костыли ORM.

                                                                                                                          Причем тут ORM, если связи есть и в вашем коде, и вообще в предметной области?


                                                                                                                          Их пара, когда у вас два класса. Будет больше, будет винегрет if'ов

                                                                                                                          Не будет. Приводите примеры пожалуйста для своих утверждений.


                                                                                                                          То есть ручной материализацией (триггерами по сути).

                                                                                                                          Причем тут триггеры? Триггеры там ровно в той же мере, что и в вашем коде. Вы же откуда-то данные берете для вставки, и добавляете по какому-то событию. Таблица изменения цен никуда не девается, просто не используется для расчета текущей цены. Состояние отдельно, логи отдельно.


                                                                                                                          Кстати, а куда бы вы это поле последней цены добавили и как таблицу назвали?

                                                                                                                          Это цена товара на складе? Вот в параметры этой сущности бы и добавил. Есть Product с характеристиками, есть Sku(product_id) на конкретном складе, вот у этого Sku есть текущая цена на этом складе. Связь со складом у него даже в названии отражена. Никакого перелопачивания миллионов данных по всем складам не требуется.

                                                                                                            0
                                                                                                            и с суррогатными классами (вроде LastPrice)

                                                                                                            Какой же он суррогатный, если у вас специальное представление для него написано? Он там присутствует только потому что у вас в требованиях крайне спорное решение с доставанием последней записи из истории изменений. Как я уже сказал, обычно это делается по-другому.

                                                                                                              0
                                                                                                              Какой же он суррогатный

                                                                                                              Суррогатный класс потому что последняя цена — это функция (от товара и склада), а не объект (объекты это товар и склад). А вы создаете по сути суррогатные объекты и суррогатные классы под них.
                                                                                                                0

                                                                                                                Функция, которая возвращает что? Запись в таблице prices это тоже объект.

                                                                                                                  0
                                                                                                                  Возвращает число — цену.

                                                                                                                  Да, но это объект изменения цены — PriceChange, но никак не последняя цена — LastPrice.
                                                                                                                    0

                                                                                                                    Цитата:
                                                                                                                    "текущая цена это представление — SELECT LAST (price ORDER BY date,id) FROM prices GROUP BY product, store"


                                                                                                                    Ну не LastPrice, а CurrentPrice, это ничего не меняет. У вас есть конкретный бизнес-термин, который обозначает конкретную бизнес-сущность. От того, что вы будете делать вид, что ее нет, она никуда не денется. А при разработке могут быть проблемы из-за недостаточной модели предметной области.

                                                                                                                      0
                                                                                                                      Так представление это и есть функция, точнее набор функций. Но в данном случае так как представление из одной функции, можно считать, что это и есть функция.

                                                                                                                      который обозначает конкретную бизнес-сущность

                                                                                                                      Тут вопрос что такое сущность. Если вы имеете ввиду под этим класс (для которого создаются объекты), то последняя цена — не класс.
                                                                                                                        0
                                                                                                                        Функция это отображение 'A' в 'B'. Все строки множества 'B' это записи о текущей цене некоторого товара. После CREATE VIEW вы какое имя напишете для этого представления? Вот оно и будет в названии класса в приложении.

                                                                                                                        Тут вопрос что такое сущность.

                                                                                                                        Инфологическая модель данных «Сущность-связь»
                                                                                                                        Сущность – любой различимый объект (объект, который мы можем отличить от другого), информацию о котором необходимо хранить в базе данных. Сущностями могут быть люди, места, самолеты, рейсы, вкус, цвет и т.д. Необходимо различать такие понятия, как тип сущности и экземпляр сущности. Понятие тип сущности относится к набору однородных личностей, предметов, событий или идей, выступающих как целое. Экземпляр сущности относится к конкретной вещи в наборе.

                                                                                                                        Entity–relationship model
                                                                                                                        An entity may be defined as a thing capable of an independent existence that can be uniquely identified. An entity is an abstraction from the complexities of a domain. When we speak of an entity, we normally speak of some aspect of the real world that can be distinguished from other aspects of the real world.

                                                                                                                        An entity is a thing that exists either physically or logically.

                                                                                                                        An entity, strictly speaking, is an instance of a given entity-type.

                                                                                                                          0
                                                                                                                          Если определять сущность как в википедии, то это все что угодно. Количество в документе тоже тогда сущность. Как и ограничение, что остаток больше 0. То есть абсолютно бесполезное понятие.

                                                                                                                          У меня более конкретное определение объекта — то, что явно создается при помощи new, имеет идентификатор (один) и т.п.
                                                                                                                            0
                                                                                                                            Количество в документе тоже тогда сущность.

                                                                                                                            "independent existence" означает состояние и поведение, что означает необходимость определять объект как один и тот же в разные моменты изменения поведения. Если мы хотим выделять количество как один и тот же объект в разные моменты времени, то да, это будет экземпляр сущности, а его тип сущностью. В соответствующих предметных областях выделяют такие классы. Например в каких-нибудь анализаторах кода будет объект, соответствующий интовой переменной в анализируемом коде.


                                                                                                                            У количества в документе мы состояние и поведение не выделяем, поэтому оно не сущность. А у документа выделяем, поэтому оно сущность.


                                                                                                                            У меня более конкретное определение объекта — то, что явно создается при помощи new, имеет идентификатор (один) и т.п.

                                                                                                                            Ну вы же уточняли, что я подразумеваю, я подразумеваю общепринятое определение. То, что вы используете другое определение, не делает мои рассуждения неправильными.


                                                                                                                            Если общепринятое определение вам не подходит, вам надо создать полностью свою непротиворечивую систему терминов и предупреждать об этом, чтобы не вводить в заблуждение собеседников. Например вам нужно дать название тому, что создается при помощи new, но имеет 2 поля для идентификатора, пояснить, откуда в логической предметной области взялся какой-то оператор new, либо не использовать термины "сущность" и "объект" вне контекста языка программирования, и т.д.

                                                                                                                              0
                                                                                                                              У количества в документе мы состояние и поведение не выделяем, поэтому оно не сущность

                                                                                                                              Не понимаю в чем разница количества в документе и цены товара на складе. Кроме того что первый показатель от одного объекта, а второй от двух.
                                                                                                                              Если общепринятое определение вам не подходит, вам надо создать полностью свою непротиворечивую систему терминов и предупреждать об этом, чтобы не вводить в заблуждение собеседников

                                                                                                                              Мое соответствует классическому ООП. Там есть классы и их можно делать new. Все остальное методы / процедуры.
                                                                                                                              Например вам нужно дать название тому, что создается при помощи new, но имеет 2 поля для идентификатора

                                                                                                                              То что создается при помощи new имеет идентификатор — в C++ это адрес в памяти, в java внутренний id и т.п… А 2 поля идентификатора — это костыли ORM.

                                                                                                                              А вообще надо про это статью написать. В смысле Сущность vs Класс.
                                                                                                                                0
                                                                                                                                Не понимаю в чем разница количества в документе и цены товара на складе.

                                                                                                                                Не знаю, это у вас надо спросить, зачем вы сделали отдельную таблицу с изменением этой цены, да еще и отдельное представление по этой таблице. И почему для количества не сделали.


                                                                                                                                Мое соответствует классическому ООП. Там есть классы и их можно делать new.

                                                                                                                                И-и? Вот у нас в приложении не было строки из представления last_prices в базе данных, а вот появилась. Какой операцией она создается в оперативной памяти?


                                                                                                                                То что создается при помощи new имеет идентификатор — в C++ это адрес в памяти, в java внутренний id и т.п… А 2 поля идентификатора — это костыли ORM.

                                                                                                                                Да причем тут ORM-то?) У вас в коде нет ORM, а таблицы с двумя идентификаторами есть.

                                                                                                                                  0
                                                                                                                                  То что создается при помощи new имеет идентификатор — в C++ это адрес в памяти, в java внутренний id и т.п… А 2 поля идентификатора — это костыли ORM.

                                                                                                                                  Сущность с двумя идентификаторами это связь, ее существование автоматически вытекает из существования нескольких сущностей. Если у нас есть 2 сущности, мы можем создать между ними связь. Будет ли она отражать реальную связь из предметной области, это другой вопрос.


                                                                                                                                  Более того. Обычные связи один-ко-многим типа product.category_id, это частный случай связи с отдельной таблицей. И их точно так же можно моделировать отдельными таблицами с двумя столбцами, вообще все связи можно так моделировать, задавая соответствущие ограничения на столбцы. Просто так никто не делает по техническим причинам, просто помещают в ту таблицу, которая со стороны "много", и на другом конце связи получается всегда "один" объект. А для связи многие-ко-многим так упростить не получится.


                                                                                                                                  По поводу C++. Покажите, как будет выглядеть структура классов, где есть авторы, книги, и их взаимосвязь, без работы с БД. Естественно с возможностью получить все книги конкретного автора и всех авторов конкретной книги.

                                                                            0
                                                                            Вот и надо говорить о проблемах и альтернативах. Возможно для кого-то некоторое снижение производительности будет приемлемо для уменьшения вероятности ошибки и увеличения удобности разработки.

                                                                            Проблемы и альтернативы это разные вещи. Хотя в статье как раз есть много и про альтернативы (и как это сделано в lsFusion).

                                                                            Пока писал уже ответили на остальное.
                                                                0
                                                                О проталкивании условий в подзапрос — у меня работает (pg 11), если изменить запрос, чтоб использовался индекс:

                                                                SELECT SUM(cc.ls) 
                                                                        FROM Product pr
                                                                        LEFT JOIN (SELECT MAX(shipment) AS ls, s.product
                                                                            FROM shipmentDetail s
                                                                            GROUP BY s.product) cc ON cc.product=pr.id
                                                                        WHERE pr.id = 1; -- name LIKE 'Product 86%';


                                                                то есть, оно в принципе есть.



                                                                А как оно должно в вашем запросе работать, какой предикат протолкнется в подзапрос, если условие по имени продукта, а подзапрос про имена продуктов не знает?

                                                                Вот для сравнения запрос с lateral и план, работает только по отфильтрованным строкам.

                                                                SELECT SUM(cc.ls) 
                                                                  FROM Product pr
                                                                  LEFT JOIN lateral ( SELECT MAX(shipment) AS ls
                                                                            FROM shipmentDetail s
                                                                		    where s.product = pr.id
                                                                            ) cc on true
                                                                        WHERE pr.name LIKE 'Product 86%';


                                                                  0
                                                                  то есть, оно в принципе есть.

                                                                  Так она не join протолкнула, а значение. То есть PPD там есть, JPPD нет.
                                                                  А как оно должно в вашем запросе работать, какой предикат протолкнется в подзапрос, если условие по имени продукта, а подзапрос про имена продуктов не знает?

                                                                  Я в общем то в статье описал как это делают другие субд. Но по сути да, они вставляют что-то вроде lateral, как вы и написали.

                                                                  Что кстати весьма забавно, что lateral PostgreSQL поддерживает, а JPPD нет.
                                                                    0
                                                                    Тут фишка видимо в том, что lateral это гарантировано nested loop join.

                                                                    То есть вот такой запрос:
                                                                    SELECT SUM(cc.ls) 
                                                                      FROM Product pr
                                                                      LEFT JOIN lateral ( SELECT SUM(shipment/1000) AS ls
                                                                                FROM shipmentDetail s
                                                                    		    where s.product = pr.id
                                                                                ) cc on true
                                                                            ;
                                                                    

                                                                    Дает вот такой план:
                                                                    "Aggregate  (cost=40238877.72..40238877.73 rows=1 width=32) (actual time=18344.109..18344.109 rows=1 loops=1)"
                                                                    "  ->  Nested Loop Left Join  (cost=804.71..40238752.71 rows=50001 width=8) (actual time=0.615..18335.300 rows=50001 loops=1)"
                                                                    "        ->  Seq Scan on product pr  (cost=0.00..819.01 rows=50001 width=4) (actual time=0.014..12.254 rows=50001 loops=1)"
                                                                    "        ->  Aggregate  (cost=804.71..804.72 rows=1 width=8) (actual time=0.365..0.366 rows=1 loops=50001)"
                                                                    "              ->  Bitmap Heap Scan on shipmentdetail s  (cost=6.04..803.68 rows=207 width=4) (actual time=0.042..0.340 rows=200 loops=50001)"
                                                                    "                    Recheck Cond: (product = pr.id)"
                                                                    "                    Heap Blocks: exact=9986478"
                                                                    "                    ->  Bitmap Index Scan on shipmentdetail_p_s  (cost=0.00..5.99 rows=207 width=0) (actual time=0.020..0.020 rows=200 loops=50001)"
                                                                    "                          Index Cond: (product = pr.id)"
                                                                    "Planning Time: 0.194 ms"
                                                                    "Execution Time: 18344.192 ms"
                                                                    


                                                                    А его то как раз эффективнее вот так выполнять:
                                                                    SELECT SUM(cc.ls)
                                                                      FROM Product pr
                                                                      LEFT JOIN ( SELECT product, SUM(shipment/1000) AS ls
                                                                                FROM shipmentDetail s
                                                                    		    GROUP BY product
                                                                                ) cc on cc.product = pr.id
                                                                            ;
                                                                    

                                                                    "Aggregate  (cost=160706.41..160706.42 rows=1 width=32) (actual time=3355.234..3355.235 rows=1 loops=1)"
                                                                    "  ->  Hash Left Join  (cost=159631.13..160581.41 rows=50001 width=8) (actual time=3324.236..3351.385 rows=50001 loops=1)"
                                                                    "        Hash Cond: (pr.id = cc.product)"
                                                                    "        ->  Seq Scan on product pr  (cost=0.00..819.01 rows=50001 width=4) (actual time=0.025..5.641 rows=50001 loops=1)"
                                                                    "        ->  Hash  (cost=159027.97..159027.97 rows=48253 width=12) (actual time=3323.202..3323.202 rows=50000 loops=1)"
                                                                    "              Buckets: 65536  Batches: 1  Memory Usage: 2661kB"
                                                                    "              ->  Subquery Scan on cc  (cost=158062.91..159027.97 rows=48253 width=12) (actual time=3285.821..3308.588 rows=50000 loops=1)"
                                                                    "                    ->  Finalize HashAggregate  (cost=158062.91..158545.44 rows=48253 width=12) (actual time=3285.819..3301.673 rows=50000 loops=1)"
                                                                    "                          Group Key: s.product"
                                                                    "                          ->  Gather  (cost=147447.25..157580.38 rows=96506 width=12) (actual time=3168.262..3214.404 rows=150000 loops=1)"
                                                                    "                                Workers Planned: 2"
                                                                    "                                Workers Launched: 2"
                                                                    "                                ->  Partial HashAggregate  (cost=146447.25..146929.78 rows=48253 width=12) (actual time=3153.383..3168.983 rows=50000 loops=3)"
                                                                    "                                      Group Key: s.product"
                                                                    "                                      ->  Parallel Seq Scan on shipmentdetail s  (cost=0.00..115197.00 rows=4166700 width=8) (actual time=0.024..801.467 rows=3333334 loops=3)"
                                                                    "Planning Time: 0.383 ms"
                                                                    "Execution Time: 3358.020 ms"
                                                                    


                                                                    То есть PostgreSQL не принимает никакого решения, а что сказали то и делаю.