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

Belakimem wrote: > > 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 ); > > 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. Hi, I would be interested in the query plan of the remote select statement. If you are using 6.x or higher, can you please run "onstat -g sql" to find out the query plan that is running on the remote machine ? What about the size of your table "db1@unixbox:d" ? How many rows will be returned by that "select * from db1@... " ? My guess: The select will not use the index placed on the column "a", because the remote machine cannot determine the number of rows that will be returned by the second select "...from temp table...". Therefore it performs a sequential scan instead of an indexed read. Maybe it will become better if you would first create an additional temporary table at the remote site, and then run the remote select together with the remote table. insert into db1@unixbox:temptable select distinct a from b where x=y..; insert into c select * from db1@unixbox:d a, db1@unixbox:temptable b where a.a = b.a; Problem: How to create the temporary table on the remote site ? Bye Stefan