How many indexes do you need?

How many indexes do you need?

High or little? If this is too much, how many actually we need?

The answer to this question is simple. Although not satisfying.

It depends.

On what?

Depends on the type of system, the size of the table, the data, the amount and type of processing.

Generally speaking, I would say that up to 5 indexes (plus PK, FK) seem reasonable. And above 10 I would wonder... Not to mention the tables, where we will find 30-40 + indexes. It looks suspicious. If we also have a performance problem, it may be worth looking at the database from the index side.

Well, but we don't learn much from just looking. We need some smart tools to do the job.

INDEX MONITORING sounds great.

This tool allows you to verify which indexes are used and which are completely useless. We need to monitor the full life of the application, both during normal daytime work and during night batch processing. Attention should also be paid to specific days on which processes may differ, i.e. end of the month, end of the day, end of the quarter, end of the year, etc. The results will be as correct as our time frame.

How it's working? I'll show you an example. I will use the default HR schema based on Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production. 

create table emp_idx_monitoring as select * from employees;

ALTER TABLE emp_idx_monitoring
ADD CONSTRAINT emp_idx_m_pk PRIMARY KEY (employee_id);

create index emp_idx_m_first_name_idx on emp_idx_monitoring  (first_name);
create index emp_idx_m_last_name_idx on emp_idx_monitoring  (last_name);
create index emp_idx_m_salary_idx on emp_idx_monitoring  (salary);
create index emp_idx_m_email_idx on emp_idx_monitoring  (email);

select * from all_indexes where table_name = upper('emp_idx_monitoring');

Now index monitoring would have to be enabled. 

ALTER INDEX index_name MONITORING USAGE;
--off
ALTER INDEX index_name NOMONITORING USAGE;

The command has to be executed for each index. With several indexes, as in the example, there is no problem. However, with a normal application, we need a little bit of help.

select 'ALTER INDEX '||table_owner||'.'||index_name || ' MONITORING USAGE;' 
from all_indexes where table_name =upper('emp_idx_monitoring');

-----
ALTER INDEX HR.EMP_IDX_M_SALARY_IDX MONITORING USAGE;
ALTER INDEX HR.EMP_IDX_M_EMAIL_IDX MONITORING USAGE;
ALTER INDEX HR.EMP_IDX_M_PK MONITORING USAGE;
ALTER INDEX HR.EMP_IDX_M_FIRST_NAME_IDX MONITORING USAGE;
ALTER INDEX HR.EMP_IDX_M_LAST_NAME_IDX MONITORING USAGE;

Uruchamiamy kilka zapytań:

select * from emp_idx_monitoring where employee_id =  107; 
select * from emp_idx_monitoring where first_name = 'Diana';
select * from emp_idx_monitoring where last_name = 'Lorentz';

Now we can check the results.

If we have an older version than Oracle 12.1 we can use a view V$OBJECT_USAGE. Note - there is no OWNER column, we will only see data for the current schema. If we want to check the results from the administrator account, we must use another query:

select * from 
    (select u.name "OWNER", io.name "INDEX_NAME", t.name "TABLE_NAME",
        decode(bitand(i.flags, 65536), 0, 'NO', 'YES') "MONITORING",
        decode(bitand(ou.flags, 1), 0, 'NO', 'YES') "USED",
        ou.start_monitoring,
        ou.end_monitoring
    from sys.user$ u, sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou
    where i.obj# = ou.obj#
        and io.obj# = ou.obj#
        and t.obj# = i.bo#
        and u.user# = io.owner#)
    where used = 'YES';

In Oracle 12.1 and higher the results can be found in dba_object_usage. 

SELECT index_name,
       table_name,
       monitoring,
       used,
       start_monitoring,
       end_monitoring
FROM   dba_object_usage
WHERE  table_name =  upper('emp_idx_monitoring')
AND    owner = 'HR'
ORDER BY index_name;

Useful but heavy... Is it worth using it? If you have version 12.2 - it's not worth it! In this version and higher, we have index monitoring in the package :)

Oracle tracks indexes and records statistics every 15 minutes. Information on index usage can be found in V$INDEX_USAGE_INFO and DBA_INDEX_USAGE.

Index Usage Tracking is enabled by default with the SAMPLE option. With this option, rarely used indexes may not be included in the statistics. If we want to thoroughly examine the use of indexes, we must set the parameter to ALL.

alter session set "_iut_stat_collection_type"=ALL;
alter session set "_iut_stat_collection_type"=SAMPLE;

Changing the parameter to ALL may, however, can slow down our system, so it is worth testing it first.

In the V$INDEX_USAGE_INFO view, we have information, among other things, when data was saved LAST_FLUSH_TIME.

And then we can look at the data of specific indexes in DBA_INDEX_USAGE.

select i.owner, i.table_name, i.index_name, i.index_type, i.uniqueness, iu.last_used 
from DBA_INDEX_USAGE  iu, all_indexes i 
where iu.name(+) = i.index_name
  and table_name =upper('emp_idx_monitoring');

Of course, the index usage statistics will be as good as the timeframe we specify, no matter which tool we use. However, if we have a lot of indexes in the system and the system has performance problems - maybe removing unnecessary indexes will give the application a second breath.