Professional Web Applications Themes

indexes on null columns - IBM DB2

What are the issues regarding creating a index on a nullable column? For example there are 3 nullable columns. I want to add one index on each of the 3 nullable columns: Table1 : 100,000 rows id bigint not null primary key empid1 bigint nullable null 9/10 of the time empid2 bigint nullable null 1/3 of the time empid3 bigint nullable null 1/3 of the time thanks...

  1. #1

    Default indexes on null columns

    What are the issues regarding creating a index on a nullable column?

    For example there are 3 nullable columns. I want to add one index on
    each of the 3 nullable columns:

    Table1 : 100,000 rows

    id bigint not null primary key
    empid1 bigint nullable null 9/10 of the time
    empid2 bigint nullable null 1/3 of the time
    empid3 bigint nullable null 1/3 of the time

    thanks
    Kris Koschik Guest

  2. #2

    Default Re: indexes on null columns

    As far as an index is concerned, a NULL is treated like any other value.
    (Yeah, I know NULL is really the absense of a value, but index manager
    failed the basic relational concepts course ;-)

    Given your stats, you won't be able to create a unique index on the columns
    since a unique undex will only allow one NULL. With a non-unique index, all
    the NULLs will be gathered into a single index key (with lots of associated
    RIDs).

    --
    __________________________________________________ ___________________
    Doug Doole
    DB2 Universal Database Development
    IBM Toronto Labs

    DB2 UDB v8.1 is available for AIX, HP, Linux, Solaris and Windows
    [url]http://www-3.ibm.com/software/data/db2/udb/v8/[/url]

    Visit the DB2 UDB and DB2 Connect Online Support site at:
    [url]http://www.ibm.com/software/data/db2/udb/winos2unix/support[/url]
    Douglas Doole Guest

  3. #3

    Default Re: indexes on null columns

    On DB2 for OS390 you can create a UNIQUE WHERE NOT NULL

    Douglas Doole wrote:
    >As far as an index is concerned, a NULL is treated like any other value.
    >(Yeah, I know NULL is really the absense of a value, but index manager
    >failed the basic relational concepts course ;-)
    >
    >Given your stats, you won't be able to create a unique index on the columns
    >since a unique undex will only allow one NULL. With a non-unique index, all
    >the NULLs will be gathered into a single index key (with lots of associated
    >RIDs).
    >
    >
    >
    Gary Rempel Guest

  4. #4

    Default Re: indexes on null columns

    "Douglas Doole" <dooleca.ibm.com> wrote in message
    news:bf6e92$2tr$1hanover.torolab.ibm.com...
    > As far as an index is concerned, a NULL is treated like any other value.
    > (Yeah, I know NULL is really the absense of a value, but index manager
    > failed the basic relational concepts course ;-)
    I'ld say it passed. It was SQL that flunked.

    ;-)

    Regards
    Paul Vernon
    Business Intelligence, IBM Global Services


    Paul Vernon Guest

Similar Threads

  1. Replies: 0
    Last Post: March 9th, 07:24 PM
  2. cfgridupdate errors when null columns inserted
    By 22 in forum Coldfusion - Getting Started
    Replies: 0
    Last Post: March 28th, 06:42 PM
  3. Replies: 0
    Last Post: July 11th, 02:10 AM
  4. Replies: 0
    Last Post: July 2nd, 06:18 AM
  5. compound primary key with null columns... is it possible?
    By Giovanni Azua in forum Oracle Server
    Replies: 4
    Last Post: December 19th, 08:36 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139