Hi,

Thanks for your reply.

I am looking for multicolumn clustered index as claimed in http://www.monetdb.org/Documentation/Manuals/SQLreference/Indices

Can I get some more details on "MonetDB uses the sort property as a Cluster" and how to set it?

Regards, Ashish

On 28/08/13 5:10 PM, "Lefteris" lsidir@gmail.com wrote:

what do you mean by clustered indexes? multi column clustering? or just one column? in the later, MonetDB uses the sort property as a cluster

On Wed, Aug 28, 2013 at 1:23 PM, Ashish Kumar Singh ashishkumar.singh@altair.com wrote:

...

Any thoughts from anybody?

From: Ashish Singh ashishkumar.singh@altair.com Date: Wednesday, 28 August 2013 1:28 AM To: "users-list@monetdb.org" users-list@monetdb.org Cc: Ashish Singh ashishkumar.singh@altair.com Subject: Clustered Indexes with monetdb

HI All,

Can somebody point me to relevant information on how to do clustered indexes with monetdb?

Regards, Ashish

---

users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

Dear all,

to avoid any confusion (and hopefully satisfy anybodies impatience on getting an "instant" answer on this best-effort free-off charge mailing list), let me point out that MonetDB does not use (read: rely on) any user-specified clustered indexes, and consequently does not provide any means to create them. This also means that --- as Lefteris correctly pointed out --- any SQL "CREATE INDEX" statement is (currently) parsed, administrated in the schema catalog (e.g., to be able to handle "DROP INDEX" statements correctly), but otherwise ignored.

A common way to mimic clustered index is to create a sorted version of a table, e.g., via, say, "CREATE TABLE <my-clustered-table> AS SELECT * FROM <my-table> ORDER BY <my-cluster-attributes> WITH DATA;".

As Lefteris also correctly pointed out, MonetDB then internally exploits the knowledge that the column holding the first attribute listed in <my-cluster-attributes> is sorted during query processing. MonetDB is (currently?) not aware of the fact that subsequent columns from <my-cluster-attributes> are sub-ordered within distinct values of the preceeding columns, and thus cannot exploit this property during query processing.

Hope this clarifies and helps (in time).

Best, Stefan

Lefteris lsidir@gmail.com wrote:

if you define a column to be sorted, monetdb will use different algorithms to access it, such as binary search and merge joins.

In the link you send, as far as I know, the sentence "often freely neglects it" should be interpreted as "neglects it".

I am also not aware of the latest status, work is been done on this issues. But until recent, there was no inter-column properties shared in monetdb. so one can not say that column B is subordered on column A (which would lead to a deep clustering schema).

What do you want to do? keep in mind that monetdb will create as needed hash indexes and bitmaps(in the default branch, not the stable) without asking you. MonetDB is almost self-tuning when it comes to indexes.

On Wed, Aug 28, 2013 at 1:43 PM, Ashish Kumar Singh ashishkumar.singh@altair.com wrote:

...

Hi,

Thanks for your reply.

I am looking for multicolumn clustered index as claimed in http://www.monetdb.org/Documentation/Manuals/SQLreference/Indices

Can I get some more details on "MonetDB uses the sort property as a Cluster" and how to set it?

Regards, Ashish

On 28/08/13 5:10 PM, "Lefteris" lsidir@gmail.com wrote:

...

what do you mean by clustered indexes? multi column clustering? or just one column? in the later, MonetDB uses the sort property as a cluster

On Wed, Aug 28, 2013 at 1:23 PM, Ashish Kumar Singh ashishkumar.singh@altair.com wrote:

...

Any thoughts from anybody?

From: Ashish Singh ashishkumar.singh@altair.com Date: Wednesday, 28 August 2013 1:28 AM To: "users-list@monetdb.org" users-list@monetdb.org Cc: Ashish Singh ashishkumar.singh@altair.com Subject: Clustered Indexes with monetdb

HI All,

Can somebody point me to relevant information on how to do

clustered

...

...

...

indexes with monetdb?

Regards, Ashish

---

users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

---

users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

Thank you all it is helpful!

From: Stefan Manegold <Stefan.Manegold@cwi.nlmailto:Stefan.Manegold@cwi.nl> Date: Wednesday, 28 August 2013 6:20 PM To: "users-list@monetdb.orgmailto:users-list@monetdb.org" <users-list@monetdb.orgmailto:users-list@monetdb.org>, Lefteris <lsidir@gmail.commailto:lsidir@gmail.com>, Ashish Singh <ashishkumar.singh@altair.commailto:ashishkumar.singh@altair.com> Cc: "dev-list@monetdb.orgmailto:dev-list@monetdb.org" <dev-list@monetdb.orgmailto:dev-list@monetdb.org> Subject: Re: Clustered Indexes with monetdb

ps: There is no way to "*define* a column as sorted". What Lefteris IMHO intended to say it that if you sort a table (see my example) or load order data, MonetDB will recognize and exploit this fact (on a per column basis).

Stefan Manegold <Stefan.Manegold@cwi.nlmailto:Stefan.Manegold@cwi.nl> wrote: Dear all,

to avoid any confusion (and hopefully satisfy anybodies impatience on getting an "instant" answer on this best-effort free-off charge mailing list), let me point out that MonetDB does not use (read: rely on) any user-specified clustered indexes, and consequently does not provide any means to create them. This also means that --- as Lefteris correctly pointed out --- any SQL "CREATE INDEX" statement is (currently) parsed, administrated in the schema catalog (e.g., to be able to handle "DROP INDEX" statements correctly), but otherwise ignored.

A common way to mimic clustered index is to create a sorted version of a table, e.g., via, say, "CREATE TABLE <my-clustered-table> AS SELECT * FROM <my-table> ORDER BY <my-cluster-attributes> WITH DATA;".

As Lefteris also correctly pointed out, MonetDB then internally exploits the knowledge that the column holding the first attribute listed in <my-cluster-attributes> is sorted during query processing. MonetDB is (currently?) not aware of the fact that subsequent columns from <my-cluster-attributes> are sub-ordered within distinct values of the preceeding columns, and thus cannot exploit this property during query processing.

Hope this clarifies and helps (in time).

Best, Stefan

Lefteris <lsidir@gmail.commailto:lsidir@gmail.com> wrote:

if you define a column to be sorted, monetdb will use different algorithms to access it, such as binary search and merge joins.

In the link you send, as far as I know, the sentence "often freely neglects it" should be interpreted as "neglects it".

I am also not aware of the latest status, work is been done on this issues. But until recent, there was no inter-column properties shared in monetdb. so one can not say that column B is subordered on column A (which would lead to a deep clustering schema).

What do you want to do? keep in mind that monetdb will create as needed hash indexes and bitmaps(in the default branch, not the stable) without asking you. MonetDB is almost self-tuning when it comes to indexes.

On Wed, Aug 28, 2013 at 1:43 PM, Ashish Kumar Singh <ashishkumar.singh@altair.commailto:ashishkumar.singh@altair.com> wrote: Hi,

Thanks for your reply.

I am looking for multicolumn clustered index as claimed in http://www.monetdb.org/Documentation/Manuals/SQLreference/Indices

Can I get some more details on "MonetDB uses the sort property as a Cluster" and how to set it?

Regards, Ashish

On 28/08/13 5:10 PM, "Lefteris" <lsidir@gmail.commailto:lsidir@gmail.com> wrote:

what do you mean by clustered indexes? multi column clustering? or just one column? in the later, MonetDB uses the sort property as a cluster

On Wed, Aug 28, 2013 at 1:23 PM, Ashish Kumar Singh <ashishkumar.singh@altair.commailto:ashishkumar.singh@altair.com> wrote: Any thoughts from anybody?

From: Ashish Singh <ashishkumar.singh@altair.commailto:ashishkumar.singh@altair.com> Date: Wednesday, 28 August 2013 1:28 AM To: "users-list@monetdb.orgmailto:users-list@monetdb.org" <users-list@monetdb.orgmailto:users-list@monetdb.org> Cc: Ashish Singh <ashishkumar.singh@altair.commailto:ashishkumar.singh@altair.com> Subject: Clustered Indexes with monetdb

HI All,

Can somebody point me to relevant information on how to do clustered indexes with monetdb?

Regards, Ashish

________________________________

users-list mailing list users-list@monetdb.orgmailto:users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

________________________________

users-list mailing list users-list@monetdb.orgmailto:users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

-- | Stefan.Manegold@CWI.nlmailto:Stefan.Manegold@CWI.nl | Database Architectures (DA) | | www.CWI.nl/~manegoldhttp://www.CWI.nl/~manegold | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |