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.
What Oracle version?
( And, by the way, the other database is not SQL it is SqlServer ( that is a database, SQL is a language used by many
database engines).
Received on Fri Sep 23 2005 - 10:26:04 CDT
