Baan Indexes Not Designed for Performance
I do not know about you, but BaanIVc with the "Level 2" Oracle driver
has some of the lamest indexes I have seen, well, since PeopleSoft anyway.
And this is not just Oracle specific, any database will have problems
with Baan's indexes, including Informix and DB2.
A couple examples from some large tables in implementations I have
seen:
Table TTPPPC231ccc has thirteen indexes. All thirteen have the fields
T$YEAR, T$PERI, T$EMNO, T$SERN. Many other fields are shared too.
What a waste.
Five of the thirteen indexes start with fields that have less than
five unique values. Only one of the thirteen indexes has a 'head'
(lead field) with more than a couple thousand values.
Thirteen must be Baan's lucky number.
The huge TTFGLD4xxccc general ledger tables seem to start every
index with a company number field, guaranteed to be a domain with
few unique values in any implementation of BAAN.
So what is the point? All the documentation I have read from
Oracle, Informix, etc., says to model a B-Tree composite index
with a head field that has a large domain with many values distributed
evenly over that domain. This allows the RDBMS optimizer to
pick the best path to the data.
Hmm... This is exactly what Baan does not do. Why trust the RDBMS
to pick the best path. What does Informix or Oracle know about databases,
not as much as the Dutch boys. With level 1 Baan did not even let the
RDBMS make the index. So Baan now lards the SQL with hints to limit
the effectiveness of the optimizer.
And Baan, don't put thirteen indexes on a table, it makes updating very slow.
Maybe if Baan concentrated on engineering instead of conservative
religious practices, apartheid and corporate shenanigans they
might sell some product. Or maybe they are all shooting smack and
smoking hash in Amsterdam. Ya sure, you betcha.