andrey_anonymous,
Я уменьшил количество не null sd до 100 тоже начал использоваться. Меня на самом деле интересовала принципиальная возможность использования этого индекса (правда в другом запросе), так что я получил ответ на свой вопрос.
Но раз пошла такая пьянка, есть еще интересная вещь.
Кроме таблицы shipmentDetail сверху есть еще таблица product (с товарами).
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
CREATE TABLE PRODUCT
( "ID" NUMBER(*,0)
"NAME" VARCHAR2(100 BYTE)
)
INSERT INTO Product (id, name)
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 FROM numbers;
И индекс по product, shipment в shipmentDetail:
1.
CREATE INDEX shipment_p_s ON shipmentDetail(product, shipment)
Делаю вот такой запрос:
1.
SELECT MAX(shipment) AS ls FROM ShipmentDetail s WHERE s.product = 123
Получаю план
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
SQL_ID 5fw3ztm7dz5hm, child number 0
-------------------------------------
SELECT MAX(shipment) AS ls FROM ShipmentDetail s WHERE s.product = 123
Plan hash value: 157083448
---------------------------------------------------------------------------------------------
| 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)| SHIPMENT_P_S | 1 | 10 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / S@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "S"@"SEL$1" ("SHIPMENTDETAIL"."PRODUCT" "SHIPMENTDETAIL"."SHIPMENT"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("S"."PRODUCT"=123)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) MAX("SHIPMENT")[22]
2 - "SHIPMENT"[NUMBER,22]
3 - "SHIPMENT"[NUMBER,22]
Все хорошо. Oracle использует индекс и что самое главное догадывается вставить FIRST ROW.
Теперь делаю тот же запрос с Predicate push down
1.
2.
3.
SELECT cc.ls FROM Product pr, (SELECT MAX(shipment) AS ls, s.product
FROM ShipmentDetail s
GROUP BY s.product) cc WHERE cc.product=pr.id AND pr.name LIKE 'Product 86%';
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
SQL_ID bm3dxvsk1p64p, child number 0
-------------------------------------
SELECT cc.ls FROM Product pr, (SELECT MAX(shipment) AS ls, s.product
FROM ShipmentDetail s GROUP BY s.product) cc WHERE
cc.product=pr.id AND pr.name LIKE 'Product 86%'
Plan hash value: 3614704251
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 120 (100)| |
| 1 | HASH GROUP BY | | 4898 | 138K| 120 (2)| 00:00:01 |
| 2 | NESTED LOOPS | | 4898 | 138K| 118 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| PRODUCT | 25 | 475 | 68 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | SHIPMENT_P_S | 198 | 1980 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$F5BB74E1
3 - SEL$F5BB74E1 / PR@SEL$1
4 - SEL$F5BB74E1 / S@SEL$2
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$F5BB74E1")
MERGE(@"SEL$2" >"SEL$1")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
FULL(@"SEL$F5BB74E1" "PR"@"SEL$1")
INDEX(@"SEL$F5BB74E1" "S"@"SEL$2" ("SHIPMENTDETAIL"."PRODUCT"
"SHIPMENTDETAIL"."SHIPMENT"))
LEADING(@"SEL$F5BB74E1" "PR"@"SEL$1" "S"@"SEL$2")
USE_NL(@"SEL$F5BB74E1" "S"@"SEL$2")
USE_HASH_AGGREGATION(@"SEL$F5BB74E1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("PR"."NAME" LIKE 'Product 86%')
4 - access("S"."PRODUCT"="PR"."ID")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (rowset=256) "S"."PRODUCT"[NUMBER,22], ROWID[ROWID,10],
MAX("SHIPMENT")[22]
2 - ROWID[ROWID,10], "SHIPMENT"[NUMBER,22], "S"."PRODUCT"[NUMBER,22]
3 - ROWID[ROWID,10], "PR"."ID"[NUMBER,22]
4 - "SHIPMENT"[NUMBER,22], "S"."PRODUCT"[NUMBER,22]
В плане никакого FIRST ROW (должен быть над INDEX) нет. Ну и хер бы с ним, записей не много. Делаем с большим количеством записей (просто убирая фильтр по имени).
1.
2.
3.
SELECT cc.ls FROM Product pr, (SELECT MAX(shipment) AS ls, s.product
FROM ShipmentDetail s
GROUP BY s.product) cc WHERE cc.product=pr.id;
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
SQL_ID 98719kzpx2bbd, child number 0
-------------------------------------
SELECT cc.ls FROM Product pr, (SELECT MAX(shipment) AS ls, s.product
FROM ShipmentDetail s GROUP BY s.product) cc WHERE
cc.product=pr.id
Plan hash value: 1879424675
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 22329 (100)| |
|* 1 | HASH JOIN | | 50000 | 1513K| | 22329 (2)| 00:00:01 |
| 2 | TABLE ACCESS FULL | PRODUCT | 50000 | 244K| | 68 (0)| 00:00:01 |
| 3 | VIEW | | 50536 | 1283K| | 22260 (2)| 00:00:01 |
| 4 | HASH GROUP BY | | 50536 | 493K| 191M| 22260 (2)| 00:00:01 |
| 5 | INDEX FAST FULL SCAN| SHIPMENT_P_S | 10M| 95M| | 7912 (1)| 00:00:01 |
------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / PR@SEL$1
3 - SEL$2 / CC@SEL$1
4 - SEL$2
5 - SEL$2 / S@SEL$2
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "PR"@"SEL$1")
NO_ACCESS(@"SEL$1" "CC"@"SEL$1")
LEADING(@"SEL$1" "PR"@"SEL$1" "CC"@"SEL$1")
USE_HASH(@"SEL$1" "CC"@"SEL$1")
INDEX_FFS(@"SEL$2" "S"@"SEL$2" ("SHIPMENTDETAIL"."PRODUCT" "SHIPMENTDETAIL"."SHIPMENT"))
USE_HASH_AGGREGATION(@"SEL$2")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("CC"."PRODUCT"="PR"."ID")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=1; rowset=256) "CC"."LS"[NUMBER,22]
2 - (rowset=256) "PR"."ID"[NUMBER,22]
3 - (rowset=256) "CC"."LS"[NUMBER,22], "CC"."PRODUCT"[NUMBER,22]
4 - (rowset=256) "S"."PRODUCT"[NUMBER,22], MAX("SHIPMENT")[22]
5 - "SHIPMENT"[NUMBER,22], "S"."PRODUCT"[NUMBER,22]
Опять никакого FIRST ROW и запрос выполняется почти 3 секунды.
Переписываем запрос с явным rownum = 1 (SUM пришлось добавить, не в курсе как SQL Developer заставить не fetch'ить запросы)
1.
SELECT SUM(l) FROM (SELECT (SELECT shipment FROM (SELECT shipment FROM ShipmentDetail s WHERE s.product = pr.id ORDER BY s.shipment) s WHERE rownum = 1) AS l FROM product pr)
Получаю нормальный план:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
SQL_ID 8ut826vdv0gr6, child number 0
-------------------------------------
SELECT SUM(l) FROM (SELECT (SELECT shipment FROM (SELECT shipment FROM
ShipmentDetail s WHERE s.product = pr.id ORDER BY s.shipment) s WHERE
rownum = 1) AS l FROM product pr)
Plan hash value: 3251716916
-----------------------------------------------------------------------------------
| 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| SHIPMENT_P_S | 2 | 20 | 3 (0)| 00:00:01 |
| 4 | SORT AGGREGATE | | 1 | 5 | | |
| 5 | TABLE ACCESS FULL| PRODUCT | 50000 | 244K| 68 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$3
2 - SEL$4 / S@SEL$3
3 - SEL$4 / S@SEL$4
4 - SEL$F5BB74E1
5 - SEL$F5BB74E1 / PR@SEL$2
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$4")
OUTLINE_LEAF(@"SEL$3")
OUTLINE_LEAF(@"SEL$F5BB74E1")
MERGE(@"SEL$2" >"SEL$1")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
FULL(@"SEL$F5BB74E1" "PR"@"SEL$2")
NO_ACCESS(@"SEL$3" "S"@"SEL$3")
INDEX(@"SEL$4" "S"@"SEL$4" ("SHIPMENTDETAIL"."PRODUCT"
"SHIPMENTDETAIL"."SHIPMENT"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
3 - access("S"."PRODUCT"=:B1)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "SHIPMENT"[NUMBER,22]
2 - "SHIPMENT"[NUMBER,22]
3 - "S"."SHIPMENT"[NUMBER,22]
4 - (#keys=0) SUM()[22]
5 - "PR"."ID"[NUMBER,22]
То есть с FIRST ROW (в данном случае COUNT STOPKEY, но суть та же) и временем выполнения 400 мс в 10 раз меньше. Причем те же яйца в MS SQL.
Это что за фигня? Почему когда предикат равен значению оптимизатор понимает что надо взять одну запись, а при join predicate push down нет. Там же по идее одинаковые оптимизаторы должны быть. И здесь тяжело объяснить это cost'ами или неправильной статистикой, потому как FIRST ROW нужно по идее в любом случае вставлять.
