You may have already wondered about whether a specific index is sensible or if you can save space by dropping it.
For this MaxDB provides statistic information in the INDEXES view.
The column INDEXUSED provides the information if the index had been used at all yet and, starting with MaxDB 7.6 it also shows how often an Index has been used.
Therefore, by selecting the statistics from this view, one might want to determine which indexes to drop and which to keep.
Unfortunately things are not that easy.
Let's try out, what leads to an increase of the counter and what not.
sqlcli -d db760 -u lars,lars
Welcome to the MaxDB interactive terminal.
Type: \h for help with commands
\q to quit
sqlcli db760=> Create table childdata (ckey int default serial primary key, col2 varchar(20) )
0 rows affected (2718 usec)
sqlcli db760=> create unique index icd on childdata (col2, ckey)
0 rows affected (1711 usec)
sqlcli db760=> select index_used from indexes where indexname='ICD'
| INDEX_USED |
| ---------------- |
| 0 |
1 row selected (68.202 msec)
|
Ok, I've created a small table with an index on the non-key column.
Let's enter some data and check what happens to the usage_counter.
Unique Constraint checking
sqlcli db760=> insert into childdata values (default, 'X')
1 row affected (2373 usec)
sqlcli db760=> insert into childdata values (default, 'X')
* 250: POS(1) Duplicate secondary key:ICD SQLSTATE: 23000
sqlcli db760=> select index_used from indexes where indexname='ICD'
| INDEX_USED |
| ---------------- |
| 0 |
1 row selected (19.017 msec)
|
So, for determine the duplicate key of the 'ICD'-Index this index has to be used.
But, the INDEX_USED counter was not increased.
SELECT, INSERT and UPDATE
What happens If we perform some SELECT, INSERT and UPDATE on this table?
Note: to make sure that the data is access via the index, I make use of the INDEXACCESS hint here.
explain select /*+indexaccess*/ * from childdata where col2='X'
OWNER TABLENAME COLUMN_OR_INDEX STRATEGY PAGECOUNT
LARS CHILDDATA ICD2 RANGE CONDITION FOR INDEX 1
ONLY INDEX ACCESSED
COL2 (USED INDEX COLUMN)
RESULT IS NOT COPIED , COSTVALUE IS 1
QUERYREWRITE - APPLIED RULES:
DistinctPullUp 1
|
So the index will be used, so let's start the tests:
sqlcli db760=> select /*+indexaccess*/ * from childdata where col2='X'
| CKEY | COL2 |
| -------------- | -------------------- |
| 1 | X |
1 row selected (493 usec)
sqlcli db760=> select columnname, columnno, index_used from indexcolumns where indexname='ICD2'
| | COLUMNNAME | COLUMNNO | INDEX_USED |
| -------------------------------- | -------------- | ---------------- |
| COL2 | 1 | 1 |
| CKEY | 2 | 1 |
2 rows selected (18.832 msec)
|
As we see a SELECT increases the INDEX_USED counter.
You may have noticed that I've used the second view, that contains the INDEX_USED counter, INDEXCOLUMNS here.
One thing to be aware of for this view is, that although it displays one row for all columns, the INDEX_USED counter does *not* distinguish whether a predicate, that led to the index_usage, was applied to a specific column.
This means, that the INDEX_USED value will always be the same for all columns in an index and that you cannot tell, what column is more often used in an index and which one less often.
Now, what about UPDATE and DELETE?
sqlcli db760=> \a
Autocommit mode switched OFF
sqlcli db760=>update /*+indexaccess*/ childdata set col2 ='P' where col2='X'
1 rows affected (00.039 sec)
sqlcli db760=>rollback
0 rows affected (881 usec)
sqlcli db760=> select columnname, columnno, index_used from indexcolumns where indexname='ICD2'
| COLUMNNAME | COLUMNNO | INDEX_USED |
| -------------------------------- | -------------- | ---------------- |
| COL2 | 1 | 2 |
| CKEY | 2 | 2 |
2 rows selected (18.832 msec)
sqlcli db760=>delete /*+indexaccess*/ from childdata set col2 ='P' where col2='X'
1 rows affected (00.039 sec)
sqlcli db760=>rollback
0 rows affected (881 usec)
sqlcli db760=> select columnname, columnno, index_used from indexcolumns where indexname='ICD2'
| COLUMNNAME | COLUMNNO | INDEX_USED |
| -------------------------------- | -------------- | ---------------- |
| COL2 | 1 | 3 |
| CKEY | 2 | 3 |
2 rows selected (18.832 msec)
|
Two things are notable here:
- Index accesses for UPDATE and DELETE statements are correctly recorded.
That is quite important as there are may be indexes that have been made just for this. - The update of the INDEX_USED counter happens *outside* the application transaction and is *not affected by rollback or commit*.
That makes sense as well. Even if the transaction needs to be rolled back, the index has been used so the counter value needs to be kept.
Foreign Key constraints
Ok, what else can we do with indexes?
In NON-SAP databases there are (hopefully) foreign key constraints.
These always need to be backed up by primary keys or indexes on the referenced table so that a quick lookup is possible for MaxDB.
Will these index usages be recorded as well?
Let's check that!
First, I create a table with a foreign key to our table.
sqlcli db760=> \a
Autocommit mode switched ON
sqlcli db760=> \mu
Multiline mode switched ON
sqlcli db760=> create table parentdata (ckey int default serial primary key,
> colp2 varchar (20) , FOREIGN KEY colp2_in_childdata (colp2) REFERENCES lars.childdata (col2) ON DELETE RESTRICT)
> ;
0 rows affected (3986 usec)
sqlcli db760=> \mu
Multiline mode switched OFF
|
Now, let's use the FK constraint...
sqlcli db760=> insert into parentdata values (default, 'X')
1 row affected (19.719 msec)sqlcli db760=> select index_used from indexes where indexname='ICD'
| INDEX_USED |
| ---------------- |
| 3 |
1 row selected (18.877 msec)
sqlcli db760=> insert into parentdata values (default, 'Y')
* 350: POS(1) Referential integrity violated:COLP2_IN_CHILDDATA,LARS,PARENTDATA SQLSTATE: 23000
sqlcli db760=> select index_used from indexes where indexname='ICD'
| INDEX_USED |
| ---------------- |
| 3 |
1 row selected (18.619 msec)
|
Since for both INSERTS the index has to be used (and the database explicitly told us that it has been done at least for the case where the FK constraint was violated), the INDEX_USED counter should have been increased.
This was not done.
Therefore, if an index is never used for SELECT, UPDATE, DELETE one may come to the conclusion that the index can safely be dropped, although the index is necessary for the FK constraint.
Drop the unused index
Let's check what happens, if we try to drop the index:
sqlcli db760=> drop index icd
* -7066: POS(1) Unique index referenced by foreign key not allowed SQLSTATE: I7066
|
Luckily, MaxDB prevents us from making such nonsense actions.
sqlcli db760=> alter table parentdata drop foreign key COLP2_IN_CHILDDATA
0 rows affected (19.277 msec)
sqlcli db760=> drop index icd
0 rows affected (22.821 msec)
|
Ok, that's about the INDEX_USED column.
Best regards,
Lars
