Re: Forcing the use of particular execution plans

Tim Truman wrote:
> Query:
> SELECT count(*) as count FROM
> (
>     SELECT *
>         FROM transaction t, merchant m
>         WHERE t.merchant_id = m.id
>             AND m.id = 198
>             AND t.transaction_date >= '20050101'
>             AND t.transaction_date <= '20060925'
>             AND credit_card_no LIKE '1111%111'
>
>     UNION ALL
>     SELECT *
>         FROM transaction t, merchant m
>         WHERE t.merchant_id = m.id
>             AND m.parent_merchant_id = 198
>             AND t.transaction_date >= '20050101'
>             AND t.transaction_date <= '20060925'
>             AND credit_card_no LIKE '1111%111'
> ) AS foobar
>

Actually, I think the best course of action is to rewrite the query to a
faster alternative. What you can try is:
SELECT SUM(count) AS count FROM
(
    SELECT count(*) AS count
        FROM transaction t, merchant m
        WHERE t.merchant_id = m.id
            AND m.id = 198
            AND t.transaction_date >= '20050101'
            AND t.transaction_date <= '20060925'
            AND credit_card_no LIKE '1111%111'

    UNION ALL
    SELECT count(*) AS count
        FROM transaction t, merchant m
        WHERE t.merchant_id = m.id
            AND m.parent_merchant_id = 198
            AND t.transaction_date >= '20050101'
            AND t.transaction_date <= '20060925'
            AND credit_card_no LIKE '1111%111'
) AS foobar;


The next optimization is to merge the 2 subqueries into one. If you
schema is such that m.id can not be the same as m.parent_merchant_id I
think your query can be reduced to:
SELECT count(*) AS count
    FROM transaction t, merchant m
    WHERE t.merchant_id = m.id
        AND
        (
            m.id = 198
            OR
            m.parent_merchant_id = 198
        )
        AND t.transaction_date >= '20050101'
        AND t.transaction_date <= '20060925'
        AND credit_card_no LIKE '1111%111'


If m.id can be the same as m.parent_merchant_id you need something like:
SELECT SUM(
    CASE WHEN m.id = m.parent_merchant_id THEN 2 ELSE 1 END
    ) AS count
    FROM transaction t, merchant m
    WHERE t.merchant_id = m.id
        AND
        (
            m.id = 198
            OR
            m.parent_merchant_id = 198
        )
        AND t.transaction_date >= '20050101'
        AND t.transaction_date <= '20060925'
        AND credit_card_no LIKE '1111%111'

Jochem