Re: 2 million row database from hell! help a newbie in over his head.

In article <7NXg7.301$Iw2.19807_at_petpeeve.ziplink.net>, David Cressey says...

>Not my impression, at all. I've read some Kimball, and I've created some
>data marts (star schemas, actually). And I think that it goes a lot deeper
>than "index challenged".

Well, I still have database warehousing primer on my todo list, therefore, you can be very sceptical about what I write, of course. Indexing in general is about speeding data access: ideally, no matter how complex my query is, it could still be made fast by creating some redundant data structures. Very broad range of techniques fall into this definition. For example, database performance folks tried all performance tuning techniques upon my query, gave up, and finally discovered that one approach still could work: creating a materialized view (snapshot). Materialized view is a [generalised] index, therefore. Same for star schema. Why making those reduntant data visible as additional tables, then?

This simplistic view is not without problems, of course. For example, could we consider adjusency list as being normalized data, while Joe Celko's ranges as a [sophisticated] indexing technique? Clearly not, because expressing queries in adjucency list model requires some hierarhical extensions to Relational Model, while in Joe's approach Relational Model with Aggregates would suffice. Indexing, however is not supposed to change how we describe the access to the data on logical level (i.e. queries).

>Maybe I'm just dense, but I still don't get what you mean by "program your
>own indexes". Was "8.1.7" supposed to explain it to me?

http://www.oradoc.com/ora817/appdev.817/a76937/dci07id2.htm#620278

To put it bluntly, a user can write R-Tree for spatial data retrieval as an extensible index. And not only R-tree, of course. Writing a custom index which imply some C "cartridge" programming, is not for fainthearted as of today, though. I, personally, wouldn't bother checking it out until they make it java, at least. Received on Thu Aug 23 2001 - 05:13:26 CEST