Thus far we've discussed only the index structures supported by every DBMS (or at least the majority of major DBMSs). But a few interesting or inspiring features are implemented by only some DBMSs. We think that each one is worth a heads-up.
-
DESC values. Used by: IBM.
Useful if there is an ORDER BY <column> DESC clause, but not essential. For example, Oracle allows you to specify the keyword DESC when you're creating an index, but ignores it. All Oracle indexes are purely ascending , and Oracle handles the descending situation by scanning backward, using back-pointing pointers in the leaf blocks.
-
Low-level access. Used by: Informix.
Sometimes the routines that the DBMS uses to scan or update the index are available as a C library, usually with a name like "ISAM API" or "Tree Access."
-
Function keys. Used by: Informix, Microsoft, Oracle, PostgreSQL.
It's wonderful to be able to do this:
CREATE INDEX Index1 ON Table1 (LOWER(column1))
so that:
SELECT * FROM Table1 WHERE LOWER(column1) = 'x'
will blaze. But don't try it until you know what "nondeterministic" means; see Chapter 11, "Stored Procedures."
-
Reverse keys. Used by: IBM, Oracle.
QuestionWhy would you want to store CHUMLEY as YELMUHC , thus putting the key in the Y s rather than the C s? AnswerIf the latter part of the key value is more changeable than the front part, then a reverse key will lead to greater dispersal. That reduces hot spots and makes access slightly faster.
-
Hashes. Used by: Ingres, Informix, Oracle, PostgreSQL.
Because the efficiency of hashing in certain contests against B-tree searching is extremely good, it's surprising that so few DBMSs use it except for internal purposesfor instance, when deciding which partition a row goes into.
-
Full-text indexing. Used by: Microsoft, MySQL.
Easy to use (you probably think that Google.com is simple and quick), but updates are hard (you also probably notice that adding a URL to Google takes a few weeks).