Martin, Martin Kersten wrote:
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.
My intention was this behavior as a result of a sort of volcano-style processing.
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)
I see.
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.
"set trace = 'show,ticks,stmt,rbytes,wbytes'; trace .." was very useful. However, trace has no effect on "create table" (DDL). So, I could not find how "with no data"/"with data" effects. I assume that "with (no) data" does not effect query execution itself. Thanks, Makoto