Есть запрос
SELECT BLOCK,
BLOCK_TO,
BLOCK_COLOR,
BLOCK_BLINK,
LINE_BLINK,
LINE_COLOR,
ARROW,
ARROW_DIRECTION,
STATETIME
FROM SPWAY.RU_DU R,
SPWAY.DU D,
SPWAY.BLOCK B,
SPWAY.SCB_MARSHRUT_EVENTS M
WHERE M.BLOCK = B.BLOCK
AND B.KDU = D.KDU
AND D.ID = R.DU_ID
AND :polygon_id IN(R.DU_ID, R.RU_ID)
CONNECT BY PRIOR BLOCK_TO = M.BLOCK
START WITH NOT EXISTS (
SELECT 1
FROM SPWAY.SCB_MARSHRUT_EVENTS
WHERE M.BLOCK = BLOCK_TO
)
MODEL RETURN ALL ROWS
PARTITION BY (
CONNECT_BY_ISLEAF*BLOCK_TO p
)
DIMENSION BY (
CONNECT_BY_ISLEAF l,
BLOCK_TO bt
)
MEASURES (
LEVEL lv,
CONNECT_BY_ROOT M.BLOCK lb,
M.BLOCK BLOCK,
BLOCK_TO,
BLOCK_COLOR,
BLOCK_BLINK,
LINE_BLINK,
LINE_COLOR,
ARROW,
ARROW_DIRECTION,
BLOCK_TO_COLOR,
STATETIME
)
RULES (
UPSERT ALL BLOCK[l=1,0] = MAX(BLOCK_TO)[1,ANY],
lb[l=1,bt=0] = MAX(lb)[1,ANY],
BLOCK_COLOR[l=1,bt=0] = MAX(BLOCK_TO_COLOR)[1,ANY],
STATETIME[l=1,bt=0] = MAX(STATETIME)[1,ANY],
lv[l=1,bt=0] = 100
)
ORDER BY LB,LV
И его план
SELECT STATEMENT ALL_ROWSCost: 28 Bytes: 45,480 Cardinality: 758
24 SORT ORDER BY Cost: 28 Bytes: 45,480 Cardinality: 758
23 SQL MODEL ORDERED Cost: 28 Bytes: 45,480 Cardinality: 758
22 FILTER
21 CONNECT BY WITH FILTERING
10 FILTER
8 COUNT
7 HASH JOIN Cost: 27 Bytes: 45,480 Cardinality: 758
1 TABLE ACCESS FULL TABLE SPWAY.RU_DU Cost: 3 Bytes: 112 Cardinality: 16
6 HASH JOIN Cost: 23 Bytes: 40,174 Cardinality: 758
2 TABLE ACCESS FULL TABLE SPWAY.DU Cost: 3 Bytes: 128 Cardinality: 16
5 HASH JOIN Cost: 20 Bytes: 36,225 Cardinality: 805
3 TABLE ACCESS FULL TABLE SPWAY.SCB_MARSHRUT_EVENTS Cost: 5 Bytes: 28,175 Cardinality: 805
4 TABLE ACCESS FULL TABLE SPWAY.BLOCK Cost: 14 Bytes: 66,870 Cardinality: 6,687
9 TABLE ACCESS FULL TABLE SPWAY.SCB_MARSHRUT_EVENTS Cost: 5 Bytes: 5 Cardinality: 1
20 HASH JOIN
11 CONNECT BY PUMP
19 COUNT
18 HASH JOIN Cost: 27 Bytes: 45,480 Cardinality: 758
12 TABLE ACCESS FULL TABLE SPWAY.RU_DU Cost: 3 Bytes: 112 Cardinality: 16
17 HASH JOIN Cost: 23 Bytes: 40,174 Cardinality: 758
13 TABLE ACCESS FULL TABLE SPWAY.DU Cost: 3 Bytes: 128 Cardinality: 16
16 HASH JOIN Cost: 20 Bytes: 36,225 Cardinality: 805
14 TABLE ACCESS FULL TABLE SPWAY.SCB_MARSHRUT_EVENTS Cost: 5 Bytes: 28,175 Cardinality: 805
15 TABLE ACCESS FULL TABLE SPWAY.BLOCK Cost: 14 Bytes: 66,870 Cardinality: 6,687
Если его переписать вот так, то план будет намного проще и оптимальнее и работает он в два раза быстрее
SELECT *
FROM
(
SELECT BLOCK,
BLOCK_TO,
BLOCK_COLOR,
BLOCK_BLINK,
LINE_BLINK,
LINE_COLOR,
ARROW,
ARROW_DIRECTION,
STATETIME
FROM SPWAY.RU_DU R,
SPWAY.DU D,
SPWAY.BLOCK B,
SPWAY.SCB_MARSHRUT_EVENTS M
WHERE M.BLOCK = B.BLOCK
AND B.KDU = D.KDU
AND D.ID = R.DU_ID
AND :polygon_id IN(R.DU_ID, R.RU_ID)
MODEL RETURN ALL ROWS
PARTITION BY (
nvl((select max(0) from SPWAY.SCB_MARSHRUT_EVENTS where block=m.block_to),1)*BLOCK_TO p
)
DIMENSION BY (
nvl((select max(0) from SPWAY.SCB_MARSHRUT_EVENTS where block=m.block_to),1) l,
BLOCK_TO bt
)
MEASURES (
M.BLOCK BLOCK,
BLOCK_TO,
BLOCK_COLOR,
BLOCK_BLINK,
LINE_BLINK,
LINE_COLOR,
ARROW,
ARROW_DIRECTION,
BLOCK_TO_COLOR,
STATETIME
)
RULES (
UPSERT ALL BLOCK[l=1,0] = MAX(BLOCK_TO)[1,ANY],
BLOCK_COLOR[l=1,bt=0] = MAX(BLOCK_TO_COLOR)[1,ANY],
STATETIME[l=1,bt=0] = MAX(STATETIME)[1,ANY]
)
)
CONNECT BY PRIOR BLOCK = BLOCK_TO
START WITH BLOCK_TO IS NULL
ORDER BY CONNECT_BY_ROOT BLOCK, -LEVEL
план соответственно
SELECT STATEMENT ALL_ROWSCost: 28 Bytes: 21,357 Cardinality: 189
11 SORT ORDER BY Cost: 28 Bytes: 21,357 Cardinality: 189
10 CONNECT BY NO FILTERING WITH START-WITH
9 VIEW APP_SERV. Cost: 27 Bytes: 21,357 Cardinality: 189
8 SQL MODEL ORDERED Bytes: 11,340 Cardinality: 189
7 HASH JOIN Cost: 27 Bytes: 11,340 Cardinality: 189
1 TABLE ACCESS FULL TABLE SPWAY.RU_DU Cost: 3 Bytes: 28 Cardinality: 4
6 HASH JOIN Cost: 23 Bytes: 40,174 Cardinality: 758
2 TABLE ACCESS FULL TABLE SPWAY.DU Cost: 3 Bytes: 128 Cardinality: 16
5 HASH JOIN Cost: 20 Bytes: 36,225 Cardinality: 805
3 TABLE ACCESS FULL TABLE SPWAY.SCB_MARSHRUT_EVENTS Cost: 5 Bytes: 28,175 Cardinality: 805
4 TABLE ACCESS FULL TABLE SPWAY.BLOCK Cost: 14 Bytes: 66,870 Cardinality: 6,687
Вопрос, как первоначальный запрос заставить работать также быстро, какие хинты нужно прописать в него и что поменять?
Было бы интересно услышать мнение специалистов.
Удалось добавить в первоначальный запрос хинт NO_CONNECT_BY_FILTERING и план запроса в результате все же стал лучше, да и запрос немного ускорился.
SELECT STATEMENT ALL_ROWSCost: 28 Bytes: 45,480 Cardinality: 758
13 SORT ORDER BY Cost: 28 Bytes: 45,480 Cardinality: 758
12 SQL MODEL ORDERED Cost: 28 Bytes: 45,480 Cardinality: 758
11 FILTER
10 CONNECT BY NO FILTERING WITH START-WITH
8 COUNT
7 HASH JOIN Cost: 27 Bytes: 45,480 Cardinality: 758
1 TABLE ACCESS FULL TABLE SPWAY.RU_DU Cost: 3 Bytes: 112 Cardinality: 16
6 HASH JOIN Cost: 23 Bytes: 40,174 Cardinality: 758
2 TABLE ACCESS FULL TABLE SPWAY.DU Cost: 3 Bytes: 128 Cardinality: 16
5 HASH JOIN Cost: 20 Bytes: 36,225 Cardinality: 805
3 TABLE ACCESS FULL TABLE SPWAY.SCB_MARSHRUT_EVENTS Cost: 5 Bytes: 28,175 Cardinality: 805
4 TABLE ACCESS FULL TABLE SPWAY.BLOCK Cost: 14 Bytes: 66,870 Cardinality: 6,687
9 TABLE ACCESS FULL TABLE SPWAY.SCB_MARSHRUT_EVENTS Cost: 5 Bytes: 5 Cardinality: 1