tunning hellp!!

From: <ibm_97_at_yahoo.com>
Date: Thu, 23 Dec 1999 00:56:45 GMT
Message-ID: <83rs0b$gtl$1@nnrp1.deja.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;

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Dec 22 1999 - 18:56:45 CST