search

google-code-export / h2database

Automatically exported from code.google.com/p/h2database
0 stars 1 forks source link

CREATE AS SELECT direct write optimization #232

Closed GoogleCodeExporter closed 9 years ago

GoogleCodeExporter commented 9 years ago 
The SELECT results should be written directly to the target table when possible.

See discussion here: 
http://groups.google.com/group/h2-database/browse_thread/thread/d0d4ac8a2418b32e

Please find draft changes to the code attached.

--Alexey

Original issue reported on code.google.com by adkry...@gmail.com on 1 Oct 2010 at 9:33

Attachments:

GoogleCodeExporter commented 9 years ago 
Thanks a lot for the patch! It's very good. Is it OK for you if I work on it a 
bit, and then send it back to you so you can apply it? Or I can commit it if 
you want me to. In general I think it's a good approach to use the LocalResult 
as a parameter to the query method. I will also help a lot when implementing 
server side cursors.

Original comment by thomas.t...@gmail.com on 5 Oct 2010 at 8:12

GoogleCodeExporter commented 9 years ago

Original comment by thomas.t...@gmail.com on 5 Oct 2010 at 8:12

GoogleCodeExporter commented 9 years ago 
Sure, please do work on it. I can create a patch off your changes when you 
commit and either fix my local version accordingly or wait for the next h2 
release. I'm glad this was helpful. Thank you

Original comment by adkry...@gmail.com on 6 Oct 2010 at 6:04

GoogleCodeExporter commented 9 years ago 
There is a problem for inserts that reference itself. Example:

INSERT INTO TEST SELECT * FROM TEST

In theory the engine could detect if an insert references itself, but it's a 
bit dangerous; it's very easy to make mistakes in this circular reference 
detection algorithm (for example if there are triggers, constraints, Java 
functions and so on).

It probably safer to only support CREATE TABLE ... AS SELECT for now.

Original comment by thomas.t...@gmail.com on 10 Oct 2010 at 9:48

GoogleCodeExporter commented 9 years ago 
I see what you mean... At any rate CREATE TABLE ... AS SELECT is the main use 
case in my application, so having support just for this case would help me a 
great deal.

Original comment by adkry...@gmail.com on 11 Oct 2010 at 7:22

GoogleCodeExporter commented 9 years ago 
Implemented in version 1.2.144 (see the change log).

Original comment by thomas.t...@gmail.com on 15 Oct 2010 at 6:28