Maximo: ASSET table has duplicate indexes?

Question

I have been investigating the indexes on the asset table in Maximo 7.6.1.1.

I noticed that there are a couple of indexes that appear to be duplicated:

ASSET_NDX1
ASSET_NDX14

select  
    *
from    
    all_indexes
where   
    table_name = 'ASSET'
    and index_name in ('ASSET_NDX1','ASSET_NDX14')

Are the indexes duplicates?

And if not, what is each used for?

Tony Andrews · Accepted Answer · 2019-10-02T15:25:07.877

3

They are not the same, so they could potentially both be useful for different queries:

This could use ASSET_NDX14 (and not touch the table):

select siteid 
from asset
where assetnum = :a;

This could use ASSET_NDX1 (and not touch the table):

select assetnum
from asset
where siteid = :s;

See Oracle Database Concepts Guide for more details about composite indexes.

edited Oct 02 '19 at 15:25

answered Oct 02 '19 at 15:06

Tony Andrews

129,880
21
220
259

On a side note: I wasn't familiar with the `:a` and the `:s` in your query. For my records, they are [bind variables](https://stackoverflow.com/a/2181662/10936066). – User1974 Oct 02 '19 at 15:11
Is the only difference between the two indexes *the order in which the fields are listed?* `siteid`,`assetnum` vs. `assetnum`, `siteid`. I think I need this answer dumbed down a bit into layman's terms. – User1974 Oct 02 '19 at 15:14
Yes that's right. `assetnum` is on the "leading edge" (i.e. is first) of ASSET_NDX14, and so can be used for quick look-ups by `assetnum` value but not by `siteid` value. And vice versa. I got them the wrong way round in my answer, I will fix it. – Tony Andrews Oct 02 '19 at 15:24
Ok, thanks. This paragraph from the link you provided helped: *"Composite indexes can speed retrieval of data for `SELECT` statements in which the `WHERE` clause references all or the leading portion of the columns in the composite index. Therefore, the order of the columns used in the definition is important. In general, the most commonly accessed columns go first."* – User1974 Oct 02 '19 at 15:54

Sun · Answer 2 · 2019-10-03T19:35:55.600

The following query you run for the Maximo database will give you the indexes used by asset:

select mi.tbname, mi.name, mk.colseq, mk.colname, mk.ordering  from maxsysindexes mi
join maxsyskeys mk on mk.ixname = mi.name
where mi.tbname = 'ASSET'
order by mi.tbname, mi.name, mk.colseq;

Oracle database equivalent would be:

SELECT table_name, index_name, column_position, column_name, descend 
FROM all_ind_columns 
WHERE table_name ='ASSET'
ORDER BY table_name, index_name, column_position

You can also view the same information in Maximo: * go to System Configuration > Database Configuration * Look up ASSET object * look under Indexes tab

You can compare the two indexes you are asking about. Although I have 7.6.1.1 installed here, index names can vary depending on how much custom configuration you've done.

Maximo: ASSET table has duplicate indexes?

2 Answers2