Oracle FAQ Your Portal to the Oracle Knowledge Grid

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

RE: Index on status field?

From: Martic Zoran <>
Date: Thu, 7 Apr 2005 08:34:15 -0700 (PDT)
Message-ID: <>

Hi Mark,

Thanks for your generous answer.

Exactly, which one is new and which one is processed on a huge table.

At the moment my customer is using the date field to do the same thing. Inserting stupid day 01.01.1970 (many people will like histograms on this column) then updating to the sysdate when processed.

Was not done by me, but by my company (hard to put = sometimes), two solutions are on the table:

  1. do processing based on insert date, the update is not required then, but some control table yes
  2. do processing based on status field then do the update

The index is locally partitioned and it is now 1.5G per partition. Very big for a stupid index without purpose, so I considered the solution 2 in the game because it will require less code changes.

Of course that I am almost in 99% cases using the solution 1 where there is not a need for the update. But require much more code changes.
So up to the customer to decide which one to pick :)

Then I was thinking what is happening with the index size when you are NULLifying the field (removing it from the index) and inserting 0 again.

How Oracle is reusing the index blocks in that case?

At the end I maybe figure it out the answer myself, just no time to think (joking of course). I know that the index is populated as the natural logical key is belonging to, jsut never thought what if all values are the same, then you removing them and putting them back. Just curious.

The inserts into the table are from many processes at the same time while reading the table is 1 process for the moment.
Inserts are usually bulk inserts, on some tables are simple inserts.


Do you Yahoo!?
Yahoo! Personals - Better first dates. More second dates.
Received on Thu Apr 07 2005 - 11:38:25 CDT

Original text of this message