Why not just dump relational?

The point of a query optimiser is to speed things up. And I'm not saying "throw everything
into a single table". I liken MV to C (and relational to Pascal). C is very loose and lets you
shoot yourself in the foot (as does MV). Pascal, done properly, is so rigid it's a horrible
straightjacket. If you can program C with the rigours of Pascal then you get the advantages of
both worlds.

Anyway, back to query optimisation. If I want to select a row based on an attribute value,
then MV or relational is irrelevant, I have to scan the entire table (or use an index, which
is effectively selecting row(s) by key from an index table). So, selecting by value is equal
in MV and relational, it's expensive.

Selecting by key is very fast in MV - as I said, accessing a record by key requires on average
1.05 table accesses. If your optimiser costs you more than 5%, throw it away! In MV also (with
a properly designed database :-), accessing the record will retrieve off the disk every bit of
information that shares the same simple primary key.

In a relational database, there is no guarantee that your data has a primary key. If you
access an attribute that can have multiple values (ie a one-to-many relationship with the
primary table), there's no guarantee that related values will be retrieved at the same time.
Etc etc. The thing is, in a properly designed MV database, a single table access will retrieve
all the information about a single instance of an entity. And a relational optimiser really
scores when retrieving information about a single instance of an entity from across multiple
tables. Problem is, it's hard to score against an adversary that *consistently* gets it right
"first time every time". If you're reading ONE record from a table of two hundred records, a
totally unoptimised relational database will require an average of 100 table accesses, making
a 10000% cost of your optimiser acceptable (of course, I would be very surprised to find such
a database actually exists in the real world :-)

I used INFORMATION and now use UniVerse - I think the query language is similar. It's very
English-like - indeed one variant is still called ENGLISH, I believe. Yes it takes some
getting used to. But SQL ... that's AWFUL. Yes, it's logical. But it gets horribly nested,
convoluted and heaven-knows-what. Give me the Pick query language any day :-)

What made you think Pick was hierarchical? I'll give you that - again in a properly designed
MV database - each entity definition should be hierarchical. But the links between entities
should be relational. Which gives you all the *speed* of a hierarchical database, and all the
*flexibility* of a relational database. Yes - relational is flexible - but it pays for it in
speed.

Properly designed, MV has all the speed of hierarchical. But you can choose ANY entity as the
peak of your hierarchy, giving it all the flexibility of relational. And - apart from when you
do a "find me this" query - all your data access is based on primary keys that normally hit
"first time, every time". That's blindingly fast compared to a paradigm that says "always
SELECT FROM". Yes I know relational doesn't do that under the covers, but I'd rather a model
that explicitly guarantees me instant response than one that guarantees me nothing but
typically does pretty well - I've come across enough horror stories about upgrades to the
optimisation engine "breaking" carefully crafted code and turning a racehorse into a snail.

Cheers,
Wol