RE: Index on status field?
- From: Martic Zoran <zoran_martic@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 7 Apr 2005 08:34:15 -0700 (PDT)
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.
Regards,
Zoran
--- "Mark W. Farnham" <mwf@xxxxxxxx> wrote:
> Presumably the meaning of 0 is "I'm a new record and
> I need some work done
> on me." In your scheme there is only one step in the
> workflow that results
> in NULL, meaning "I've been handled" so disappear
> from the index to keep the
> index reasonably small and usuable as a moderately
> quick look-up (a few
> millions of rows of interest as opposed to hundreds
> of millions of rows of
> transactions that have been processed.
>
> I have never experimented with precisely the
> situation you inquire about,
> because there has always been an interest in
> first-in, first-out (FIFO)
> processing in the systems I have worked on. This
> usually means the status is
> either a monotonically increasing internal id or a
> date. (Either of which is
> very nice for answering the question "Do I have
> anything that's been in
> there too long?"
>
> Do you have parallel insert jobs? I'm thinking for a
> single value status for
> all new records that is an excellent recipe for
> generating block-leaf-split
> whatever the latest words they use to describe that
> problem is, problem. How
> Oracle deposits the new rowid references into the
> index probably varies with
> the segment allocation type (ASSM or freelists) as
> well as whether you're
> OPS, RAC, or GRAC. Probably they don't want to move
> anything, so I'd guess
> you'd find the first one too big and stick it in
> just before that. In your
> case, that is the end. Of course with parallel
> inserts (of which kind?
> Pumping the new ones in direct?) Anyway, seems
> pretty easy to test.
>
> But why not use multiple values and partition your
> index? Maybe index size
> is a concern? Usually the value of preserving the
> ability to process in the
> order received and be able to tell if any old ones
> got "stuck" exceeds the
> cost of the extra size. Since you're punching them
> to NULL when done, it'll
> give you a little headroom for dynamic row growth at
> a lower percent free
> without causing row migration in the table, anyway.
>
> I'd probably be looking at thread#,date_arrived, and
> partition the index by
> thread#. Then I'd process the rows with the same
> number processors as I had
> insert threads and all the collisions disappear.
>
> Now Oracle SHOULD be pretty good at garbage
> collection for cases like this,
> so the size of your index should not get much bigger
> than the space required
> to hold the peak number of key values and rowids
> "not yet handled." Over the
> years various releases I've seen various problems
> where Oracle's garbage
> collection is less than perfect. So I would indeed
> be monitoring these
> indexes for growth above the peak "not yet handled"
> size.
>
> Good luck, let me know how it works out for you?
>
__________________________________
Do you Yahoo!?
Yahoo! Personals - Better first dates. More second dates.
http://personals.yahoo.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
