Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Location
    Near D.C.
    Posts
    56

    Unanswered: Oracle-like binary indexes

    Is there an index that is the equivalent of how Oracle defines a binary index?
    Used for a low-cardinality like M, F....and such.

    The binary indexes noted in DB2 are for BLOBs or other binary columns...a horse of a different color.

    I'm likely just getting the naming wrong, since vendor marketing likes making so many the same things "special" to each platform or version.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You probably meant to say "bitmap indexes", not "binary". DB2 builds bitmaps "on the fly" during query execution if the optimizer decides that it is warranted. You cannot create a bitmap index as a database object.

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    DB2 also has hash indexing which is specified with the ORGANIZE BY KEY SEQUENCE keywords in the Create Table. This is also known as a range-clustered table. Each possible key value in the defined range has a predetermined location in the physical table. The storage required for a range-clustered table must be available when the table is created, and must be sufficient to contain the number of rows in the specified range multiplied by the row size.

    Not too many DB2 DBA's use ORGANIZE BY KEY SEQUENCE, but not too many Oracle DBA's use bit map indexes either.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Oct 2003
    Location
    Near D.C.
    Posts
    56
    Binary can be used as well as bitmap...I've always switched them back and forth.
    Which is likely why I'm caught up in this Abbott & Costello routine.

    Thanks for the input, the hash indexing sounds interesting,
    however, I don't believe it is practical for this exercise.
    And now with some more specs given to me, I doubt I could utilize a bitmap since the table won't be static.

    Appreciate the answers.

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Also, I do not see any kind of an index that has a fifty-fifty split on cardinality being helpful, in the case of M, F, on any database product. I have created indexes on flag fields (Y/N), but in those cases 95+% of the table was one response or the other and we were looking for the value that was on less than 5% of the rows. For instance, Y(es) the row has been processed and we are only looking for the rows that have N(ot) been processed.
    Dave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •