what if if you use sub-query..are there any indexes on lowest_amount and highest_amount..thanks...subodh On 21 October 2011 11:11, amonte <ax.mount@xxxxxxxxx> wrote: > Hello everyone > I am running a query which looks like following (running in 10.2.0.4): > > select a.account_number, a.id, b.* > from account a, tmp_account b > where b.amount between a.lowest_amount and a.highest_amount > > account has 8000000 rows and tmp_account 150000, the execution plan shows > merge sort join as expected, this query takes around 5 hours to run, is > there anyway to improve this? > > > Alex > > > -- > //www.freelists.org/webpage/oracle-l > > > -- ============================================= TRUTH WINS AT LAST, DO NOT FORGET TO SMILE TODAY ============================================= -- //www.freelists.org/webpage/oracle-l