Re: tunning hellp!!

Hi
A few thoughts.

If you are running the 'choose' optimiser have you analysed the tables recently?

Do the 'exists' first in the 'from'.

run trace/explain plan (or use toad www.toadsoft.com) to the do the same so you can see whats actually happening.

try a outer join rather than the union.

Get the O'Reilly book on Oracle Performance Tuning - excellant SQL tuning sections.

martin

ibm_97_at_yahoo.com wrote:
>
> Oracle 8i for Solaris.
>
> 'n' and 'a' table are big, each has 1 million rows.
> The other tables are small, the biggest one is 100 rows.
> 'test' and 'test1' only have 2 rows, 5 rows.
>
> I create indexes on those columns:
> n(nfalarmid), n(applicationid), n(instanceid), n(eventid)
> a(eventid)
> m(nfalarmid), m(nfalarmgroupid)
> g(nfalarmgroupid)
>
> Do I have to create index g(description)? I also use 'exists' instead
> of 'IN'. Any performance gain could be get? Thanks a lot!!
>
> select g.description, count(*)
> from nfevent n, nfalarmgroups g, nfalarmgroupmappings m
> where m.nfalarmid=n.nfalarmid
> and n.applicationid = 1
> and n.instanceid = 1
> and m.nfalarmgroupid = g.nfalarmgroupid
> and exists(select 'x' from test t where t.test=g.nfalarmgroupid)
> group by m.nfalarmgroupid, g.description
> union
> select g.description, SUM(a.bytes)
> from nfevent n, pixappdetails a, nfalarmgroups g, nfalarmgroupmappings m
> where n.eventid = a.eventid
> and m.nfalarmid=n.nfalarmid
> and n.applicationid = 1
> and n.instanceid = 1
> and m.nfalarmgroupid = g.nfalarmgroupid
> and exists(select 'x' from test1 t1 where
> t1.test1=g.nfalarmgroupid)
> group by m.nfalarmgroupid, g.description;
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Thu Dec 23 1999 - 02:57:09 CST