Join index

Ying Zhang Y.Zhang at cwi.nl
Thu Jul 18 00:14:26 CEST 2013


Hello Bouslah,

MonetDB doesn't make use of user created indices, i.e., the CREATE INDEX statement is just a NOP.  If necessary, the DBMS engine will automatically decide to create and use hash index.

What is the execution time of your query and what is the expected execution time?
Maybe you can try if reorganising the conditions in WHERE has any effect on the execution time?
Maybe you can also try to reformulate your query to make it easier to push down some selections.
As far as I know, our optimiser may not be smart enough to find the optimal execution order for your WHERE clause.

With kind regards,

Jennie

On Jul 17, 2013, at 21:23, Bouslah Ahmed wrote:

> Hi, 
> I want to optimize the runtime of this query
> 
> Select sum(a.C4), b.C1, b.C2, b.C3
> from v1 a, v2 b
> where 
> (b.C1 = a.C1 and b.C2 = a.C2 and b.C3 = a.C3)
> or 
> (b.C1 = a.C1 and b.C2 = a.C2 and b.C3 = 0)
> or
> (b.C1 = a.C1 and b.C2 = 0 and b.C3 = 0)
> or
> (b.C1 = 0 and b.C2 = 0 and b.C3 = 0)
> group by b.C1, b.C2, b.C3;
> 
> why I thought of creating a BITMAP index join or create a bitmap index for v1 columns (c1, c2, c3) and v2 (c1, c2, c3).
> I can't do this with MonetDB
> 
> can you help me.
> 
> 
> _______________________________________________
> users-list mailing list
> users-list at monetdb.org
> http://mail.monetdb.org/mailman/listinfo/users-list




More information about the users-list mailing list