Tunning help!!

From: NJN <ibm_97_at_yahoo.com>
Date: 22 Dec 1999 23:26:18 EST
Message-ID: <3861A42D.13EE2680@yahoo.com>

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; Received on Wed Dec 22 1999 - 22:26:18 CST