Makoto YUI wrote:
Hi folks,
I continue searching for a workaround for "create table .. as select .. order by .. limit .." in MonetDB. I would like to create a table using a result-set of a query with "order by & limit" though I'm aware that this is a geeky requirement.
-- pareparing data create table t1 (f1 int, f2 char); insert into t1 values(3, 'a'); insert into t1 values(1, 'b'); insert into t1 values(4, 'c'); insert into t1 values(5, 'd'); insert into t1 values(2, 'e');
[Not allowed in SQL spec but what I somehow want]
create table t2 as select * from t1 where f2 <> 'b' order by f1 limit 3;
If your intention is to stop after seeing the first 3 tuples where f2 <> 'b', then i have to disappoint you. MonetDB will calculate all answers first and deliver only 3.
Here below is my unwise workaround representing the above DDL&DML :-( Are there any better suggestions?
---------------------------- [workaround #1]
create table t2 as ( select f1,f2 from (select *, row_number() over (order by f1) as rownum from t1 where f2 <> 'b') as tmp where tmp.rownum <= 3 ) with data;
Pros: Everything is done in SQL. Cons: However, more than 3 or every records are scanned.
yes it will always do that for you, there is no escape as far as i can see. Other then a user defined function that implements something like select(f2,'<>','b',3)
Automatic query rewriting is slightly hard.
---------------------------- [workaround #2]
create table t2 as select * from t1 where f2 <> 'b' with no data;
Then, is t1 fully scanned in the above case?
look at the TRACE <sqlquery> and you will see both the timing and the result sizes of all intermediates.
Or, only 1 record is scanned with the "no data" option.
select * from t1 where f2 <> 'b' order by f1 limit 3; foreach(r: results) { // batch inserts using prepared statements insert into t2 values(r.f1,..,r.fN); insert into t2 values(r.f1,..,r.fN); insert into t2 values(r.f1,..,r.fN); insert into t2 values(r.f1,..,r.fN); }
Pros: Redundant scan might be avoided. Cons: Data copy between client and DB would happen. Moreover, iterating the resultset and inserting records would be in-efficient.
--------------------------------------- BTW, a table 't_union' is not created though the following query as in [1] are successfully executed.
create table t1 (f1 int); -- insert into t1 values(1); /* at least one result is requred */ create table t2 (f1 int); create table t_union as (select f1 from t1 union all select f1 from t2) with data;
The table 't_union' is not created even when using "with no data" option. This would be a minor documentation issue.
Thanks, Makoto
------------------------------------------------------------------------------ This SF.Net email is sponsored by the Verizon Developer Community Take advantage of Verizon's best-in-class app development support A streamlined, 14 day to market process makes app distribution fast and easy Join now and get one step closer to millions of Verizon customers http://p.sf.net/sfu/verizon-dev2dev _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users