| Union takes more time please help [message #135405] |
Wed, 31 August 2005 08:01  |
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  |
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 #135618 is a reply to message #135405] |
Thu, 01 September 2005 09:48 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
