Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Index on status field?

Re: Index on status field?

From: Martic Zoran <zoran_martic_at_yahoo.com>
Date: Sat, 9 Apr 2005 23:45:51 -0700 (PDT)
Message-ID: <20050410064551.5818.qmail@web52609.mail.yahoo.com>

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 time.
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 it.
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 system.

Regards,
Zoran                 



Do you Yahoo!?
Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/
--
http://www.freelists.org/webpage/oracle-l
Received on Sun Apr 10 2005 - 02:49:45 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US