From 7b175fa90c363fbed96aa4c55593f9dbe6f03ada Mon Sep 17 00:00:00 2001 From: Andrew Date: Tue, 14 Dec 2021 09:18:15 +0400 Subject: =?UTF-8?q?=D0=98=D1=81=D0=BF=D1=80=D0=B0=D0=B2=D0=B8=D0=BB=20?= =?UTF-8?q?=D1=87=D0=B5=D1=82=D0=B2=D1=91=D1=80=D1=82=D1=83=D1=8E=20=D0=BB?= =?UTF-8?q?=D0=B0=D0=B1=D1=83.?= MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit --- reports/task4/images/columns-indexed.png | Bin 205133 -> 173375 bytes reports/task4/images/columns-not-indexed.png | Bin 211133 -> 184149 bytes reports/task4/images/columns-requests.png | Bin 782748 -> 643768 bytes reports/task4/images/composite-indexed.png | Bin 274659 -> 174729 bytes reports/task4/images/composite-not-indexed.png | Bin 326015 -> 186116 bytes reports/task4/images/composite-requests.png | Bin 845982 -> 665722 bytes reports/task4/images/covering-indexed.png | Bin 220259 -> 185919 bytes reports/task4/images/covering-not-indexed.png | Bin 238546 -> 180969 bytes reports/task4/images/covering-requests.png | Bin 790068 -> 557419 bytes task4_requests.sql | 26 +++++++++---------------- 10 files changed, 9 insertions(+), 17 deletions(-) diff --git a/reports/task4/images/columns-indexed.png b/reports/task4/images/columns-indexed.png index 7eb787e..030e39d 100644 Binary files a/reports/task4/images/columns-indexed.png and b/reports/task4/images/columns-indexed.png differ diff --git a/reports/task4/images/columns-not-indexed.png b/reports/task4/images/columns-not-indexed.png index c6f9f6b..edd0fc0 100644 Binary files a/reports/task4/images/columns-not-indexed.png and b/reports/task4/images/columns-not-indexed.png differ diff --git a/reports/task4/images/columns-requests.png b/reports/task4/images/columns-requests.png index 95a448a..dae914c 100644 Binary files a/reports/task4/images/columns-requests.png and b/reports/task4/images/columns-requests.png differ diff --git a/reports/task4/images/composite-indexed.png b/reports/task4/images/composite-indexed.png index 1c65e4a..3303483 100644 Binary files a/reports/task4/images/composite-indexed.png and b/reports/task4/images/composite-indexed.png differ diff --git a/reports/task4/images/composite-not-indexed.png b/reports/task4/images/composite-not-indexed.png index f022e75..6faaa97 100644 Binary files a/reports/task4/images/composite-not-indexed.png and b/reports/task4/images/composite-not-indexed.png differ diff --git a/reports/task4/images/composite-requests.png b/reports/task4/images/composite-requests.png index 341cf58..df496bb 100644 Binary files a/reports/task4/images/composite-requests.png and b/reports/task4/images/composite-requests.png differ diff --git a/reports/task4/images/covering-indexed.png b/reports/task4/images/covering-indexed.png index 1bba316..10ccda3 100644 Binary files a/reports/task4/images/covering-indexed.png and b/reports/task4/images/covering-indexed.png differ diff --git a/reports/task4/images/covering-not-indexed.png b/reports/task4/images/covering-not-indexed.png index 4236129..cf1f540 100644 Binary files a/reports/task4/images/covering-not-indexed.png and b/reports/task4/images/covering-not-indexed.png differ diff --git a/reports/task4/images/covering-requests.png b/reports/task4/images/covering-requests.png index 77c2409..091896b 100644 Binary files a/reports/task4/images/covering-requests.png and b/reports/task4/images/covering-requests.png differ diff --git a/task4_requests.sql b/task4_requests.sql index fc41e5d..a7eee14 100644 --- a/task4_requests.sql +++ b/task4_requests.sql @@ -16,10 +16,8 @@ GO -- составной -SELECT Boxes.id, Orders.id, date -FROM Boxes JOIN Orders -ON Boxes.order_id = Orders.id -WHERE Boxes.warehouse_id = 12465 +SELECT id, warehouse_id FROM Boxes +WHERE warehouse_id = 12465 GO CREATE INDEX Boxes_Composite ON Boxes (order_id, warehouse_id); @@ -30,11 +28,8 @@ GO -- покрывающий SELECT * FROM Orders -WHERE EXISTS( - SELECT * FROM Customers - WHERE customer_id = Customers.id - AND Customers.email LIKE N'%@mail.ru' -); +WHERE date > '2021-09-04' +GO CREATE INDEX Orders_Index_Covering ON Orders (id) INCLUDE (customer_id, city_id, date); @@ -53,15 +48,12 @@ GO -- индекс с включеными столбцами -SELECT date FROM Orders -WHERE EXISTS( - SELECT * FROM Customers - WHERE customer_id = Customers.id - AND Customers.email LIKE N'%@mail.ru' -); +SELECT id, customer_id, date FROM Orders +WHERE customer_id = 12345 +GO -CREATE INDEX Orders_Index_Columns ON Orders (id) - INCLUDE (customer_id, date); +CREATE INDEX Orders_Index_Columns ON Orders (customer_id) + INCLUDE (id, date); GO DROP INDEX Orders_Index_Columns ON Orders; GO -- cgit v1.2.3