On Apr 9, 1:51=A0pm, Mtek <m...@[EMAIL PROTECTED]
> wrote:
> Hi,
>
> I have a REAL nasty query here which I'm hoping to make smaller by
> using some joins. =A0I 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 =3D ca.customer_id(+)
AND c.customer_id =3D s.customer_id
AND c.customer_id =3D v.customer_id
AND c.customer_id =3D cd.customer_id(+)
AND s.email_product_id =3D '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 =3D 1
AND s.status =3D 1
AND s.sell_rep_id IN (201, 202)
AND p.produst_id =3D 1
AND TRUNC(start_date) BETWEEN p_start_date AND p_end_date
AND p.produst_id =3D s.produst_id
AND c.customer_id =3D s.customer_id
AND c.customer_id =3D ca.customer_id
AND c.customer_id =3D ct.customer_id
AND c.customer_id =3D 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 =3D ca.customer_id(+)
AND c.customer_id =3D s.customer_id
AND c.customer_id =3D v.customer_id
AND c.customer_id =3D cd.customer_id(+)
AND s.email_product_id =3D '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 =3D 1
AND s.status =3D 1
AND s.sell_rep_id IN (201, 202)
AND p.produst_id =3D 1
AND TRUNC(start_date) BETWEEN p_start_date AND p_end_date
AND p.produst_id =3D s.produst_id
AND c.customer_id =3D s.customer_id
AND c.customer_id =3D ca.customer_id
AND c.customer_id =3D ct.customer_id
AND c.customer_id =3D 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.
|
