Re: Index on status field?

  • From: Martic Zoran <zoran_martic@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Sat, 9 Apr 2005 23:45:51 -0700 (PDT)

In my little bit more complex case I am recieving
around 30M records per day, while 3M of them cannot be
processed during the normal daily operations.
They are processed once per day at the end of the day.
So as the day progressing the partitioned index on
status is growing and giving me back more and more
rows to process again and again and these rows are not
possible to process.

Of course I managed to use insertdate to process only
particular records once, while using status to know
what is not processed as we talked (0/NULL).

This is simplified version of this, because the logic
behind is much more complex.

Of course as Lex pointed check constraint will not be
nice to check 0 and NULL. Also I trust the app that
only 0 and NULL will be in status field :)

Sometimes I need to go too far from standard
normalization and constraint story because 3 or 4 big
SQL's are not going to do any harm to the data anyway.

As I pointed out, even Oracle reusing index slots, I
ussually have the situation where updated indexes are
ussually much bigger then the others. But at the end
no big deal. No rebuilding them of course, waste of
Do not like rebuilding even some poeple pointing that
out as very good strategy in some cases, but it is
just an administration headache. Even it is good for
some indexes without clarification how is that going
to help SQL's running on top of them it is not worth
You need to have a crazy reason to do rebuilding where
you can justify that you will save some time on later
executed SQL's, or space and memory are issues on the


Do you Yahoo!? 
Yahoo! Small Business - Try our new resources site!

Other related posts: