In many cases, the slowness in our applications is due to coding-related issues, design, or long-running SQL queries; indexing often helps.
What is indexing?
Imagine you search for a specific topic in a book. I know the younger generations no longer use books, but in the old days, we used the index to look up a topic and find the page number where it was located. Similar to finding a topic in a book without an index, it takes a long time for a Database to find values in tables where no index exists. Balancing too few and too many indexes is essential because every index requires disk space.
This blog post outlines strategies for indexing in the T24 Transact Core Banking platform.
Creating and testing indexes in T24 Transact
There are several options for how you can create indexes. If you use the XML Oracle drivers, there is a program create-extindex. You can run it on the jsh prompt.
In this example, we created an index on the Category column of the Account table.
create-extindex -v FBNK.ACCOUNT CATEGORY jsh
The –v (verbose) flag will display the SQL script generated.
ON FBNK_ACCOUNT x (NVL(NUMCAST(extract value(x.xmlrecord, "/row/c2')), 0) CREATE INDEX nix_FBNK_ACCOUNT_c2
If you get errors when creating an index, it could be for one of the following reasons:
The table's standard selection has not been rebuilt.
The column is defined as multi-value (only single-value columns can be indexed unless the structured storage option is used).
The column contains an ITYPE.
If the index gets created successfully but does not get used:
The selectivity of the values may be such that the majority of records are to be selected in an inquiry; in these cases, Oracle may choose to do a table scan as it is deemed more performant.
The table is tiny, and a table scan will be more performant.
The Oracle parameter index_cost_adj=1 is not set in the database.
Create an index for a single-value field in TAFJ
Get into DBTools->JQL prompt and execute the command "GETDBNAME FBNK.AA.ARRANGEMENT" to make a note on the Oracle view name of the table.
Execute the below command in SQLPlus to get the data definition language for the problematic field. "R13TAFJ" denotes the currently logged-in database user name.
Use the sample command below to create an index for a single-value field based on the definition extracted in Step 2.
Execute the below command and check whether the index has been created successfully.
Use the explain plan to see if the select uses index with the following commands.
Similarly, you can create an index for the following cases:
XML index creation on the hash partitioned table
Steps to create an index for a function-based null index
Index creation using promoted columns for multi-value fields in an Oracle database
Create an index for a single value field using the TO_NUMBER function
If your T24 Transact TAFJ core banking systems are slow, we are here to help. Contact our T24 tuning experts to find the root cause of this slowness and implement tunings to bring back the desired speed.
Keep up the great work! Happy performance engineering!