Update of /cvsroot/monetdb/sql/src/server
In directory sc8-pr-cvs16.sourceforge.net:/tmp/cvs-serv19756/src/server
Modified Files:
rel_bin.mx rel_exp.mx rel_optimizer.mx rel_prop.mx
sql_parser.mx sql_rel2bin.mx sql_scan.mx sql_statement.mx
sql_updates.mx
Log Message:
the relational version now does use the hash indices on selects
U sql_updates.mx
Index: sql_updates.mx
===================================================================
RCS file: /cvsroot/monetdb/sql/src/server/sql_updates.mx,v
retrieving revision 1.137
retrieving revision 1.138
diff -u -d -r1.137 -r1.138
--- sql_updates.mx 25 Apr 2008 07:25:29 -0000 1.137
+++ sql_updates.mx 14 May 2008 20:18:28 -0000 1.138
@@ -380,7 +380,7 @@
{
stmt *is = NULL;
- if (i->type == unique) {
+ if (i->type == hash_idx) {
is = hash_insert(sql, i, inserts);
} else if (i->type == join_idx) {
is = join_idx_insert(sql, i, inserts);
@@ -1030,7 +1030,7 @@
if (is_idx_updated(i, updates) == 0)
continue;
- if (i->type == unique) {
+ if (i->type == hash_idx) {
is = hash_update(sql, i, updates, updcol);
} else if (i->type == join_idx) {
is = join_idx_update(i, updates, updcol);
U sql_rel2bin.mx
Index: sql_rel2bin.mx
===================================================================
RCS file: /cvsroot/monetdb/sql/src/server/sql_rel2bin.mx,v
retrieving revision 1.117
retrieving revision 1.118
diff -u -d -r1.117 -r1.118
--- sql_rel2bin.mx 6 Mar 2008 08:36:00 -0000 1.117
+++ sql_rel2bin.mx 14 May 2008 20:18:26 -0000 1.118
@@ -792,7 +792,7 @@
sql_idx *i;
for(in = t->idxs.set->h; in; in = in->next){
i = in->data;
- if (i->type == unique &&
+ if (i->type == hash_idx &&
list_length(l) == list_length(i->columns)) {
node *icn;
U rel_bin.mx
Index: rel_bin.mx
===================================================================
RCS file: /cvsroot/monetdb/sql/src/server/rel_bin.mx,v
retrieving revision 1.40
retrieving revision 1.41
diff -u -d -r1.40 -r1.41
--- rel_bin.mx 24 Apr 2008 20:42:27 -0000 1.40
+++ rel_bin.mx 14 May 2008 20:18:23 -0000 1.41
@@ -287,10 +287,6 @@
/* here we handle join indices */
if ((p=find_prop(e->p, PROP_JOINIDX)) != NULL) {
- /* it maybe much easier to rewrite the join expression
- in the optimizer, to allready use the 'columns'
- fk_idx, pk_idx
- */
sql_idx *i = p->value, *ri;
sql_fkey *fk = (sql_fkey*)i->key;
sql_exp *el = e->l;
@@ -303,9 +299,9 @@
if (!l) {
swapped = 1;
l = bin_find_column(right, el->l, i->base.name);
- r = bin_find_column(left, er->l, ri->base.name);
+ r = bin_find_column(left, er->l, "%OID%");
} else {
- r = bin_find_column(right, er->l,
ri->base.name);
+ r = bin_find_column(right, er->l, "%OID%");
}
/* small performance improvement, ie use idx directly */
if (l->type == st_alias &&
@@ -426,18 +422,22 @@
list_append(l, sc);
}
if (t->idxs.set) {
+ /* oid column */
+ if (t->columns.set->h) {
+ sql_column *c = t->columns.set->h->data;
+ stmt *sc = stmt_bat(c, stmt_dup(ts), RDONLY);
+ char *rnme = (rel->name)?rel->name:t->base.name;
+
+ rnme = (rnme)?_strdup(rnme):NULL;
+ sc = stmt_mirror(sc);
+ sc = stmt_alias(sc, rnme, _strdup("%OID%"));
+ list_append(l, sc);
+ }
for (n = t->idxs.set->h; n; n = n->next) {
sql_idx *i = n->data;
- stmt *sc = NULL;
+ stmt *sc = stmt_idxbat(i, RDONLY);
char *rnme = (rel->name)?rel->name:t->base.name;
- if (i->type == join_idx) {
- sc = stmt_idxbat(i, RDONLY);
- } else {
- sql_column *c = t->columns.set->h->data;
- sc = stmt_bat(c, stmt_dup(ts), RDONLY);
- sc = stmt_mirror(sc);
- }
rnme = (rnme)?_strdup(rnme):NULL;
sc = stmt_alias(sc, rnme, _strdup(i->base.name));
list_append(l, sc);
@@ -1135,6 +1135,40 @@
}
static stmt *
+rel2bin_hash_lookup( mvc *sql, sql_rel *rel, stmt *sub, sql_idx *i, node *en )
+{
+ sql_subtype *it = sql_bind_localtype("int");
+ stmt *h = NULL;
+ stmt *bits = stmt_atom_int(1 + 31/(list_length(i->columns)+2));
+ sql_exp *e = en->data;
+ sql_exp *l = e->l;
+ stmt *idx = bin_find_column(sub, l->l, i->base.name);
+
+ for( en = rel->exps->h; en; en = en->next ) {
+ sql_exp *e = en->data;
+ stmt *s;
+
+ assert(e->type == e_cmp && e->flag == cmp_equal);
+ s = exp_bin(sql, e->r, NULL, NULL, NULL, NULL);
+
+ if (h) {
+ sql_subfunc *xor =
sql_bind_func_result3(sql->session->schema, "rotate_xor_hash", it, it,
tail_type(s), it);
+
+ h = stmt_Nop(stmt_list( list_append( list_append(
+ list_append(create_stmt_list(), h),
+ stmt_dup(bits)), s)), xor);
+ } else {
+ sql_subfunc *hf =
sql_bind_func_result(sql->session->schema, "hash", tail_type(s), NULL, it);
+
+ h = stmt_unop(s, hf);
+ }
+ }
+ stmt_destroy(bits);
+ return stmt_uselect(idx, h, cmp_equal);
+}
+
+
+static stmt *
rel2bin_select( mvc *sql, sql_rel *rel )
{
list *l;
@@ -1156,6 +1190,18 @@
} else {
predicate = rel2bin_predicate();
}
+ /* handle possible index lookups */
+ /* expressions are in index order ! */
+ if ( (en = rel->exps->h) != NULL) {
+ sql_exp *e = en->data;
+ prop *p;
+
+ if ((p=find_prop(e->p, PROP_HASHIDX)) != NULL) {
+ sql_idx *i = p->value;
+
+ sel = rel2bin_hash_lookup(sql, rel, sub, i, en);
+ }
+ }
for( en = rel->exps->h; en; en = en->next ) {
stmt *s = exp_bin(sql, en->data, sub, NULL, NULL, sel);
U rel_optimizer.mx
Index: rel_optimizer.mx
===================================================================
RCS file: /cvsroot/monetdb/sql/src/server/rel_optimizer.mx,v
retrieving revision 1.15
retrieving revision 1.16
diff -u -d -r1.15 -r1.16
--- rel_optimizer.mx 9 Apr 2008 09:56:23 -0000 1.15
+++ rel_optimizer.mx 14 May 2008 20:18:24 -0000 1.16
@@ -548,6 +548,7 @@
idx = find_fk_index(r, rcols, l, lcols);
swapped = 1;
}
+ /* TODO then maybe 2 hash indices */
#ifdef DEBUG
printf("%s %d %s %d (%d)\n",
@@ -1024,6 +1025,156 @@
return rel;
}
+static int
+index_exp(sql_exp *e, sql_idx *i)
+{
+ if (e->type == e_cmp) {
+ switch(i->type) {
+ case hash_idx:
+ if (e->flag == cmp_equal)
+ return 0;
+ case join_idx:
+ default:
+ return -1;
+ }
+ }
+ return -1;
+}
+
+static sql_column *
+selectexp_col(sql_exp *e, sql_rel *r)
+{
+ sql_table *t = r->l;
+
+ if (e->type == e_cmp) {
+ sql_exp *ec = e->l;
+
+ if (ec->type == e_column) {
+ char *name = ec->name;
+ node *cn;
+
+ if (r->exps) { /* use alias */
+ for (cn = r->exps->h; cn; cn = cn->next) {
+ sql_exp *ce = cn->data;
+ if (strcmp(ce->name, name) == 0) {
+ name = ce->r;
+ break;
+ }
+ }
+ }
+ for (cn = t->columns.set->h; cn; cn = cn->next) {
+ sql_column *c = cn->data;
+ if (strcmp(c->base.name, name) == 0)
+ return c;
+ }
+ }
+ }
+ return NULL;
+}
+
+static sql_idx *
+find_index(sql_rel *r, list **EXPS)
+{
+ sql_rel *b;
+ sql_table *t;
+
+ if ((b = find_basetable(r)) == NULL)
+ return NULL;
+
+ /* any (partial) match of the expressions with the index columns */
+ /* Depending on the index type we may need full matches and only
+ limited number of cmp types (hash only equality etc) */
+ /* Depending on the index type we should (in the rel_bin) generate
+ more code, ie for spatial index add post filter etc, for hash
+ compute hash value and use index */
+ t = b->l;
+ if (t->idxs.set) {
+ node *in;
+
+ /* find the columns involved in the selection over this base
table*/
+ for(in = t->idxs.set->h; in; in = in->next) {
+ list *exps, *cols;
+ sql_idx *i = in->data;
+ fcmp cmp = (fcmp)&sql_column_kc_cmp;
+
+ /* join indices are only interesting for joins */
+ if (i->type == join_idx || list_length(i->columns) <= 1)
+ continue;
+ /* based on the index type, find qualifying exps */
+ exps = list_select(r->exps, i, (fcmp) &index_exp,
(fdup)exp_dup);
+ /* now we obtain the columns, move into
sql_column_kc_cmp! */
+ cols = list_map(exps, b, (fmap) &selectexp_col);
+
+ /* Match the index columns with the expression columns.
+ TODO, Allow partial matches ! */
+ if (list_match(cols, i->columns, cmp) == 0) {
+ /* re-order exps in index order */
+ node *n, *m;
+ list *es = list_create((fdestroy)&exp_destroy);
+
+ for(n = i->columns->h; n; n = n->next) {
+ int i = 0;
+ for(m = cols->h; m; m = m->next, i++) {
+ if (cmp(m->data, n->data) == 0){
+ sql_exp *e =
list_fetch(exps, i);
+ list_append(es,
exp_dup(e) );
+ break;
+ }
+ }
+ }
+ assert(list_length(exps) == list_length(es));
+ /* fix the destroy function */
+ cols->destroy = NULL;
+ list_destroy(cols);
+ list_destroy(exps);
+ *EXPS = es;
+ return i;
+ }
+ cols->destroy = NULL;
+ list_destroy(cols);
+ list_destroy(exps);
+ }
+ }
+ return NULL;
+}
+
+static sql_rel *
+rel_select_use_index(mvc *sql, sql_rel *rel)
+{
+ (void)sql;
+ if (rel->op == op_select) {
+ list *exps = NULL;
+ sql_idx *i = find_index(rel, &exps);
+
+#ifdef DEBUG
+ rel_print(sql, rel, 0);
+#endif
+ if (i) {
+ prop *p;
+ node *n;
+
+ /* add PROP_HASHIDX to all column exps */
+ for( n = exps->h; n; n = n->next) {
+ sql_exp *e = n->data;
+
+ e->p = p = prop_create(PROP_HASHIDX, e->p);
+ p->value = i;
+ }
+ /* add the remaining exps to the new exp list */
+ if (list_length(rel->exps) < list_length(exps)) {
+ for( n = rel->exps->h; n; n = n->next) {
+ sql_exp *e = n->data;
+ if (!list_find(exps, e, (fcmp)&exp_cmp))
+ list_append(exps, exp_dup(e));
+ }
+ }
+ list_destroy(rel->exps);
+ rel->exps = exps;
+ }
+ }
+ return rel;
+}
+
static sql_rel *
rewrite(mvc *sql, sql_rel *rel, rewrite_fptr rewriter)
{
@@ -1088,6 +1239,8 @@
if (gp.cnt[op_select])
rel = rewrite(sql, rel, &rel_push_select_down);
*/
+ if (gp.cnt[op_select])
+ rel = rewrite(sql, rel, &rel_select_use_index);
if (gp.cnt[op_join])
rel = rewrite(sql, rel, &rel_join_order);
U sql_statement.mx
Index: sql_statement.mx
===================================================================
RCS file: /cvsroot/monetdb/sql/src/server/sql_statement.mx,v
retrieving revision 1.164
retrieving revision 1.165
diff -u -d -r1.164 -r1.165
--- sql_statement.mx 24 Apr 2008 20:43:32 -0000 1.164
+++ sql_statement.mx 14 May 2008 20:18:27 -0000 1.165
@@ -2282,7 +2282,7 @@
case st_bat:
return &st->op1.cval->type;
case st_idxbat:
- if (st->op1.idxval->type == unique) {
+ if (st->op1.idxval->type == hash_idx) {
return sql_bind_localtype("int");
} else if (st->op1.idxval->type == join_idx) {
return sql_bind_localtype("oid");
U rel_exp.mx
Index: rel_exp.mx
===================================================================
RCS file: /cvsroot/monetdb/sql/src/server/rel_exp.mx,v
retrieving revision 1.10
retrieving revision 1.11
diff -u -d -r1.10 -r1.11
--- rel_exp.mx 9 Apr 2008 09:56:23 -0000 1.10
+++ rel_exp.mx 14 May 2008 20:18:23 -0000 1.11
@@ -72,6 +72,7 @@
extern sql_exp *rel_find_exp( sql_rel *rel, sql_exp *e);
+extern int exp_cmp( sql_exp *e1, sql_exp *e2);
extern int exp_is_join(sql_exp *e);
extern int exp_is_eqjoin(sql_exp *e);
extern int exp_is_correlation(sql_exp *e, sql_rel *r );
@@ -410,6 +411,12 @@
return NULL;
}
+int
+exp_cmp( sql_exp *e1, sql_exp *e2)
+{
+ return (e1 == e2)?0:-1;
+}
+
int
exp_is_join(sql_exp *e)
{
U rel_prop.mx
Index: rel_prop.mx
===================================================================
RCS file: /cvsroot/monetdb/sql/src/server/rel_prop.mx,v
retrieving revision 1.1
retrieving revision 1.2
diff -u -d -r1.1 -r1.2
--- rel_prop.mx 27 Jan 2008 15:40:08 -0000 1.1
+++ rel_prop.mx 14 May 2008 20:18:24 -0000 1.2
@@ -31,6 +31,8 @@
#define PROP_COUNT 0
#define PROP_JOINIDX 1 /* could use join idx */
+#define PROP_HASHIDX 2 /* could use hash idx */
+#define PROP_SORTIDX 3 /* could use sortedness */
#define prop_list() list_create((fdestroy)&prop_destroy)
U sql_scan.mx
Index: sql_scan.mx
===================================================================
RCS file: /cvsroot/monetdb/sql/src/server/sql_scan.mx,v
retrieving revision 1.134
retrieving revision 1.135
diff -u -d -r1.134 -r1.135
--- sql_scan.mx 1 Apr 2008 17:04:59 -0000 1.134
+++ sql_scan.mx 14 May 2008 20:18:27 -0000 1.135
@@ -234,6 +234,7 @@
keywords_insert("INTERSECT", INTERSECT);
keywords_insert("CORRESPONDING", CORRESPONDING);
keywords_insert("UNIQUE", UNIQUE);
+ keywords_insert("CLUSTERED", CLUSTERED);
keywords_insert("USING", USING);
keywords_insert("VALUES", VALUES);
keywords_insert("VIEW", VIEW);
U sql_parser.mx
Index: sql_parser.mx
===================================================================
RCS file: /cvsroot/monetdb/sql/src/server/sql_parser.mx,v
retrieving revision 1.281
retrieving revision 1.282
diff -u -d -r1.281 -r1.282
--- sql_parser.mx 9 May 2008 14:00:54 -0000 1.281
+++ sql_parser.mx 14 May 2008 20:18:25 -0000 1.282
@@ -681,7 +681,7 @@
%token<sval> PUBLIC REFERENCES SCHEMA SET AUTO_COMMIT
%token RETURN
-%token ALTER ADD TABLE COLUMN TO UNIQUE VALUES VIEW WHERE WITH
+%token ALTER ADD TABLE COLUMN TO UNIQUE CLUSTERED VALUES VIEW WHERE WITH
%token<sval> sqlDATE TIME TIMESTAMP INTERVAL
%token YEAR MONTH DAY HOUR MINUTE SECOND ZONE
%token LIMIT OFFSET
@@ -1336,8 +1336,9 @@
;
opt_index_type:
- UNIQUE { $$ = unique; }
- | /* empty */ { $$ = unique; }
+ UNIQUE { $$ = hash_idx; }
+ | CLUSTERED { $$ = clustered; }
+ | /* empty */ { $$ = hash_idx; }
;
/* sql-server def
-------------------------------------------------------------------------
This SF.net email is sponsored by: Microsoft
Defy all challenges. Microsoft(R) Visual Studio 2008.
http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
_______________________________________________
Monetdb-sql-checkins mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/monetdb-sql-checkins
