Home » RDBMS Server » Performance Tuning » Union takes more time please help
Union takes more time please help [message #135405] Wed, 31 August 2005 08:01 Go to next message
misha603
Messages: 20
Registered: July 2005
Location: India
Junior Member

Hi,
I have the following query to be executed
select /*+ FULL(TMPSIT) */ TMPSIT.cosit, SYSDATE
from (select /*+ PARALLEL(TV) PARALLEL(V) PARALLEL(S) */ S.cosit
from thisveh TV, rveh V, rsit S
where V.nuveh = TV.nuveh
and (S.COSIT = V.COSITORI
or S.COSIT = V.COSITDST)
and S.cocatsit = '30'
union
select S.cosit
from thisveh TV, rvehord O, rsit S
where O.nuveh = TV.nuveh
and S.COSIT = O.COSITDST
and S.cocatsit = '30'
union
select /*+ PARALLEL(TV) PARALLEL(L) PARALLEL(S) */ S.cosit
from thislot TV, rlot L, rsit S
where L.nulot = TV.nulot
and (S.COSIT = L.COSITDEP
or S.COSIT = L.COSITARI)
and S.cocatsit = '30'
union
select /*+ PARALLEL(TV) PARALLEL(MS) PARALLEL(S) */ S.cosit
from thismvm TV, rmvmsit MS, rsit S
where MS.numvm = TV.numvm
and S.COSIT = MS.COSIT and S.cocatsit = '30') TMPSIT

If I execute the above query it takes nearly an hour to execute but if I execute each query one by one each query gets executed in less than 5 minutes.
Query1 took 1 minute
Query2 took 4 minutes
Query3 took 2 minutes
Query4 took 3 minutes

Could you please tell me why is it taking so long when I use union?

Thanks
Regards
Santharaj
Re: Union takes more time please help [message #135428 is a reply to message #135405] Wed, 31 August 2005 09:27 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Does your data have duplicates that you need oracle to eliminate for you? If not, then use union all, so that oracle doesn't have to scan through your entire final result looking for duplicates.

And why all the hints, especially the full table scan hint? Is there something specific to your situation causing your statistics to not be of help?

And I guess those tables are intended to have a degree of 0 in most situations, or do you always want to parallel them?
Re: Union takes more time please help [message #135596 is a reply to message #135428] Thu, 01 September 2005 07:07 Go to previous messageGo to next message
misha603
Messages: 20
Registered: July 2005
Location: India
Junior Member

Hi,
Thanks a lot for your response.
I tried giving union all but it gives me duplicate records. So I'm using union.
I'm really new to performance tuning. Could you please tune that query with appropiate hints.
I would really appreciate your kind and timely help.

Thanks in advance

Regards
Raja
Re: Union takes more time please help [message #135618 is a reply to message #135405] Thu, 01 September 2005 09:48 Go to previous message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Gather stats on your table, and remove the hints.

If it needs more, try to remove the duplication of criteria you have in there with your = '30' and the or statement by using an inline view of a subquery factoring clause.

Also, will want to spend time reading the oracle 10g performance tuning guide.
Previous Topic: To increase performance Delete statement
Next Topic: Multiple Tablespaces
Goto Forum:
  


Current Time: Tue Dec 06 22:35:40 CST 2022