💐 🌌 📹 لماذا لا SQL؟ 🏇🏾 🤷🏾 🌅

بدون المبالغة ، يمكننا القول أن لغة SQL واحدة من أكثر اللغات شيوعًا في العالم. يمكن كتابة أنظمة المعلومات بلغات Java و Python و JavaScript و C # و PHP وعشرات اللغات الأخرى ، لكن قاعدة بيانات SQL بشكل أو بآخر ستكون في الغالبية العظمى من هذه الأنظمة. بين تطبيقات الأعمال ، تميل النسبة المئوية للأنظمة التي تستخدم SQL إلى 100٪.

في الوقت نفسه ، فإن معظم المواد الموجودة حول SQL على لوحة الوصل والموارد الأخرى تتحول إلى أسئلة بسيطة ، مثل: "ما هي أنواع الاتصالات" ، "كيف يختلف الاتصال الأيسر عن اليمين" ، "ما هي المشغلات" وما إلى ذلك. علاوة على ذلك ، في هذه المواد لا يُقال شيئًا عن مشكلات SQL (وتنفيذاتها) ، وهي في الواقع كثيرة جدًا. لذلك ، قررنا ملء كل من هذه الفجوات: من ناحية ، لمعرفة عدد الأشياء في SQL تعمل من الداخل ، ومن ناحية أخرى ، لماذا لا تعمل كما ينبغي / ترغب في ذلك.

في الوقت نفسه ، لا تتناول المقالة "أذواق وألوان الأقلام ذات الرأس". جميع المشكلات التي ينطوي عليها الأمر أساسية: فهي موجودة أثناء تطوير أي نظام معلومات تقريبًا ولا تقتصر على "جمال الكود" ، ولكن إلى درجة أو أخرى تؤدي إما إلى انخفاض حاد في الإنتاجية ، أو إلى زيادة كبيرة في عتبة الدخول ، أو إلى تكاليف العمالة الكبيرة من جانب المطور.

تبين أن المقال ضخم للغاية ، ولا يمكن لأي شخص إتقانه في وقت واحد. لذلك ، لجعله أكثر ملاءمة للتنقل فيه ، وكذلك لتكون قادرًا على تقييم "حجم الكارثة" ، نبدأ بجدول محتويات مع قائمة بجميع المشاكل التي تمت معالجتها:

سيتم اختبار كل شيء على ثلاثة RDBMSs - PostgreSQL و MS SQL Server و Oracle. جميع أحدث نظم إدارة قواعد البيانات هي 11 و 2019 و 19 c ، على التوالي.

قبل البدء ، سنقوم بإنشاء قاعدة بيانات مع البضائع والمقاولين ووثائق الإيصالات والشحنات. سنملأها بالبيانات وبكميات كبيرة ، حيث سيكون هناك العديد من الأمثلة التي توضح مشاكل تحسين الاستعلام / الأداء على كميات كبيرة من البيانات.

ملء قاعدة البيانات النصي في أوراكل

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 , , , , . - - , , .

لماذا لا 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: