ca.portal.admin

Re: Index definition

Discussion created by ca.portal.admin on Aug 5, 2009
Kay,

Usually you do not have much choice after the programs have been written,
changing the sequence or the order of the fields may affect programs and
how they access the data.

The best way to decrease the number of splits is simply to increase the IBC
for the index, if you double it you basically cut the number of splits in
half.
However, depending on the page size and the size of the SR8 that may not
always be possible.

I usually play around with the IBC and page reserve during index rebuilds.

Let's say you have an index where the IBC=100 and a page size of 11476.
When you do your rebuild you use a special schema that has an IBC=90 and a
special DMCL with a page reserve for the area=1148 (10%).
When you go back to using the original schema and original DMCL you now
have room for growing the SR8s by 10% without having to split them.

They will of course not grow that even, but you will reduce the number of
splits significantly, especially just after the index rebuild. If you have
much growth in the area, you may want to go a little higher on the page
reserve (maybe add the size of an SR8). This way when you do get a split,
initially you have a bigger chance that it can split and stay on the same
page - as far as I remember IDMS first checks if there is space on the
current page.

If you are really concerned about the performance, you should also use a
displacement, it moves the leaf-level SR8s away from the root of the index,
making room for the intermediate SR8s between the root and the leaf level.

Always remember that the maximum size of an SR8 should not be larger than
30% of the page size.
Larger SR8s increase the likelihood of deadlocks, but can improve
performance.
Smaller SR8s increase the splits and can hurt performance.

Tommy Petersen
110 Cokesbury Rd
Room 542H
Lebanon, NJ 08833

Phone:
Internal 200 - 3699
External (908) 236-3699
Fax: (908) 236-3692




""Rozeboom, Kay
[DAS]""
<KAY.ROZEBOOM@IOW To
A.GOV> IDMS-L@LISTSERV.IUASSN.COM
Sent by: IDMS cc
Public Discussion
Forum Subject
<IDMS-L@LISTSERV. Index definition
IUASSN.COM>


08/05/2009 10:28
AM


Please respond to
IDMS Public
Discussion Forum
<IDMS-L@LISTSERV.
IUASSN.COM>






We have an index defined as below:

KEY IS (
FIELD1 ASCENDING
FIELD2 ASCENDING)
DUPLICATES ARE FIRST

FIELD1 has a lot of duplicates, FIELD2 not so many. Neither field is
""naturally increasing"" such as a time stamp.

Is this index defined the best way to reduce the number of SR8 splits?
Would it better to define the key in descending order, or have duplicates
last?

Kay Rozeboom
State of Iowa
Information Technology Enterprise
Department of Administrative Services
Telephone: 515.281.6139 Fax: 515.281.6137
Email: Kay.Rozeboom@Iowa.Gov
"
IDMS 3rd-party providers forum
IDMSVENDOR-L@LISTSERV.IUASSN.COM
SMTP
IDMSVENDOR-L@LISTSERV.IUASSN.COM
IDMSVENDOR-L@LISTSERV.IUASSN.COM
SMTP








Normal

Normal
Re: Index definition
"The major issue you may face here is an explosion of orphans, not a split
issue per se.

Orphan chains happens when the insertion point is at the beginning of an
index (as with descending date indexes) or, in your case, where you may have
a lot of duplicates in a duplicates first condition.

Without going into the mechanics, these sorts of things result in creating a
cascading series of orphans, as you keep splitting blocks that already
contain orphans, pushing the block that actually points to the orphaned
record way way down the SR8 chain from the SR8 the orphan points back to.
This can cause performance issues, esp during an ERASE operation, as all the
intermediate SR8s in the orphan chain have to have their orphan counts
adjusted.

In your case, since this is a compound index, it may not be that big a deal.
Run an index report and look at your orphan counts. In any event, you would
have less exposure with a sequence that tends to insert at the END of things
already there, in your case that would be DUPLICATES LAST.

Don Casey
Principal Consultant
Run Right, LLC

Outcomes