We have experienced some erratic tables lately. I have rebuilt them using Chimney Sweep, everything looks ok. Since the problems persist, I looked more closely (while totally rebuilding the tables) and noticed that the secondary indexes are huge, four times that of the .db file in some cases. What is the best way to deal with degradation through use? Delete the indexes and rebuild? Make a new table as well as new indexes? Is there any suggested schedule for rebuild events?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Secondary Index Size
- Thread starter JBirdieH
- Start date
JBirdieH,
The fact that your indexes are larger than your database tables (e.g. DB files) suggests that your indexes may be too large or that you might need to take a second look at your data model for normalization issues.
Generally, secondary indexes should be quite small, as they generally contain fewer fields than your original table.
If, for example, your table's primary key has more than just a few fields, that's a sign that you may need to create foreign keys.
RDA has a pretty good article that discusses this (see the data normalization primer at for details).
To answer your specific questions, the best way to rebuild indexes is to delete and then recreate them. And rebuilds should be rare events, not something that's regularly scheduled. If you find it necessary to rebuild that frequently, then it's possible that you have environmental factors that are corrupting tables inappropriately, e.g. users are turning off their PC's while Paradox is loaded, your network is dropping packets, and so on.
Hope this helps...
-- Lance
The fact that your indexes are larger than your database tables (e.g. DB files) suggests that your indexes may be too large or that you might need to take a second look at your data model for normalization issues.
Generally, secondary indexes should be quite small, as they generally contain fewer fields than your original table.
If, for example, your table's primary key has more than just a few fields, that's a sign that you may need to create foreign keys.
RDA has a pretty good article that discusses this (see the data normalization primer at for details).
To answer your specific questions, the best way to rebuild indexes is to delete and then recreate them. And rebuilds should be rare events, not something that's regularly scheduled. If you find it necessary to rebuild that frequently, then it's possible that you have environmental factors that are corrupting tables inappropriately, e.g. users are turning off their PC's while Paradox is loaded, your network is dropping packets, and so on.
Hope this helps...
-- Lance
NotAllThere
Technical User
JBirdieH,
In addition to the advice Lance gives, try a reIndexAll on the table.
Sample below is from V8 Help:
; reindexAllCust:
ushButton
method pushButton(var eventInfo Event)
var
tblVar Table
pdoxTbl String
endVar
pdoxTbl = "Customer.db"
tblVar.attach(pdoxTbl)
if tblVar.lock("Exclusive" then ; attempt to lock Customer.db
tblVar.reIndexAll() ; rebuild all Customer.db indexes
tblVar.unLock("Exclusive" ; unlock the table
else
msgStop("Sorry", "Can't lock " + pdoxTbl + " table."
endIf
endMethod
In addition to the advice Lance gives, try a reIndexAll on the table.
Sample below is from V8 Help:
; reindexAllCust:
ushButtonmethod pushButton(var eventInfo Event)
var
tblVar Table
pdoxTbl String
endVar
pdoxTbl = "Customer.db"
tblVar.attach(pdoxTbl)
if tblVar.lock("Exclusive"
then ; attempt to lock Customer.dbtblVar.reIndexAll() ; rebuild all Customer.db indexes
tblVar.unLock("Exclusive"
; unlock the tableelse
msgStop("Sorry", "Can't lock " + pdoxTbl + " table."
endIf
endMethod
- Thread starter
- #4
For example we have one table that is related to a master recording document table. The table in question contains the recording document #, lastname, firstname and is indexed on all three. There are three secondary indexes, one for each of the fields as a speed aid in searching. Each of these is roughly as large as the table 40,000 kb. Is a foreign key the same as a secondary key?
JBirdieH,
Actually, I misused the phrase "foreign key," which refers to the field(s) in a detail table that links back to values in the master table.
I can't think of the precisely correct term off the top of my head, but the reason your secondary indexes are so large is precisely because your primary key is so large.
Secondary indexes essentially contain two sets of values, the primary key value(s) and an additional field indicating the record's position in that particular index.
To reduce the size of your secondary index, reduce the size of your primary index. Ideally, you should have a unique integer for each record. This will keep the secondary indexes very small indeed.
This may mean adding additional code to maintain reliable index numbers. I'd take a look at RDA's AutoKey product for help with that. It's reasonably priced and very easy to use.
Hope this helps...
-- Lance
Actually, I misused the phrase "foreign key," which refers to the field(s) in a detail table that links back to values in the master table.
I can't think of the precisely correct term off the top of my head, but the reason your secondary indexes are so large is precisely because your primary key is so large.
Secondary indexes essentially contain two sets of values, the primary key value(s) and an additional field indicating the record's position in that particular index.
To reduce the size of your secondary index, reduce the size of your primary index. Ideally, you should have a unique integer for each record. This will keep the secondary indexes very small indeed.
This may mean adding additional code to maintain reliable index numbers. I'd take a look at RDA's AutoKey product for help with that. It's reasonably priced and very easy to use.
Hope this helps...
-- Lance
- Status