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 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).


SQL Performance Tuning
SQL Performance Tuning
ISBN: 0201791692
EAN: 2147483647
Year: 2005
Pages: 125

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net