Re: SQL query time out in ORACLE db

gilroy.ponniah_at_gmail.com wrote:
> Hi,
> I have the following join query running on SQL and ORACLE identical
> database/tables with the same indexes. However, in SQL this query runs
> in 10 seconds and in ORACLE it gets timed out after three minutes. Any
> idea in making it work in ORACLE is much appreciated.
>
> SQL
> select g3.[value] as LOCATION, g1.[value] as TASKID, sum(g0.transamt)
> as TRANSAMT, sum(cast(g2.[value] as int)) as QTY
> from glpost g0
> join glposto g1 on g0.postingseq=g1.postingseq and
> g0.cntdetail=g1.cntdetail and g1.optfield='TASKID' and g1.[value] in
> ('000000','000001')
> join glposto g2 on g1.postingseq=g2.postingseq and
> g1.cntdetail=g2.cntdetail and g2.optfield='TXNQTY'
> join glposto g3 on g1.postingseq=g3.postingseq and
> g1.cntdetail=g3.cntdetail and g3.optfield='TEAM' and g3.[value] in
> ('301','302','303')
> Where g0.fiscalyr >= 2005 and g0.fiscalyr <= 2005 and g0.fiscalperd >=
> 6 and g0.fiscalperd <= 6
> group by g3.[value], g1.[value]
>
> ORACLE
> select g3.value as LOCATION, g1.value as TASKID, sum(g0.transamt) as
> TRANSAMT, sum(cast(g2.value as int)) as QTY
> from glpost g0
> join glposto g1 on g0.postingseq=g1.postingseq and
> g0.cntdetail=g1.cntdetail and g1.optfield='TASKID' and g1.value in
> ('000000','000001')
> join glposto g2 on g1.postingseq=g2.postingseq and
> g1.cntdetail=g2.cntdetail and g2.optfield='TXNQTY'
> join glposto g3 on g1.postingseq=g3.postingseq and
> g1.cntdetail=g3.cntdetail and g3.optfield='TEAM' and g3.value in
> ('301','302','303')
> Where g0.fiscalyr >= 2005 and g0.fiscalyr <= 2005 and g0.fiscalperd >=
> 6 and g0.fiscalperd <= 6
> group by g3.value, g1.value
>
>
> Thank you.
> Best Rgds,
> Gilroy.

Interesting how you knew VALUE is a reserved word in SQL Server but failed to recognise that with Oracle. This modified query:

 select g3."VALUE" as LOCATION, g1."VALUE" as TASKID, sum(g0.transamt) as
 TRANSAMT, sum(cast(g2."VALUE" as int)) as QTY  from glpost g0
 join glposto g1 on g0.postingseq=g1.postingseq and  g0.cntdetail=g1.cntdetail and g1.optfield='TASKID' and g1."VALUE" in  ('000000','000001')
 join glposto g2 on g1.postingseq=g2.postingseq and  g1.cntdetail=g2.cntdetail and g2.optfield='TXNQTY'  join glposto g3 on g1.postingseq=g3.postingseq and  g1.cntdetail=g3.cntdetail and g3.optfield='TEAM' and g3."VALUE" in  ('301','302','303')
 Where g0.fiscalyr >= 2005 and g0.fiscalyr <= 2005 and g0.fiscalperd
>=

 6 and g0.fiscalperd <= 6
 group by g3."VALUE", g1."VALUE";

should work.

David Fitzjarrell Received on Fri Sep 23 2005 - 10:33:30 CDT