Re: Nasty Query Here

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Wed, 9 Apr 2008 17:30:35 -0700 (PDT)
Message-ID: <07998e36-eb46-4549-a629-bbc4a1255980@1g2000prf.googlegroups.com>

On Apr 9, 1:51 pm, Mtek <m..._at_mtekusa.com> wrote:
> Hi,
>
> I have a REAL nasty query here which I'm hoping to make smaller by
> using some joins.  I got help from here before, so while I'm trying to
> do it, I was hoping some others can look at it also.
>
> Thank you,
>

Are you wanting the query to be smaller, or execute faster?

Slightly reformatted:
SELECT
  c.email || '|' || ca.first_name || '|' || ca.last_name || '|' || 'ZACKS' || '|' || TO_CHAR(c.date_registered, 'mmddrr hh24:mi:ss') || '|' ||

    TO_CHAR(TRUNC(SYSDATE-1),'MM-DD-YYYY') || '|' || v.adid || '|' || cd.day_phone line,
  'HOUSE' list_type
FROM
  customer c,
  customer_account ca,
  visitor v,
  subscr_email_product s,
  customer_address cd
WHERE
  c.date_registered BETWEEN p_start_date AND p_end_date

  AND c.customer_id = ca.customer_id(+)
  AND c.customer_id = s.customer_id
  AND c.customer_id = v.customer_id
  AND c.customer_id = cd.customer_id(+)
  AND s.email_product_id = 'HL'

  AND email NOT IN (
    SELECT
      email
    FROM

      customer c,
      customer.subscriptions s,
      customer.product p,
      customer_address ca,
      customer_account ct,
      visitor v
    WHERE
      ca.address_type_id = 1
      AND s.status = 1
      AND s.sell_rep_id IN (201, 202)
      AND p.produst_id = 1
      AND TRUNC(start_date) BETWEEN p_start_date AND p_end_date
      AND p.produst_id  = s.produst_id
      AND c.customer_id = s.customer_id
      AND c.customer_id = ca.customer_id
      AND c.customer_id = ct.customer_id
      AND c.customer_id = v.customer_id (+))

UNION
SELECT
  c.email || '|' || ca.first_name || '|' || ca.last_name || '|' || 'ZACKS' || '|' || TO_CHAR(c.date_registered, 'mmddrr hh24:mi:ss') || '|' ||

    TO_CHAR(TRUNC(SYSDATE-1),'MM-DD-YYYY') || '|' || v.adid || '|' || cd.day_phone line,
  'AMERITRADE' list_type
FROM
  customer c,
  customer_account ca,
  visitor v,
  subscr_email_product s,
  customer_address cd
WHERE
  c.date_registered BETWEEN p_start_date AND p_end_date

  AND c.customer_id = ca.customer_id(+)
  AND c.customer_id = s.customer_id
  AND c.customer_id = v.customer_id
  AND c.customer_id = cd.customer_id(+)
  AND s.email_product_id = 'HL'

  AND email IN (
    SELECT
      email
    FROM

      customer c,
      customer.subscriptions s,
      customer.product p,
      customer_address ca,
      customer_account ct,
      visitor v
    WHERE
      ca.address_type_id = 1
      AND s.status = 1
      AND s.sell_rep_id IN (201, 202)
      AND p.produst_id = 1
      AND TRUNC(start_date) BETWEEN p_start_date AND p_end_date
      AND p.produst_id  = s.produst_id
      AND c.customer_id = s.customer_id
      AND c.customer_id = ca.customer_id
      AND c.customer_id = ct.customer_id
      AND c.customer_id = v.customer_id (+));

• You are using a UNION between the two halves of the SQL statement, when there is no chance of duplicates between the two halves - use UNION ALL instead if possible.
• You are using TRUNC(start_date) in the subquery, if there is an index on this column, it will not be used, unless it is a function based index on TRUNC(start_date). If there is a time component to the start_date column, consider using the following: AND start_date BETWEEN p_start_date AND (p_end_date + 0.999)
• The two halves of the SQL statement are nearly the same except that one half is looking for the email address to be in the subquery, and the other with the email address not in the same subquery. Consider converting the subquery into an inline view (will need to add DISTINCT) and outer joining it to the main query, and use either NVL2 or DECODE to return either 'AMERITRADE' or 'HOUSE' depending on if the email returned from the inline view is null or not.
• You are selecting from a large number of tables - do you need to select from all of those tables, and are all restrictions specified to minimize the number of rows returned?
• You are not specifying from which table all columns will be retrieved - you may want to fix that.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Wed Apr 09 2008 - 19:30:35 CDT