Why not SQL?

Without exaggeration, we can say that SQL is one of the most common languages in the world. Information systems can be written in Java, Python, JavaScript, C #, PHP and a dozen other languages, but the SQL database in one form or another will be in the vast majority of such systems. Among business applications, the percentage of systems using SQL tends to be 100%.

At the same time, most of the existing materials about SQL on the hub and other resources come down to simple questions, such as: “what types of connections are”, “how the left connection differs from the right”, “what are triggers” and so on. Moreover, in these materials practically nothing is said about SQL problems (and its implementations), which are actually very, very many. Therefore, we decided to fill both of these gaps: on the one hand, to tell how many things in SQL work from the inside, and on the other hand, why they do not work as they should / would like to.

At the same time, the article is not about “tastes and colors of felt-tip pens”. All the problems involved are fundamental: they are present during the development of almost any information system and are not limited to the “beauty of the code,” but to one degree or another they lead either to a critical drop in productivity, to a significant increase in the entry threshold, or to significant labor costs on the part of the developer.

The article turned out to be quite voluminous, and not everyone can master it at a time. Therefore, to make it more convenient to navigate in it, as well as to be able to assess the "scale of the disaster", we begin with a table of contents with a list of all the problems addressed:

Everything will be tested on three RDBMSs - PostgreSQL, MS SQL Server and Oracle. All the latest DBMSs are 11, 2019, and 19c, respectively.

Before you begin, create a database with goods, contractors and documents of receipts and shipments. We’ll fill it with data, and with a large amount, as there will be many more examples demonstrating the problems of query / performance optimization on large volumes of data.

Database Fill Script in 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 , , , , . - - , , .

All Articles

Why not SQL?

(View)

View:

View:

View: FROM

Join Predicate Push Down (JPPD)

JPPD: CTE

JPPD:

JPPD:

JOIN WHERE

OR

N+1

JOIN

(AO)

AO:

AO:

More articles: