A short example with 8000 records in account and 150 records in tmp_account: SQL> SQL> create table account( 2 account_number number not null, 3 id varchar2(10) not null, 4 lowest_amount number(22,4), 5 highest_amount number(22,4) 6 ); Table created. SQL> SQL> create table tmp_account( 2 account_number number not null, 3 amount number(22,4), 4 descr varchar2(40) 5 ); Table created. SQL> SQL> begin 2 for i in 1..8000 loop 3 insert into account 4 (account_number, id, lowest_amount, highest_amount) 5 values 6 (i, 'Ex '||i, mod(i, 337), mod(i, 93)); 7 if i <= 150 then 8 insert into tmp_account 9 (account_number, amount, descr) 10 values 11 (i, mod(i, 43), 'Example record '||i); 12 end if; 13 end loop; 14 15 commit; 16 17 end; 18 / PL/SQL procedure successfully completed. SQL> SQL> set autotrace on SQL> SQL> select a.account_number, a.id, b.* 2 from account a, tmp_account b 3 where b.amount between a.lowest_amount and a.highest_amount; ACCOUNT_NUMBER ID ACCOUNT_NUMBER AMOUNT DESCR -------------- ---------- -------------- ---------- ---------------------------------------- 6782 Ex 6782 42 42 Example record 42 1390 Ex 1390 42 42 Example record 42 7793 Ex 7793 42 42 Example record 42 7119 Ex 7119 42 42 Example record 42 5097 Ex 5097 42 42 Example record 42 4423 Ex 4423 42 42 Example record 42 6108 Ex 6108 42 42 Example record 42 4086 Ex 4086 42 42 Example record 42 2401 Ex 2401 42 42 Example record 42 1727 Ex 1727 42 42 Example record 42 3412 Ex 3412 42 42 Example record 42 716 Ex 716 42 42 Example record 42 42 Ex 42 42 42 Example record 42 6781 Ex 6781 42 42 Example record 42 1389 Ex 1389 42 42 Example record 42 7792 Ex 7792 42 42 Example record 42 7118 Ex 7118 42 42 Example record 42 5096 Ex 5096 42 42 Example record 42 ... 1685 Ex 1685 86 0 Example record 86 2022 Ex 2022 86 0 Example record 86 2359 Ex 2359 86 0 Example record 86 4044 Ex 4044 86 0 Example record 86 5392 Ex 5392 86 0 Example record 86 6066 Ex 6066 86 0 Example record 86 5729 Ex 5729 86 0 Example record 86 4381 Ex 4381 86 0 Example record 86 4718 Ex 4718 86 0 Example record 86 5055 Ex 5055 86 0 Example record 86 7414 Ex 7414 86 0 Example record 86 7751 Ex 7751 86 0 Example record 86 6403 Ex 6403 86 0 Example record 86 6740 Ex 6740 86 0 Example record 86 337 Ex 337 86 0 Example record 86 52108 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3631114592 -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3000 | 275K| | 110 (4)| 00:00:02 | | 1 | MERGE JOIN | | 3000 | 275K| | 110 (4)| 00:00:02 | | 2 | SORT JOIN | | 150 | 7200 | | 4 (25)| 00:00:01 | | 3 | TABLE ACCESS FULL | TMP_ACCOUNT | 150 | 7200 | | 3 (0)| 00:00:01 | |* 4 | FILTER | | | | | | | |* 5 | SORT JOIN | | 8000 | 359K| 952K| 105 (2)| 00:00:02 | | 6 | TABLE ACCESS FULL| ACCOUNT | 8000 | 359K| | 9 (0)| 00:00:01 | -------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter("B"."AMOUNT"<="A"."HIGHEST_AMOUNT") 5 - access(INTERNAL_FUNCTION("B"."AMOUNT")>=INTERNAL_FUNCTION("A"."LOWEST_AMOUNT" )) filter(INTERNAL_FUNCTION("B"."AMOUNT")>=INTERNAL_FUNCTION("A"."LOWEST_AMOUNT" )) Note ----- - dynamic sampling used for this statement Statistics ---------------------------------------------------------- 152 recursive calls 0 db block gets 92 consistent gets 0 physical reads 0 redo size 1324733 bytes sent via SQL*Net to client 38588 bytes received via SQL*Net from client 3475 SQL*Net roundtrips to/from client 4 sorts (memory) 0 sorts (disk) 52108 rows processed SQL> SQL> select a.account_number, a.id, b.* 2 from account a, tmp_account b 3 where b.account_number = a.account_number 4 and b.amount between a.lowest_amount and a.highest_amount; ACCOUNT_NUMBER ID ACCOUNT_NUMBER AMOUNT DESCR -------------- ---------- -------------- ---------- ---------------------------------------- 1 Ex 1 1 1 Example record 1 2 Ex 2 2 2 Example record 2 3 Ex 3 3 3 Example record 3 4 Ex 4 4 4 Example record 4 5 Ex 5 5 5 Example record 5 6 Ex 6 6 6 Example record 6 7 Ex 7 7 7 Example record 7 8 Ex 8 8 8 Example record 8 9 Ex 9 9 9 Example record 9 10 Ex 10 10 10 Example record 10 11 Ex 11 11 11 Example record 11 12 Ex 12 12 12 Example record 12 13 Ex 13 13 13 Example record 13 14 Ex 14 14 14 Example record 14 15 Ex 15 15 15 Example record 15 16 Ex 16 16 16 Example record 16 17 Ex 17 17 17 Example record 17 18 Ex 18 18 18 Example record 18 19 Ex 19 19 19 Example record 19 20 Ex 20 20 20 Example record 20 21 Ex 21 21 21 Example record 21 22 Ex 22 22 22 Example record 22 23 Ex 23 23 23 Example record 23 24 Ex 24 24 24 Example record 24 25 Ex 25 25 25 Example record 25 26 Ex 26 26 26 Example record 26 27 Ex 27 27 27 Example record 27 28 Ex 28 28 28 Example record 28 29 Ex 29 29 29 Example record 29 30 Ex 30 30 30 Example record 30 31 Ex 31 31 31 Example record 31 32 Ex 32 32 32 Example record 32 33 Ex 33 33 33 Example record 33 34 Ex 34 34 34 Example record 34 35 Ex 35 35 35 Example record 35 36 Ex 36 36 36 Example record 36 37 Ex 37 37 37 Example record 37 38 Ex 38 38 38 Example record 38 39 Ex 39 39 39 Example record 39 40 Ex 40 40 40 Example record 40 41 Ex 41 41 41 Example record 41 42 Ex 42 42 42 Example record 42 42 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1664268811 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 94 | 13 (8)| 00:00:01 | |* 1 | HASH JOIN | | 1 | 94 | 13 (8)| 00:00:01 | | 2 | TABLE ACCESS FULL| TMP_ACCOUNT | 150 | 7200 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| ACCOUNT | 8000 | 359K| 9 (0)| 00:00:01 | ---------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("B"."ACCOUNT_NUMBER"="A"."ACCOUNT_NUMBER") filter("B"."AMOUNT">="A"."LOWEST_AMOUNT" AND "B"."AMOUNT"<="A"."HIGHEST_AMOUNT") Note ----- - dynamic sampling used for this statement Statistics ---------------------------------------------------------- 9 recursive calls 0 db block gets 81 consistent gets 0 physical reads 0 redo size 2401 bytes sent via SQL*Net to client 407 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 42 rows processed SQL> SQL> set autotrace off SQL> SQL> create index account_amt_idx 2 on account(lowest_amount, highest_amount); Index created. SQL> SQL> exec dbms_stats.gather_schema_stats('BING'); PL/SQL procedure successfully completed. SQL> SQL> set autotrace on SQL> SQL> select a.account_number, a.id, b.* 2 from account a, tmp_account b 3 where b.amount between a.lowest_amount and a.highest_amount; ACCOUNT_NUMBER ID ACCOUNT_NUMBER AMOUNT DESCR -------------- ---------- -------------- ---------- ---------------------------------------- 6782 Ex 6782 42 42 Example record 42 1390 Ex 1390 42 42 Example record 42 7793 Ex 7793 42 42 Example record 42 7119 Ex 7119 42 42 Example record 42 5097 Ex 5097 42 42 Example record 42 4423 Ex 4423 42 42 Example record 42 6108 Ex 6108 42 42 Example record 42 4086 Ex 4086 42 42 Example record 42 2401 Ex 2401 42 42 Example record 42 1727 Ex 1727 42 42 Example record 42 3412 Ex 3412 42 42 Example record 42 716 Ex 716 42 42 Example record 42 42 Ex 42 42 42 Example record 42 6781 Ex 6781 42 42 Example record 42 ... 7751 Ex 7751 86 0 Example record 86 6403 Ex 6403 86 0 Example record 86 6740 Ex 6740 86 0 Example record 86 337 Ex 337 86 0 Example record 86 52108 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3631114592 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 3000 | 123K| 16 (25)| 00:00:01 | | 1 | MERGE JOIN | | 3000 | 123K| 16 (25)| 00:00:01 | | 2 | SORT JOIN | | 150 | 3600 | 4 (25)| 00:00:01 | | 3 | TABLE ACCESS FULL | TMP_ACCOUNT | 150 | 3600 | 3 (0)| 00:00:01 | |* 4 | FILTER | | | | | | |* 5 | SORT JOIN | | 8000 | 140K| 11 (19)| 00:00:01 | | 6 | TABLE ACCESS FULL| ACCOUNT | 8000 | 140K| 9 (0)| 00:00:01 | ------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter("B"."AMOUNT"<="A"."HIGHEST_AMOUNT") 5 - access(INTERNAL_FUNCTION("B"."AMOUNT")>=INTERNAL_FUNCTION("A"."LOWEST _AMOUNT")) filter(INTERNAL_FUNCTION("B"."AMOUNT")>=INTERNAL_FUNCTION("A"."LOWEST _AMOUNT")) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 38 consistent gets 0 physical reads 0 redo size 1324733 bytes sent via SQL*Net to client 38588 bytes received via SQL*Net from client 3475 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 52108 rows processed SQL> SQL> select a.account_number, a.id, b.* 2 from account a, tmp_account b 3 where b.account_number = a.account_number 4 and b.amount between a.lowest_amount and a.highest_amount; ACCOUNT_NUMBER ID ACCOUNT_NUMBER AMOUNT DESCR -------------- ---------- -------------- ---------- ---------------------------------------- 1 Ex 1 1 1 Example record 1 2 Ex 2 2 2 Example record 2 3 Ex 3 3 3 Example record 3 4 Ex 4 4 4 Example record 4 5 Ex 5 5 5 Example record 5 6 Ex 6 6 6 Example record 6 7 Ex 7 7 7 Example record 7 8 Ex 8 8 8 Example record 8 9 Ex 9 9 9 Example record 9 10 Ex 10 10 10 Example record 10 11 Ex 11 11 11 Example record 11 12 Ex 12 12 12 Example record 12 13 Ex 13 13 13 Example record 13 14 Ex 14 14 14 Example record 14 15 Ex 15 15 15 Example record 15 16 Ex 16 16 16 Example record 16 17 Ex 17 17 17 Example record 17 18 Ex 18 18 18 Example record 18 19 Ex 19 19 19 Example record 19 20 Ex 20 20 20 Example record 20 21 Ex 21 21 21 Example record 21 22 Ex 22 22 22 Example record 22 23 Ex 23 23 23 Example record 23 24 Ex 24 24 24 Example record 24 25 Ex 25 25 25 Example record 25 26 Ex 26 26 26 Example record 26 27 Ex 27 27 27 Example record 27 28 Ex 28 28 28 Example record 28 29 Ex 29 29 29 Example record 29 30 Ex 30 30 30 Example record 30 31 Ex 31 31 31 Example record 31 32 Ex 32 32 32 Example record 32 33 Ex 33 33 33 Example record 33 34 Ex 34 34 34 Example record 34 35 Ex 35 35 35 Example record 35 36 Ex 36 36 36 Example record 36 37 Ex 37 37 37 Example record 37 38 Ex 38 38 38 Example record 38 39 Ex 39 39 39 Example record 39 40 Ex 40 40 40 Example record 40 41 Ex 41 41 41 Example record 41 42 Ex 42 42 42 Example record 42 42 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1664268811 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 42 | 13 (8)| 00:00:01 | |* 1 | HASH JOIN | | 1 | 42 | 13 (8)| 00:00:01 | | 2 | TABLE ACCESS FULL| TMP_ACCOUNT | 150 | 3600 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| ACCOUNT | 8000 | 140K| 9 (0)| 00:00:01 | ---------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("B"."ACCOUNT_NUMBER"="A"."ACCOUNT_NUMBER") filter("B"."AMOUNT">="A"."LOWEST_AMOUNT" AND "B"."AMOUNT"<="A"."HIGHEST_AMOUNT") Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 41 consistent gets 0 physical reads 0 redo size 2401 bytes sent via SQL*Net to client 407 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 42 rows processed SQL> The original 'join' produces useless noise along with some valid data buried deep within. Even creating the suggested index doesn't do any good (although that may be due to how I configured the data). David Fitzjarrell -- //www.freelists.org/webpage/oracle-l