db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bernt M. Johnsen" <Bernt.John...@Sun.COM>
Subject Re: slow subqueries
Date Sat, 11 Nov 2006 20:35:36 GMT
>>>>>>>>>>>> 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

Mime
View raw message