query tuning

I am trying to tune a query containing a 3 table join and am having some difficulty. Some background on the query and tables. Database version 7.3.2.3. Table 1 has approximately 30,000 rows, table 2 has 4,000,000 rows, table 3 has 7,000,000 rows (this is the order in the query). The two large tables are actually partition views, consisting of approx. 14 partitions each. We are also utilizing parallel query as well as hash join features. The appropriate init.ora parameters are set for each of these features. With no hints, the explain plan shows that a hash join is first being performed on the 7,000,000 row table and a second hash join between the 30,000 row table and the 4,000,000 row table. It seems to me that the better plan would be to hash the 30,000 row and 4,000,000 row tables and then hash that result with the 7,000,000 row table. I have added various use_hash hints and cannot seem to get this resulting plan. Using nested loops join takes approximately 2 minutes - using the above hash join takes over 8 minutes.

I've seen lots of examples of the use_hash hint on 2 table joins, but have not seen any with more than 2 tables. Does anyone have any experience with this? I'm not real clear on which tables get included in the use_hash hint. Any assistance would be greatly appreciated. Thanks in advance. Received on Fri Sep 26 1997 - 00:00:00 CDT