SQL deyimlerini basitleştirmek için genel kurallar

SORU

1 Temmuz 2009, ÇARŞAMBA

SQL deyimlerini basitleştirmek için genel kurallar

"Çıkarım kuralları veya boyutu karmaşıklığı. bir SQL sorgu azaltmak için kullanabilirim" (set çalışma kuralları veya mantık kurallarına benzer) bazı arıyorum Böyle bir şey var mı? Herhangi bir evrak, herhangi bir alet? Kendi bulduğunuz herhangi bir equivalencies? Performans açısından sorgu optimizasyonu için, ama bir şekilde benzer.

Devlet farklı Olması, bir (karmaşık) Sorgu ile Katılır, SUBSELECTs, Sendikalar olabilir (ya da değil) azaltmak için bir daha basit, eşdeğer SQL deyimi, üreten aynı sonuç, kullanarak bazı dönüşüm kuralları?

Yani, en SUBSELECTs bir BİRLEŞİM olarak yazılmış olması gibi SQL deyimleri eşdeğer dönüşümleri arıyorum.

CEVAP

1 Temmuz 2009, ÇARŞAMBA

Devlet farklı Olması, bir (karmaşık) Sorgu ile Katılır, SUBSELECTs, Sendikalar olabilir (ya da değil) azaltmak için bir daha basit, eşdeğer SQL deyimi, üreten aynı sonuç, kullanarak bazı dönüşüm kuralları?

Bu tam olarak optimize bir yaşam için (hep bunu söylüyorum o kadar da değil).

SQL kümesi tabanlı bir dil olduğu için, genellikle başka bir sorgu dönüştürmek için birden fazla yolu vardır.

Bu sorgu gibi:

SELECT  *
FROM    mytable
WHERE   col1 > @value1 OR col2 < @value2

bu dönüştürülebilir:

SELECT  *
FROM    mytable
WHERE   col1 > @value1
UNION
SELECT  *
FROM    mytable
WHERE   col2 < @value2

ya da bu:

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

çirkin ama iyi yürütme planları verim alınabilir.,

En yaygın şeylerden biri bu sorgu yerine

SELECT  *
FROM    mytable
WHERE   col IN
        (
        SELECT  othercol
        FROM    othertable
        )

bu bir:

SELECT  *
FROM    mytable mo
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    othertable o
        WHERE   o.othercol = mo.col
        )

RDBMSbazı'nin (PostgreSQL gibi), DISTINCT GROUP BY farklı yürütme planları kullanmak, bazen daha iyi birini diğeri ile değiştirmek için:

SELECT  mo.grouper,
        (
        SELECT  SUM(col)
        FROM    mytable mi
        WHERE   mi.grouper = mo.grouper
        )
FROM    (
        SELECT  DISTINCT grouper
        FROM    mytable
        ) mo

SELECT  mo.grouper, SUM(col)
FROM    mytable
GROUP BY
        mo.grouper

, ** 28 ** 29 GROUP BY türlü sağlamalarının.

MySQL olarak folloing yazılmış olabilir FULL OUTER JOIN yoktur:

SELECT  t1.col1, t2.col2
FROM    table1 t1
LEFT OUTER JOIN
        table2 t2
ON      t1.id = t2.id

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

ama bu daha verimli MySQL bunu yapmak için nasıl blogumda bu makaleye bakın:

Emulating FULL OUTER JOIN in MySQL

Oracle bu hiyerarşik sorgu:

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

bu dönüştürülmüş olabilir:

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

ikinci bir daha fazla ölçülebilir.

Yürütme planı için blogumda bu makale ayrıntıları

Genealogy query on both parents

Verilen aralığın üst üste tüm aralıkları bulmak için aşağıdaki sorguyu kullanabilirsiniz:

SELECT  *
FROM    ranges
WHERE   end_date >= @start
        AND start_date <= @end

ama SQL Server daha karmaşık bu sorgu verimleri aynı sonuçları daha hızlı

SELECT  *
FROM    ranges
WHERE   (start_date > @start AND start_date <= @end)
        OR (@start BETWEEN start_date AND end_date)

ve inanın bu çok bloguma bir makale var ya da yok,:

Overlapping ranges: SQL Server

SQL Server ayrıca toplu toplamları yapmak için etkili bir yöntem, bu sorgu çok yoksun:

SELECT  mi.id, SUM(mo.value) AS running_sum
FROM    mytable mi
JOIN    mytable mo
ON      mo.id <= mi.id
GROUP BY
        mi.id

daha verimli bir şekilde yeniden kullanarak, Tanrım bana yardım et, (beni doğru duydun: cursors, more efficiently ve bir cümle ile SQL Server) imleçler.

Bunu yapmak için nasıl blogumda bu makaleye bakın:

Flattening timespans: SQL Server

Sorgu, belirli bir tür yaygın Oracle böyle bir para birimi için etkili oranı, arar finansal uygulamalarda bir araya geldi

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
        )

Bu sorgu, ağır NESTED LOOPS yerine: HASH JOIN sağlayan bir eşitlik durumu kullanmak için yazılmış

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