Oracle Performance

I'm having a problem with a 7.2.3 server. It is running on an NCR system with 8 P133s and 3GB of physical memory (sorry, I don't know the model number) running AT&T UNIX.

I have 2 tables that form a master detail relationship, the general structure of the tables are as follows:

table T1
(

 fielda char(5) not null,
 fieldb date not null,
 fieldc number not null,
...
) (row with is around 250 bytes)
table T2
(

 fielda char(5) not null,
 fieldb date not null,
 fieldc number not null,
 fieldd char(1) not null,
...
) (row with is around 150 bytes)

The sizes of these tables are 2.5 million rows in T1, and 6 million rows in T2.

These 2 tables are joined with the following where and order by clauses

	where T1.fielda = T2.fielda
		and T1.fieldb = T2.fieldb
		and T1.fieldc = T2.fieldc
		and T1.fieldb >= '[a start date]'
		and T1.fieldb <= '[an end date]'
	order by T2.fielda, T2.fieldb, T2.fieldc, T2.fieldd

The result set returns 20 minutes later, with the sar report during the period reporting 0% idle time.

I have a requirement that the result set be built < 5 minutes. Is this possible based upon anyone's experience? I have been told that the requirement is not possible.

On a side note, selecting just the rows from T1 (no join) takes 3 minutes. This would imply that I may be able to perform the join on the client faster than the join being done by the RDBMS?!?

Any help or insight would be greatly appreciated.

Thanks in advance.
Bill Received on Fri Aug 30 1996 - 00:00:00 CEST