Re: Really slow query .. is there a better approach??

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Sun, 16 Sep 2007 18:17:22 -0700
Message-ID: <1189991842.633506.293560@n39g2000hsh.googlegroups.com>

On Sep 15, 9:53 pm, jobs <j..._at_webdos.com> wrote:
> select p.phone,p.account_number,o.amount from users_phones p, orders
> o
> where p.account_number = o.account_number and p.status=1 and p.eci=0
> and o.validation_date > sysdate-15
> and p.btn=1 and o.status=1 and o.id =(select min(oo.id) from orders
> oo
> where oo.ACCOUNT_NUMBER=p.account_number and oo.amount>0 and
> oo.validation_date > sysdate-15)
>
> if I run the code up to the "and o.id" It selects 54 rows really fast.
>
> The orders table should only have about 10 to 20 orders per Id. So why
> dos the addition of that min(oo.id) code significantly slow me down.
>
> Is there a better approach.
>
> Thanks in advance for any help or information!

First, a reformat so that I can read the SQL statement more easily: SELECT

  P.PHONE,
  P.ACCOUNT_NUMBER,
  O.AMOUNT

FROM
  USERS_PHONES P,
  ORDERS O
WHERE
  P.ACCOUNT_NUMBER=O.ACCOUNT_NUMBER

  AND P.STATUS=1
  AND P.ECI=0
  AND O.VALIDATION_DATE > SYSDATE-15
  AND P.BTN=1
  AND O.STATUS=1
  AND O.ID =(
    SELECT
      MIN(OO.ID)
    FROM
      ORDERS OO
    WHERE
      OO.ACCOUNT_NUMBER=P.ACCOUNT_NUMBER
      AND OO.AMOUNT>0
      AND OO.VALIDATION_DATE > SYSDATE-15)

What you will likely find by looking at the explain plan, the ORDERS OO table (in the subquery) will likely be probed once per matching row returned by the join of USERS_PHONES to the ORDERS table. Note that you have included the restriction OO.AMOUNT>0 in the subquery, but you have not included the restriction O.AMOUNT>0 (and possibly OO.ACCOUNT_NUMBER=O.ACCOUNT_NUMBER should have been used). Oracle version will partially determine what transformations are available to Oracle to remove the need to probe the ORDERS OO table once per matching row returned by the join of USERS_PHONES to the ORDERS table. You may be able to help Oracle by rewriting the query into an equivalent form using an inline view, rather than a subquery. The query might look like this after the rewrite: SELECT

  P.PHONE,
  P.ACCOUNT_NUMBER,
  O.AMOUNT

FROM
  USERS_PHONES P,
  ORDERS O,
  (SELECT
    OO.ACCOUNT_NUMBER,
    MIN(OO.ID) ID
  FROM
    ORDERS OO
  WHERE
    AND OO.AMOUNT>0
    AND OO.VALIDATION_DATE > SYSDATE-15
  GROUP BY
    OO.ACCOUNT_NUMBER) OO
WHERE
  P.ACCOUNT_NUMBER=O.ACCOUNT_NUMBER

  AND P.STATUS=1
  AND P.ECI=0
  AND O.VALIDATION_DATE > SYSDATE-15
  AND O.AMOUNT>0
  AND P.BTN=1
  AND O.STATUS=1
  AND O.ID=OO.ID
  AND O.ACCOUNT_NUMBER=OO.ACCOUNT_NUMBER;

You can possibly further reduce the amount of work required by Oracle through the use of analytical functions. For example: SELECT
  PHONE,
  ACCOUNT_NUMBER,
  AMOUNT
FROM
  (SELECT

    P.PHONE,
    P.ACCOUNT_NUMBER,
    O.AMOUNT,
    O.ID,

    MIN(O.ID) OVER (PARTITION BY O.ACCOUNT_NUMBER) MIN_ID   FROM
    USERS_PHONES P,
    ORDERS O,
  WHERE
    P.ACCOUNT_NUMBER=O.ACCOUNT_NUMBER

    AND P.STATUS=1
    AND P.ECI=0
    AND O.VALIDATION_DATE > SYSDATE-15
    AND O.AMOUNT>0
    AND P.BTN=1
    AND O.STATUS=1)

WHERE
  ID=MIN_ID; By using the above method, we are accessing the ORDERS table only once - we use an inline view to present only those rows that have an ID equal to the minimum ID for the ACCOUNT_NUMBER included in the filtered data set.

Please provide explain plans using DBMS_XPLAN for the three SQL statements shown above.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Sun Sep 16 2007 - 20:17:22 CDT