Tuning a Ds job involving Oracle stages

[suneyes]

Hi,
I am dealing with tuning a DS job which mainly involves oracle stages.

job contains two oracle stages whose output is clubbed using a funnel stage,written into a sequential file.

the query in oracle stage-1 will resemble

select c1,c2,c3 from t1,t2,t3

the query in oracle stag-2 will resemble

select count(t4.c1),sum(t4.c2) from
(select c1,c2,c3 from t1,t2,t3
)t4

from the datastage logs each of these satges are taking around 1.2hrs.because of parallel execution the job is getting completed in 1.2hrs.

what I am thinking of is as the part of logic (select c1,c2,c3 from t1,t2,t3) is same in both the satges,I am planning of doing both these stages in a single stage by creating a temp table.

what I want to know is.

1)Is my aproach correct
2)can I create a temp table in datastage(if yes,please tell me,how?).

please help me find a solution.

[DSDexter]

Please give us more information about the queries. Are you trying to create a Cross-Product looking at the queries that you posted that will surely fetch a lot of data, Since it will be a cartesian product. Give us clear requirements than we can help you.

[ArndW]

While the approach might be correct, I am not certain that your measurements are. Did you separate these stages into their own jobs with no other links and check the timing? If not, then your results are likely skewed since timings in jobs usually depend on other stages and often you just end up measuring the slowest stage in the chain but don't really know which one it was.

[suneyes]

Please give us more information about the queries. Are you trying to create a Cross-Product looking at the queries that you posted that will surely fetch a lot of data, Since it will be a cartesian product. Give us clear requirements than we can help you.

no in ora1 stage we get a set of records from a select statement(detail records).
in ora2 stage we will get an aggregated result(trailer record).
so in ora2 stage,we rewrite the same query written in ora1 stage as a subquery and take an aggregated sum.

we will get some 2 million records from ora1 stg and 1 record from ora2 stg.

my job looks similar to this

ora1---
|----funnel---sequentai_file
ora2---[/img]

[suneyes]

Please give us more information about the queries. Are you trying to create a Cross-Product looking at the queries that you posted that will surely fetch a lot of data, Since it will be a cartesian product. Give us clear requirements than we can help you.

no in ora1 stage we get a set of records from a select statement(detail records).
in ora2 stage we will get an aggregated result(trailer record).
so in ora2 stage,we rewrite the same query written in ora1 stage as a subquery and take an aggregated sum.

we will get some 2 million records from ora1 stg and 1 record from ora2 stg.

my job looks similar to this

ora1---
|----funnel---sequentai_file
ora2---

[ray.wurlod]

Please wrap your job design in Code tags then use Preview to get it right so that we can understand which link connects to which stages.