SQL Performance Tuning [Electronic resources] نسخه متنی

Other Index Variations

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 Ys rather than the Cs? 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).