Re: db-link 7.3.4 to 8.0.5

From: Terry Dykstra <tdykstra_at_cfol.ab.ca>
Date: 2000/03/10
Message-ID: <Cj9y4.373$QY4.29951@news0.telusplanet.net>#1/1

Yes, I use hints. Here is the whole query. The final result is 2 rows.

select /*+ RULE */ atgas.contract_type, atgas.contract_id, ipl.uwi, 'Unknown',
atgas.working_interest, ipl.form_name
from
contract_lands atgas,
(select /*+ RULE */ well.uwi, formation.form_name, dls_range, meridian, meridian_dir, dls_township, dls_section FROM
   IPL_CODE_at_PETRODATA2 IPL_CODE,
   FORMATION_at_PETRODATA2 FORMATION,
   WELL_at_PETRODATA2 WELL,
   WELL_PROD_ZONE_at_PETRODATA2 WELL_PROD_ZONE,    NODE_at_PETRODATA2 NODE,
   NODE_DLS_NTS_at_PETRODATA2 DLS
WHERE WELL_PROD_ZONE.SOURCE(+) = 'IPL'
AND WELL.SOURCE = 'IPL'
AND FORMATION.SOURCE(+) = 'IPL'
AND IPL_CODE.CODE_TYPE(+) = 'STATUS'
AND DLS.SOURCE = 'IPL'
AND DLS.LOC_QUAL = 'A'
AND NODE.SOURCE = 'IPL'
AND NODE.LOC_QUAL = 'A'
AND WELL_PROD_ZONE.UWI(+)=WELL.UWI
AND WELL_PROD_ZONE.SOURCE(+)=WELL.SOURCE AND FORMATION.SOURCE(+)=WELL_PROD_ZONE.SOURCE AND FORMATION.FORM_ID(+)=WELL_PROD_ZONE.ZONE_ID AND IPL_CODE.SOURCE(+)=WELL.SOURCE
AND IPL_CODE.CODE(+)=WELL.IPL_CRSTATUS
AND DLS.NODE_ID=NODE.NODE_ID
AND DLS.SOURCE=NODE.SOURCE
AND NODE.SOURCE=WELL.SOURCE
AND NODE.NODE_ID=WELL.BASE_NODE_ID
) ipl
where ipl.dls_range = atgas.rge

and ipl.meridian = atgas.mer and ipl.meridian_dir='W'
and ipl.dls_township = atgas.twp
and ipl.dls_section = atgas.sec

and (atgas.contract_type, atgas.contract_id, ipl.uwi) not in (select contract_type, contract_id, uwi from contract_wells) and ipl.meridian_dir = 'W' and ipl.meridian = 5 and ipl.dls_range=25 and ipl.dls_section=13 and ipl.dls_township=92 and atgas.mer = 5 and atgas.rge =25 and atgas.sec =13 and atgas.twp=92 /

--
Terry Dykstra (TeamSybase)
Canadian Forest Oil Ltd.
Check out Sybase Developer's Network: http://www.sybase.com/sdn

Brian Peasland <peasland_at_edcmail.cr.usgs.gov> wrote in message
news:38C91009.2A644F65_at_edcmail.cr.usgs.gov...


> Terry,

>

>    I'm not sure why your database is hanging, but I have seen problems

> with dblinks between Oracle 7 & 8. Query hints have problems in this

> environment. Are you using any hints? Maybe you should include a copy of

> your query. That might help diagnose the problem.

>

> HTH,

> Brian

>

> Terry Dykstra wrote:

> >

> > I run a query from a 7.3.4 database using db-links to a 8.0.5 database

 and


> > it just hangs my 734 database.  When I copy the 734 tables to the 805

> > database and run the same query without dblinks, it returns the answer

 in


> > less than 1 second.  Anybody have any insights into this?

> >

> > --

> > Terry Dykstra

> > Canadian Forest Oil Ltd.

> > Check out Sybase Developer's Network: http://www.sybase.com/sdn

>

> --

> ========================================

> Brian Peasland

> Raytheons Systems at

>   USGS EROS Data Center

> These opinions are my own and do not

> necessarily reflect the opinions of my

> company!

> ========================================

Received on Fri Mar 10 2000 - 00:00:00 CST