On Mon, Sep 22, 2008 at 10:44:20PM -0400, Alex Scotti scratched on the wall: > > On Sep 22, 2008, at 11:18 AM, Jay A. Kreibich wrote: > >> On Mon, Sep 22, 2008 at 10:07:54AM -0400, D. Richard Hipp scratched on >> the wall: >>> I am reluctant to add to SQLite the ability to explicitly specify the >>> index for a query. I agree with Alex Scotti that the whole idea >>> seems very un-RDBMS like. >> >> Well it is outside of the Relational Model, that's for sure. >> >> Then again, the whole concept of indexes are outside of the >> Relational Model. > > this isn't exactly a good argument.
I didn't realize it was an argument. I'm simply pointing out that there is a reason it feels "un-RDBMS like" -- mostly because it is. If there was a point I was trying to make, it was that something being "un-RDBMS like" in itself doesn't make it a bad thing. After all, the very concept of indexes themselves is (from a Relational Model theory viewpoint) "un-RDBMS like," but most of us are very very comfortable with them, and accept the need to occasionally manually create them to improve practical real-world performance. IMHO, the jump from "you must manually create indexes" to "you may control the *use* of an index" is a MUCH smaller jump than the very concept of "CREATE INDEX." After all, if the RDBMS is supposed to be smart enough to figure out when using or not using an index is a good idea, shouldn't it also be smart enough to figure out if creating an index is a good idea? Or, conversely, if nobody expects a system to be smart enough to know when to create them, why do we expect it to be smart enough to know when to use them? > an index surely doesn't break the relational model in any way. Of course not. No more than the sky being blue. "Indexes are outside of the Relational Model." As long as they stay outside, they shouldn't matter to the Model. But that doesn't mean they're outside the developer experience. > it's existence or absence may or may not effect execution time, but > would never yield incorrect or different results. In theory, yes. In fact, that's what keeps them outside of the Model. Of course nearly every RDBMS has some type of "REINDEX" command which says something in itself, but that's more about the realities of implementing complex systems than anything else. > to beat a dead horse, the relational model doesn't discuss anything > physical at all. Yes. That's kind of the whole point of a "model." Especially a mathematical one. > by your line of reasoning using disks would be outside. Disks *are* outside the Relational Model. You just said so, disks being physical and all. And I'm glad they're outside. I use in-memory databases all the time. > heaven forbid a buffer pool caching your i/o. As long as it would never yield incorrect or different results, the Model doesn't care about it. Just like indexes. There are plenty of practical real-world reasons to worry about disks, however. And indexes. A very hefty part of what an RDBMS system does is outside the Relational Model. The Model gives us concepts to manipulate data, not a complete solution. It is only a kernel. Further, nobody would want an RDBMS that tried to stay as true as possible to the Relational Model. I mean, how many of us would be willing to give up ORDER BY just because it is outside the Model? Actually, ORDER BY isn't outside the model, it is in direct conflict with it. But we love it and use it anyways. > on the other hand we have here a non standard sql extension which ties > users to sqlite, and blatantly does fly in the face of the relational > model. It doesn't "blatantly" anything. Indexes are outside of the Relational Model and have nothing to do with it. They're orthogonal. From that, anything having to do with creating, using, or manipulating indexes is outside the model. Within any actual real-world RDBMS product, however, once you accept the need for indexes (on any level) then it shouldn't be hard to accept the desire to control how those indexes are used. I find it much more odd that people expect the system to magically understand when to use or not use an index perfectly in every odd case, but don't think twice about the fact that we need to manually create (or not) those indexes to start with. The status quo is this strange kind of second-guessing tuning game between the DBA and the optimizer. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
