За да го заявите различно: Имайки (сложна) заявка с JOIN, SUBSELECT, UNION, възможно ли е (или не) да я намалите до по-прост, еквивалентен SQL оператор, който дава същия резултат, като използвате някои правила за трансформация?
Точно с това си изкарват прехраната оптимизаторите (не че казвам, че винаги се справят добре).
Тъй като SQL е базиран на набор език, обикновено има повече от един начин за преобразуване на една заявка в друга.
Като тази заявка:
SELECT *
FROM mytable
WHERE col1 > @value1 OR col2 < @value2
може да се трансформира в това:
SELECT *
FROM mytable
WHERE col1 > @value1
UNION
SELECT *
FROM mytable
WHERE col2 < @value2
или това:
SELECT mo.*
FROM (
SELECT id
FROM mytable
WHERE col1 > @value1
UNION
SELECT id
FROM mytable
WHERE col2 < @value2
) mi
JOIN mytable mo
ON mo.id = mi.id
, които изглеждат по-грозни, но могат да доведат до по-добри планове за изпълнение.
Едно от най-често срещаните неща е да замените тази заявка:
SELECT *
FROM mytable
WHERE col IN
(
SELECT othercol
FROM othertable
)
с този:
SELECT *
FROM mytable mo
WHERE EXISTS
(
SELECT NULL
FROM othertable o
WHERE o.othercol = mo.col
)
В някои RDBMS (като PostgreSQL), DISTINCT и GROUP BY използват различни планове за изпълнение, така че понякога е по-добре да замените единия с другия:
SELECT mo.grouper,
(
SELECT SUM(col)
FROM mytable mi
WHERE mi.grouper = mo.grouper
)
FROM (
SELECT DISTINCT grouper
FROM mytable
) mo
vs.
SELECT mo.grouper, SUM(col)
FROM mytable
GROUP BY
mo.grouper
В PostgreSQL, DISTINCT сортира и GROUP BY хешове.
MySQL липсва FULL OUTER JOIN, така че може да се пренапише като следното:
SELECT t1.col1, t2.col2
FROM table1 t1
LEFT OUTER JOIN
table2 t2
ON t1.id = t2.id
vs.
SELECT t1.col1, t2.col2
FROM table1 t1
LEFT JOIN
table2 t2
ON t1.id = t2.id
UNION ALL
SELECT NULL, t2.col2
FROM table1 t1
RIGHT JOIN
table2 t2
ON t1.id = t2.id
WHERE t1.id IS NULL
, но вижте тази статия в моя блог за това как да направите това по-ефективно в MySQL:
Тази йерархична заявка в Oracle:
SELECT DISTINCT(animal_id) AS animal_id
FROM animal
START WITH
animal_id = :id
CONNECT BY
PRIOR animal_id IN (father, mother)
ORDER BY
animal_id
може да се трансформира в това:
SELECT DISTINCT(animal_id) AS animal_id
FROM (
SELECT 0 AS gender, animal_id, father AS parent
FROM animal
UNION ALL
SELECT 1, animal_id, mother
FROM animal
)
START WITH
animal_id = :id
CONNECT BY
parent = PRIOR animal_id
ORDER BY
animal_id
, като последният е по-ефективен.
Вижте тази статия в моя блог за подробности относно плана за изпълнение:
За да намерите всички диапазони, които се припокриват с дадения диапазон, можете да използвате следната заявка:
SELECT *
FROM ranges
WHERE end_date >= @start
AND start_date <= @end
, но в SQL Server тази по-сложна заявка дава същите резултати по-бързо:
SELECT *
FROM ranges
WHERE (start_date > @start AND start_date <= @end)
OR (@start BETWEEN start_date AND end_date)
, и вярвате или не, имам статия в блога си и за това:
SQL Server също няма ефективен начин за извършване на кумулативни агрегати, така че тази заявка:
SELECT mi.id, SUM(mo.value) AS running_sum
FROM mytable mi
JOIN mytable mo
ON mo.id <= mi.id
GROUP BY
mi.id
могат да бъдат по-ефективно пренаписани с помощта на, Господи, помогни ми, курсори (правилно ме чухте: cursors, more efficiently и SQL Server в едно изречение).
Вижте тази статия в моя блог за това как да го направите:
Има определен вид заявка, често срещана във финансовите приложения, която търси ефективния курс за валута, като тази в Oracle:
SELECT TO_CHAR(SUM(xac_amount * rte_rate), 'FM999G999G999G999G999G999D999999')
FROM t_transaction x
JOIN t_rate r
ON (rte_currency, rte_date) IN
(
SELECT xac_currency, MAX(rte_date)
FROM t_rate
WHERE rte_currency = xac_currency
AND rte_date <= xac_date
)
Тази заявка може да бъде силно пренаписана, за да използва условие за равенство, което позволява HASH JOIN вместо NESTED LOOPS:
WITH v_rate AS
(
SELECT cur_id AS eff_currency, dte_date AS eff_date, rte_rate AS eff_rate
FROM (
SELECT cur_id, dte_date,
(
SELECT MAX(rte_date)
FROM t_rate ri
WHERE rte_currency = cur_id
AND rte_date <= dte_date
) AS rte_effdate
FROM (
SELECT (
SELECT MAX(rte_date)
FROM t_rate
) - level + 1 AS dte_date
FROM dual
CONNECT BY
level <=
(
SELECT MAX(rte_date) - MIN(rte_date)
FROM t_rate
)
) v_date,
(
SELECT 1 AS cur_id
FROM dual
UNION ALL
SELECT 2 AS cur_id
FROM dual
) v_currency
) v_eff
LEFT JOIN
t_rate
ON rte_currency = cur_id
AND rte_date = rte_effdate
)
SELECT TO_CHAR(SUM(xac_amount * eff_rate), 'FM999G999G999G999G999G999D999999')
FROM (
SELECT xac_currency, TRUNC(xac_date) AS xac_date, SUM(xac_amount) AS xac_amount, COUNT(*) AS cnt
FROM t_transaction x
GROUP BY
xac_currency, TRUNC(xac_date)
)
JOIN v_rate
ON eff_currency = xac_currency
AND eff_date = xac_date
Въпреки че е адски обемиста, последната заявка е 6 пъти по-бърза.
Основната идея тук е замяната на <= с =, което изисква изграждане на календарна таблица в паметта. до JOIN с.
person
Quassnoi
schedule
01.07.2009
