Home » Discuss » Forums » Library Systems » Talis Library System

Rebuilding Indexes

Submitted by markhughes on Wed, 2007-02-07 13:41.

After checking the check_talindexes.sc script on the TDN , I notice that we have quite a few indexes missing, namely: AUTHORITY_NOTE_INDX, BOOKING_INDB, BORROWER_CREDIT_MIS1, ITEM_MIS3, ITEM_UPDATE_INDB, ITEM_UPDATE_INDC, RESERVATION_MIS1, WORKS_MIS1.

I'm pretty sure that item_update.index should rebuild the missing ITEM_UPDATE_INDB and ITEM_UPDATE_INDC and I have tried running the script, but the script just returns to the prompt after a few seconds. If I run it on the MIS server, however, it seems to work OK.

Any ideas why it won't rebuid the indexes?

The versions are the script are the same on both MIS and main servers as the TALIS SOLUTIONS person checked this for me. He is out of ideas and suggested I tried posting here...

Also, what Index scripts would I try, to rebuild to sort the other missing indexes?

Thanks,
Mark

‹ Bor_type_updt.pl itm_rotate report ›
»

Building indexes

Submitted by Brian Crampton on Wed, 2007-02-07 16:34.

The indexes with MIS in will be built with the scripts with _bj in e.g. ITEM_MIS3 will be built with the script item_bj.index. This script is in /usr/opt/blcmp/talis/database/index as all the index scripts are.

Each script refers to a table name but each script will rebuild all the indexes not just the missing one. This may not have a big impact on most tables but with tables like ITEM and LOAN it takes a while to build each index. You also have to be careful about when you run the scripts as Alto performance could be affected.

In terms of the problem with the ITEM_UPDATE index problem it is difficult to say remotely but you could try running the index build line directly in SQL to see if that generates an error that may not get sent to screen.

Start an isql session and use the prod_talis database then enter

create index ITEM_UPDATE_INDB on ITEM_UPDATE(NEW_WORK_ID)

This may generate an error that will provide a route to diagnose the problem.

Thanks

Brian Crampton
Developer, Talis

»

Thanks Brian,I have run the

Submitted by markhughes on Thu, 2007-02-08 10:24.

Thanks Brian,

I have run the command manually as you suggested and that worked fine, with no errors. Strange...

I'll try examining all the relevant scripts and running the commands manually into SQL and see how it goes.

Another question though... Once these indexes have been built, do they maintain themselves, or do they periodically need manually rebuilding?

Cheers
Mark

http://www.wirral-libraries.net/
http://www.stembystem.co.uk/

»

Indexes

Submitted by Brian Crampton on Thu, 2007-02-08 11:22.

Have you found any problems with the other scripts?

As the tables are altered over time there will be some degradation although whether the degradation will be noticable and over what time period depends on many criteria.

A script called update_stats can be run that refreshes the indexes.

However rebuilding the indexes is the best way of optimising an index but those tables that may show the better performance improvement are also the larger tables so rebuilding the indexes will take longer so there is a trade off. I know some customers have found benefits in rebuilding indexes on certains tables.

It is difficult to give generic advice but if you are able to rebuild indexes at least on certain critical tables e.g. LOAN, ITEM, WORK within acceptable downtime then you are not going to do any harm but unless there is a noticable performance issue causing a problem then it may not be worth the knock on effect with downtime.

Thanks

Brian Crampton
Developer, Talis

»