Is it possible to replace NL join with HS join in

Question

Hi am having a very big query comprises of two main table and 15 sub tables.

ie, Main table say table_a join with some other 10 tables.
and another main table sat table_b join with some other 5 tables.

Table_a contains - 314988 rows and 
Table_b contains - 710989 rows.

when we join i could find many NL Joins and HS joins.

intresting fact that all HSjoin cost very less and NL join cost very very high.

So is it possible to convert the joins from NL to HS Join.

Note : its all indexed.. there is no TBSCAN everything is IXSCAN.

Answer 1

You can create an optimization profile, where you will indicate to the optimizer which methods it should use to access the data for the given query.

Take a look at this tutorial from DeveloperWorks: http://www.ibm.com/developerworks/data/library/techarticle/dm-1202storedprocedure/index.html