With similar range join problem I have used sdo_join to reduce execution time from days to minutes. With this example data sdo_join is slower. A basic example of using function based r-tree indexing http://rafudb.blogspot.com/2010/04/dark-side-of-easter.html And for this issue an SDO_JOIN example: select min(lowest_amount),max(lowest_amount),min(highest_amount),max(highest_amount)from account; 0 92 select min(amount),max(amount)from tmp_account; 0 42 CREATE OR REPLACE FUNCTION TF(FRO number, TIL number) RETURN SDO_GEOMETRY deterministic as BEGIN RETURN case when fro <= til then MDSYS.SDO_GEOMETRY(2002,NULL, NULL, SDO_ELEM_INFO_ARRAY (1,2,1), SDO_ORDINATE_ARRAY(FRO,0,TIL,0)) end; END; / CREATE OR REPLACE FUNCTION P(A number) RETURN SDO_GEOMETRY deterministic as BEGIN RETURN MDSYS.SDO_GEOMETRY(2001,NULL, SDO_POINT_TYPE(A, 0, NULL), null, null); END; / DELETE FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME = 'ACCOUNT'; INSERT INTO USER_SDO_GEOM_METADATA(TABLE_NAME,COLUMN_NAME,DIMINFO) VALUES ( 'ACCOUNT', 'RAFU.TF(LOWEST_AMOUNT,HIGHEST_AMOUNT)', SDO_DIM_ARRAY( SDO_DIM_ELEMENT('X', 0, 92, 0.5), SDO_DIM_ELEMENT('Y', 0, 0, 0.5) ) ) ; DELETE FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME = 'TMP_ACCOUNT'; INSERT INTO USER_SDO_GEOM_METADATA(TABLE_NAME,COLUMN_NAME,DIMINFO) VALUES ( 'TMP_ACCOUNT', 'RAFU.P(AMOUNT)', SDO_DIM_ARRAY( SDO_DIM_ELEMENT('X', 0, 42, 0.5), SDO_DIM_ELEMENT('Y', 0, 0, 0.5) ) ) ; COMMIT; DROP INDEX A_IDX; CREATE INDEX A_IDX ON ACCOUNT(TF(lowest_amount,highest_amount)) INDEXTYPE IS mdsys.spatial_index; DROP INDEX AT_IDX; CREATE INDEX AT_IDX ON TMP_ACCOUNT(P(amount)) INDEXTYPE IS mdsys.spatial_index; select /*+ordered*/a.account_number, a.id, b.* from table(sdo_join('TMP_ACCOUNT','RAFU.P(AMOUNT)','ACCOUNT','RAFU.TF(LOWEST_AMOUNT,HIGHEST_AMOUNT)','mask=anyinteract')) sj , tmp_account b , account a where sj.rowid1=b.rowid and sj.rowid2=a.rowid ; -- Timo Raitalaakso http://rafudb.blogspot.com On 21.10.2011 12:06, David Fitzjarrell wrote: > 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. > ... > > David Fitzjarrell -- //www.freelists.org/webpage/oracle-l