On Wed, Mar 26, 2008 at 02:28:05AM +0100, Michael Schmidt wrote:
Hi Niels,
INSERT INTO table_name (...) ORDER BY X,Y
OK, thank you for your answer. Two more questions:
1.) Is it also ok to insert the triples in sorted order one after the other, instead of copying combined with ORDER BY? Is there any possibility to see whether MonetDB /really/ recognizes that tuples are ordered? Inserting them one at the time will be slow but indeed we should recognize that the insorted values are sorted.
The sortedness of columns can only be seen on the mal/c-level. There is no easy way to show that (yet).
2.) How can I detect whether MonetDB chooses merge joins when joining on ordered columns. I used EXPLAIN and investigated the QEP (though I must confess that I don't really understand what's going on), but there were only "algebra.join" operations, whatever this means...
MonetDB uses dynamic (or runtime) optimization, ie the kind of join is chosen at runtime. So algebra.join could mean merge join as well as hash join. I you want to see I a 'fast' merge join is taken you should run with debugging flags. This will however give a lot of information (probably too much). So simply check if your runtimes improved is the easiest way. Assume you did a order by on X, the Y. Do a select count(*) where X == 'some constant' group by Y; That should be much faster in the ordered case than in the not ordered case. Niels
Michael
-- Niels Nes, Centre for Mathematics and Computer Science (CWI) Kruislaan 413, 1098 SJ Amsterdam, The Netherlands room C0.02, phone ++31 20 592-4098, fax ++31 20 592-4312 url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl