一番遅いSQLは？

クリックで表示されたSQL文の全容は：

SELECT
    s_name,
    COUNT (*) AS numwait
FROM
    supplier,
    lineitem l1,
    orders,
    nation
WHERE
    s_suppkey = l1.l_suppkey AND
    o_orderkey = l1.l_orderkey AND
    o_orderstatus = 'F' AND
    l1.l_receiptdate > l1.l_commitdate AND
    EXISTS (SELECT *
            FROM lineitem l2
            WHERE
                l2.l_orderkey = l1.l_orderkey AND
                l2.l_suppkey <> l1.l_suppkey) AND
    NOT EXISTS (SELECT *
                FROM lineitem l3
                WHERE
                    l3.l_orderkey = l1.l_orderkey AND
                    l3.l_suppkey <> l1.l_suppkey AND
                    l3.l_receiptdate > l3.l_commitdate) AND
    s_nationkey = n_nationkey AND
    n_name = 'CHINA'
GROUP BY s_name
ORDER BY
    numwait DESC,
    s_name;

Embarcadero® DB Optimizer™ XEではHint句をシュミレートしてくれる。
赤い棒グラフのHintは「使うとえらい目に合う」ことを示している。これでHintのテストがとても楽になる。

そして改善点を図解してくれる：

改善の可能性のあるHintをクリックするとHint句付のSQL文が表示される：

SELECT /*+ FULL ( l1 ) FULL ( orders ) */    s_name,
    COUNT (*) AS numwait
FROM
    supplier,
    lineitem l1,
    orders,
    nation
WHERE
    s_suppkey = l1.l_suppkey AND
    o_orderkey = l1.l_orderkey AND
    o_orderstatus = 'F' AND
    l1.l_receiptdate > l1.l_commitdate AND
    EXISTS (SELECT /*+ NO_UNNEST */
                *
            FROM lineitem l2
            WHERE
                l2.l_orderkey = l1.l_orderkey AND
                l2.l_suppkey <> l1.l_suppkey) AND
    NOT EXISTS (SELECT /*+ NO_UNNEST */
                    *
                FROM lineitem l3
                WHERE
                    l3.l_orderkey = l1.l_orderkey AND
                    l3.l_suppkey <> l1.l_suppkey AND
                    l3.l_receiptdate > l3.l_commitdate) AND
    s_nationkey = n_nationkey AND
    n_name = 'CHINA'
GROUP BY s_name
ORDER BY
    numwait DESC,
    s_name;

お勧めHintと共に実行時間などが一覧できる機能は非常に貴重だ。