Re: *** SQL Problem, please help ***

In article <19970225200500.PAA06127@ladder02.news.aol.com>, Belakimem <belakimem@aol.com> writes >The second of the folowing SQL's takes forever. > >select distinct a from b where x = y into temp temp_table;> >{ this retrieves 3 rows } > >insert into c select * from db1@unixbox1:d where>a in ( select a from temp_table ); > using in usually results in a full scan on the dominant table instead try:- insert into c select db1@unixbox1:d.* from temp_table,db1@unixbox1:d where temp_table.a = db1@unixbox1:d.a I sometimes find that rewriting the query such that the from clause includes in table in the order I wish the query to use them i.e. first temp_table then b1@unixbox1:d and ordering the where clause in the same way i.e. tabl1.col1 = tab2.col1 and tab2.col2 = tab3.col_2..helps. I suppose this is because the optimizer will ALWAYS at least evaluate the cost of the 'obvious' join strategy i.e. left to right and the 'obvious' one I know will have the least possible cost. PS ALso do update statistcs on the table db1@unixbox1:d (you may need to run this from the remote machine??) ABD update statistics high on db1@unixbox1:d.a >If I change the "select a from temp_table" for an >explicit "(value1,value2,value3)", then it is very fast. > >Obviously I cannot do this as I do not know what the values are going to >be ! > >The problem is clearly related to the fact that the temp table resides on >the "local" Unix box. > >Any thoughts / ideas on how to speed this up? > > >Potential responders, please reply to my email address as well as the >Newsgroup. Thank You. > > -- David Williams