Re: SQL statement help

Hi

There are a couple of structural problems in the query

This query does a cartesian join of these tables sponsor, coupondetail and campaign

These tables are not linked to the other tables in the query That might be one of the reasons why this query is taking so long. See the trace on the full table scans f the query.

HTH
Arvind

select couponorder.cporid
from

  HBFADMIN.COUPONORDERDETAIL
, HBFADMIN.SPONSOR
, HBFADMIN.SITE
, HBFADMIN.STATE
, HBFADMIN.COUNTY
, HBFADMIN.CAMPAIGN
, HBFADMIN.COUPONORDER

where couponorder.cpordate BETWEEN '16-SEP-99' AND '17-SEP-99' AND county.county_id = couponorder.cporcountyid AND state.state_id = county.state_id
AND couponorder.cporstateid = state.id
AND site.siteid = couponorder.cporsiteid AND couponorderdetail.cpodcampid = campaign.campid AND couponorderdetail.cpodsponid = sponsor.sponid ORDER BY cporid;

Ed Lufker wrote in message <7sgm49$5mk$1_at_sloth.swcp.com>...
>Hi All:
>
>Could someone please tell me how I could write the following statement in
>a more effient manner. The statment is running too too long. All columns
>are indexed.
>
>select couponorder.cporid from HBFADMIN.COUPONORDER
> , HBFADMIN.COUPONORDERDETAIL
> , HBFADMIN.SPONSOR
> , HBFADMIN.SITE
> , HBFADMIN.STATE
> , HBFADMIN.COUNTY
> , HBFADMIN.CAMPAIGN
> where couponorder.cpordate BETWEEN '16-SEP-99' AND '17-SEP-99'
> AND couponorder.cporstateid = state.id
> AND couponorder.cporsiteid = site.siteid
> AND state.state_id = county.state_id
> AND couponorder.cporcountyid = county.county_id
> AND couponorderdetail.cpodsponid = sponsor.sponid
> AND couponorderdetail.cpodcampid = campaign.campid
> ORDER BY cporid;
>
>Thanks in advance for any help here.
>Eddie Lufker
Received on Sun Sep 26 1999 - 08:20:49 CDT