RE: Question on IOT with ascending primary key with a twist
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: "kibeha@xxxxxxxxx" <kibeha@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 16 Jan 2014 11:38:13 +0000
- Would that make this IOT inefficient?
Not for accessing a single row
And if your "1 to 10" per minute updates is cluster then you might get a (tiny)
benefit from avoiding "buffer busy waits"
- Bad use of space when always inserting "in the middle"?
At your row size 50/50 split give about 150 rows per block - so your next
question is pertinent
- Is it worth worrying about with just a few hundred inserts a day?
So you get 6 - 8 blocks per day (est.) instead of 3 - 4: not much of a threat
in space
How many weeks before you might care about N blocks out of M being half full
instead of full ?
- Or is it even worth using an IOT for this rather than just an ordinary table?
Might as well use an IOT - it will probably be one less buffer visit and
slightly less contention on insert
(If it's a heap table you still have the same contention/split concerns for the
PK index as for the IOT).
What's the plan for when you wrap around to zero again ?
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Kim Berg Hansen [kibeha@xxxxxxxxx]
Sent: 16 January 2014 09:59
To: oracle-l@xxxxxxxxxxxxx
Subject: Question on IOT with ascending primary key with a twist
Hi, all
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@xxxxxxxxx<mailto:kibeha@xxxxxxxxx>
@kibeha
Other related posts:
