Re: Nasty Query Here

On Apr 9, 7:30 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> 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.

I'll have to look more at it. I think it is where the info is coming from which why there are so many tables......

I was hoping to make it smaller too, it is just so long..... Received on Wed Apr 16 2008 - 10:30:55 CDT