Hello to the Blogospere!
Thanks for “tuning in” again to review more of my blog.
As promised last week, I would try to post on a somewhat regular basis, so I am delighted to bring you another installment.
This week, I’d like to talk to you about database indexes.
I recently had an issue where I had to update a table containing the Asset Worklog information to re-link it to the CMDB.
The CMDB common data model had been updated/changed, and we had preserved the old reconciliation_id field in OLD_RECON_ID column/field.
The Base Element table had over 250k records and the Worklog table over 750k records.
Initially I just performed the update you see in step 5, but after 20 minutes of execution I cancelled it so that I could add indexes.
Here is what I did:-
1. create table AST_WorkLog_BACK as select * from AST_WorkLog ;
2. select * from (
select CI_INSTANCE_ID from ast_worklog
where CI_INSTANCE_ID in (
select OLD_RECON_ID from bmc_core_bmc_baseelement
)
) where rownum<10 ;
3. create index ast_worklog_ci_instance_id on T794(C301218800) ;
4. create index bmc_baseelement_C700000443 on T129(C700000443) ;
5. update ast_worklog a
set CI_INSTANCE_ID = (
select RECONCILIATIONIDENTITY from bmc_core_bmc_baseelement b
where a.CI_INSTANCE_ID = B.OLD_RECON_ID and
B.DATASETID = ‘BMC.ASSET’
);
6. drop index ast_worklog_ci_instance_id ;
7. drop index bmc_baseelement_C700000443 ;
The above has
1. created a backup (around 2-5 minutes).
2. Proved that the CI_INSTANCE_ID column is the column we need to replace.
3. Created an index on the CI_INSTANCE_ID in the underlying table
4. Created an index on the OLD_RECON_ID in the underlying table
5. Performed the update (took 20 seconds with indexes in place – took over 20 minutes without completion without indexes)
6. Removed the temporary index created in step 3
7. Removed the temporary index created in step 4
8. Remove the backup table when you are happy that the update has worked correctly
This just goes to show you how important indexes can be to a database system, and how important CORRECT INDEXING can be to an AR System server.
Finally, a point to note is that if you intend to provide an AR System form with an index, it should be done at the application level (within the Admin tool or design studio) rather than at a database level. This is because the AR system will drop a database created index during the next form change, but will maintain an AR System Index.
Next time, I will talk about system baselining so that you can identify potential problems before they actually impact your production system!
I’ll be blogging off now……
Dave
