Professional Web Applications Themes

Finding null values in a large table - Oracle Server

I have a fairly large table (millions of rows) and I would like to be able to find rows that have a null value in a particular column. I know that regular indexes don't contain null values and thus an index on this particular column probably wouldn't help. I believe that bitmap indexes include nulls, but bitmap indexes are supposedly inefficient when the column is too selective. Are there other solutions? -Tim...

  1. #1

    Default Finding null values in a large table

    I have a fairly large table (millions of rows) and I would like to be
    able to find rows that have a null value in a particular column. I
    know that regular indexes don't contain null values and thus an index
    on this particular column probably wouldn't help. I believe that
    bitmap indexes include nulls, but bitmap indexes are supposedly
    inefficient when the column is too selective. Are there other
    solutions?

    -Tim
    Tim Dry Guest

  2. #2

    Default Re: Finding null values in a large table

    Tim Dry wrote:
    > I have a fairly large table (millions of rows) and I would like to be
    > able to find rows that have a null value in a particular column. I
    > know that regular indexes don't contain null values and thus an index
    > on this particular column probably wouldn't help. I believe that
    > bitmap indexes include nulls, but bitmap indexes are supposedly
    > inefficient when the column is too selective. Are there other
    > solutions?
    >
    > -Tim
    Is this a one-time only or an ongoing requirement?

    Dan Morgan

    damorgan Guest

  3. #3

    Default Re: Finding null values in a large table


    "Tim Dry" <tldrycharter.net> wrote in message
    news:fad761ad.0212051545.14b89cc7posting.google.c om...
    > I have a fairly large table (millions of rows) and I would like to be
    > able to find rows that have a null value in a particular column. I
    > know that regular indexes don't contain null values and thus an index
    > on this particular column probably wouldn't help. I believe that
    > bitmap indexes include nulls, but bitmap indexes are supposedly
    > inefficient when the column is too selective. Are there other
    > solutions?
    >
    > -Tim
    Tim,

    Not a lot to go on here. If you're using 8i or above, a function based index
    might be the answer, assuming this a regular requirement.
    For a one off, I'd bear the pain - a table scan shouldn't be more than (say)
    30 seconds, surely? How many millions of rows?

    Personally (this may well be blind prejudice, but there has been a problem
    or three) I am inclined to avoid bitmap indexes.

    Regards,
    Paul



    Paul Brewer Guest

  4. #4

    Default Re: Finding null values in a large table

    Tim Dry wrote:
    > I have a fairly large table (millions of rows)
    Under 10 million? That would be called a smallish table by some. :-)
    > and I would like to be able to find rows that have a null value in a
    > particular column.
    <snipped>

    I will leave the index and null discussion for someone else to get into. :-)
    > Are there other solutions?
    Yep. For a quick solution to find those "problem" rows. PQ. Something like
    this:

    SELECT
    /*+ FULL(t) PARALLEL(t,10) */
    t.*
    FROM my_big_table t
    WHERE t.some_column IS NULL



    --
    Billy
    Billy Verreynne Guest

  5. #5

    Default Re: Finding null values in a large table

    damorgan <damorganexesolutions.com> wrote in message news:<3DEFEDF0.72B28A23exesolutions.com>...
    > Tim Dry wrote:
    >
    > > I have a fairly large table (millions of rows) and I would like to be
    > > able to find rows that have a null value in a particular column. I
    > > know that regular indexes don't contain null values and thus an index
    > > on this particular column probably wouldn't help. I believe that
    > > bitmap indexes include nulls, but bitmap indexes are supposedly
    > > inefficient when the column is too selective. Are there other
    > > solutions?
    > >
    > > -Tim
    >
    > Is this a one-time only or an ongoing requirement?
    >
    > Dan Morgan
    Unfortunately it is ongoing. Very ongoing.

    -Tim
    Tim Dry Guest

  6. #6

    Default Re: Finding null values in a large table

    [email]tldrycharter.net[/email] (Tim Dry) wrote:
    > damorgan <damorganexesolutions.com> wrote in message
    > news:<3DEFEDF0.72B28A23exesolutions.com>...
    > > Tim Dry wrote:
    > >
    > > > I have a fairly large table (millions of rows) and I would like to be
    > > > able to find rows that have a null value in a particular column. I
    > > > know that regular indexes don't contain null values and thus an index
    > > > on this particular column probably wouldn't help. I believe that
    > > > bitmap indexes include nulls, but bitmap indexes are supposedly
    > > > inefficient when the column is too selective. Are there other
    > > > solutions?
    > > >
    > > > -Tim
    > >
    > > Is this a one-time only or an ongoing requirement?
    > >
    > > Dan Morgan
    >
    > Unfortunately it is ongoing. Very ongoing.
    I think you can find these rows rapidly if you build the index backed with
    a not null column. I.e. create index adfljd on lkjoi (desired_col,
    notnullable_col). It would behoove you to choose the smallest not null
    column on the table, to avoid excessively large storage requirements. You
    could even make a dummy column (say, dummy char(1) default 'x' not null) if
    there are no other suitable columns to use.

    Xho

    --
    -------------------- [url]http://NewsReader.Com/[/url] --------------------
    Usenet Newsgroup Service New Rate! $9.95/Month 50GB
    ctcgag@hotmail.com Guest

Similar Threads

  1. Finding the most recent record in a table with otherwiseidentical field values
    By kbergstrom in forum Coldfusion Database Access
    Replies: 7
    Last Post: September 22nd, 08:55 PM
  2. #26288 [Opn]: Segmentation Fault while getting NULL values from a row of a table
    By kiranhariharan at in dot ibm dot com in forum PHP Development
    Replies: 0
    Last Post: November 18th, 02:11 PM
  3. Replies: 0
    Last Post: November 17th, 07:24 PM
  4. #26288 [NEW]: Segmentation Fault while getting NULL values from a row of a table(PHP v 4.2.2)
    By kiranhariharan at in dot ibm dot com in forum PHP Development
    Replies: 0
    Last Post: November 17th, 02:46 PM
  5. Null Values in a table not properly selected
    By Jacob in forum ASP Database
    Replies: 1
    Last Post: July 5th, 06:35 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