Prev: Anyone good with Joins?
Next: Problem with silent install of 10.2.0
From: Mtek on 9 Apr 2008 13:51

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,

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 (+));
From: Charles Hooper on 9 Apr 2008 20:30
On Apr 9, 1:51 pm, Mtek <m...(a)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.
From: patrick on 11 Apr 2008 13:46

Very confusing
query has customer_account ca
subquery has customer_address ca
while ca is now customer_account

May want to look at rewriting the subquery as exists/not exists
something like
and not exists (select 'x' from customer c2,
customer.subscriptions s2,
customer.product p2,
customer_address ca2,
customer_account ct2,
visitor v2
where c2.customer_id = c.customer_id
and c2.email = c.email
and ca2.customer_id = c2.customer_id
and ca2.address_type_id = 1
and s2.customer_id = c2.customer_id
and s2.status = 1
and s2.sell_rep_id IN (201, 202)
and p2.produst_id = s2.produst_id
and p2.produst_id = 1
and TRUNC(start_date) BETWEEN p_start_date AND
p_end_date
and ct2.customer_id = c2.customer_id
and v.customer_id (+) = c2.customer_id)

Also are customer_account ct2 and visitor v2 really
needed in the subquery.

====>Pat



From: Mtek on 16 Apr 2008 11:30
On Apr 9, 7:30 pm, Charles Hooper <hooperc2...(a)yahoo.com> wrote:
> On Apr 9, 1:51 pm, Mtek <m...(a)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.....
 | 
Pages: 1
Prev: Anyone good with Joins?
Next: Problem with silent install of 10.2.0