Niels Nes wrote:
On Tue, Apr 15, 2008 at 01:16:46PM +0200, Nils Grimsmo wrote:
Hello, all!
I am about to prototype something, and it would be convenient to use SQL to throw it together quickly to see if it flies. I downloaded MonetDB yesterday, so I am quite fresh :-)
I have a table with a set of columns, and the like to have a few materialized views or projections (or whatever you DB people call it ;-)) sorted on different columns. The views may also have selection, I am not sure yet.
The MonetDB/SQL views aren't materialized, ie a view which sorts (even if its allowed by the parser (which it isn't)), will be very slow.
And given problem (a data query), I use some statistics to decide which sorting order is most convenient, look up a (preferably continuous) vertical part of the columns, scan through it, and possibly do some filtering. The queries may also be trees, in which case I might use a different sorting order for each branch, and then do merging based on some of the columns.
Assume:
CREATE TABLE t (a INT, b INT); INSERT INTO t VALUES (1, 3), (4, 2);
I would like to do something like:
CREATE VIEW v AS SELECT b, a FROM t ORDER BY b, a;
Instead use a second table which holds the data of a order on b, a (possibly with a key between v and t)?
This is not allowed (works if I remove ORDER BY). I would also like to have an index over v(b,a), like:
CREATE UNIQUE INDEX j USING BTREE ON v (b, a) ;
We don't support BTREEs.
What kind of indexes are used? (It does not say under http://monetdb.cwi.nl/projects/monetdb/SQL/Documentation/Indexes.html)
But this is never allowed on a view, right? Since updates are not an issue in the prototype, I could just create a new table like this:
CREATE TABLE v (b int, a int, PRIMARY KEY(b, a), INDEX i USING BTREE (b ASC, a ASC)) AS SELECT b, a FROM t ORDER BY b, a;
(Using both PRIMARY KEY and INDEX is always butter on bacon?) But this is not allowed because of the table definition, all I am allowed to do is:
CREATE TABLE v (b, a) AS SELECT b, a FROM t ORDER BY b, a WITH DATA;
If I use PRIMARY KEY(b, a), am I then guaranteed the table to be sorted on (b, a)? Is there any other way of enforcing this in SQL?
No only if you insert in order, ie if you only do create table v (b,a) as select b, a from t order by b, a with data; and never insert it will stay sorted.
But with CREATE TABLE AS SELECT I cannot use PRIMARY KEY. Can I still get a fast retrieval of SELECT * FROM v WHERE b=13 AND a=14? Should I CREATE INDEX?
1. Any suggestions for what I should do? Is my best option to create all "views" as tables from scratch, and fill them by hand?
We are thinking of implementing 'clustered indices' for this, but currently its not ready (ie implementation still needs to be started).
2. How much slower would this be than using low-level stuff like BATs and BUNs myself? (The merges between branches in my (problem level) queries are simple, but cannot be expressed as SQL.)
Performance difference shouldn't be too high. Only if you have a lot of application knowledge you could exploit in the low level case, large difference can be expected.
Thank you for a swift and informative answer! Klem fra Nils