>>>>>>>>>>>> Jim Newsham wrote (2006-11-11 10:07:18): > > > > > This is why a nested loop is not going to work here... 20,000 squared > > > operations is very expensive, let alone millions squared. For a query > > with > > > this profile, the inner query should only be executed once. > > > > Perhaps you can get the behavior you desire by explicitly creating > > a temporary table, selecting the data from your inner query into > > the temporary table, then re-writing your main query to join against > > the temporary table? > > I'd be willing to try that. Some questions come to mind: > > - Is there a way in sql to select the results of a query into a > - table?
insert into someTable (select * from someOtherTable); > - When I'm done with the temp table, can I just drop it as for a normal > table? Yes. DROP table works on temporary tables. > - I think I read somewhere that I can't add an index to temporary tables. > Is this true? I can't even have a primary key? Yes, no indices nor primary keys. > If so, I think the performance will still be intractable for > thousands or millions of results in the temp table. Why not let your temporary table be a normal table in your schema? > > Thanks, > Jim > > -- Bernt Marius Johnsen, Database Technology Group, Staff Engineer, Technical Lead Derby/Java DB Sun Microsystems, Trondheim, Norway
signature.asc
Description: Digital signature
