Question on IOT with ascending primary key with a twist

I have a table with our product database - a bit over half a million rows and an integer pseudo primary key populated ascending by sequence. This is replicated to another database by a simple read-only refresh fast materialized view.

Now on the other database I need to supplement this materialized view by two calculated integer columns for "inventory status" (they can be 0, 1 or 2). The values will be calculated based on a function call and will change and be recalculated relatively often (something like every minute about 1 to 10 rows will be updated.)

I plan to make a small table contain the product primary key and the two calculated columns - three integer columns all in all and one of them being the PK. Then I'll make a view joining the product mview to this table on the primary key, and the application will then select from the view rather than directly from the mview.

I was thinking I might get the best performance by making that small table an IOT - half a million rows each with three integer values.

Deletes will be extremely rare (a row a month or so.) Updates will just change the values of the two status columns and shouldn't need to move things around in the IOT. Inserts will happen when new products are created (say usually less than few hundreds a day.)

Now the twist is, that the product database primary key on the source database is hampered by the source application only being able to handle signed 32-bit integers. And the primary key is populated from a common sequence used for pseudo keys for all tables (not able to change that :-(

So over the years this common pseudo key sequence has been going from 1 to 2147483647. When we reached that a few years ago, we started the sequence at -2147483648 and is now going upward toward -1.

If I make this IOT, it will start life with about half the rows with negative PK and half rows positive PK. Any insert will have a PK between the highest negative PK and -1, so it will be ascending, but always "in the middle" of the IOT.

The questions are now:

• Would that make this IOT inefficient?
• Bad use of space when always inserting "in the middle"?
• Is it worth worrying about with just a few hundred inserts a day?
• Or is it even worth using an IOT for this rather than just an ordinary table?

Thanks in advance for any hints on the issue ;-)

Regards

Kim Berg Hansen

http://dspsd.blogspot.com
kibeha_at_gmail.com
_at_kibeha

--
http://www.freelists.org/webpage/oracle-l