ca.portal.admin

Re: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 Public Discussion Forum
IDMS-L@LISTSERV.IUASSN.COM
SMTP
IDMS-L@LISTSERV.IUASSN.COM
IDMS-L@LISTSERV.IUASSN.COM
SMTP








Normal

Normal
Re: Index definition
"Well, I guess changing application program(s) involved is not possible, right? But I always try to avoid non-unique indices. Could you append the date/time to the index and have the application do a generic start on the index and select until the ""real"" keys are no longer equal to the one you want? Another more complicated option is to include a 8-byte binary field at the end of the index, have a DB procedure do a store clock instruction to get the time to generate a unique token at the end of the index (on stores), and then on find/obtain mask the token from the application program. Cool stuff if you have the time.

Dan Miley
Lockheed Martin

Outcomes