How know if an index is used?

Database
Enthusiast

How know if an index is used?

 
Tags (3)
8 REPLIES
Enthusiast

Re: How know if an index is used?

I would like to know how many time one index is used. In dbc.indicesv there is a column AccessCount , this colum is ok for this ?
I don't understand why have one secundary index with columns (nu_telef,cod_central) and the accesscount is : 29 for nu_telef and
3 for cod_central , and columnposition is 1 for cod_central and 2 for nu_telef
the accesscount should not be the same for both ? which is the number of access to the index, 29 or 3 ?

Thanks.
Enthusiast

Re: How know if an index is used?

If you need the access count of indexes (hash indexes or join indexes), then you are referring to the correct view.

Access of primary index and secondary index can be different.

I guess that you are referring to the count of the columns accessed. That can be fetched using 'DBC.ColumnsVX' view

PS: If the count is not getting logged, make sure you have enabled dbscontrol flag ''ObjectUseCountCollectRate'

Enthusiast

Re: How know if an index is used?

Note that the count measured is the count from the 'Optimizer execution plan'.

(Count for explain, insert explain, dump explain not accounted)

Enthusiast

Re: How know if an index is used?

Yes , I am refering to access count of indexes, but if my secundary index is the tuple (nu_phone,cod_central) and the access count is 29 for the column nu_phone and 3 for the column cod_central then , how many time is accessed the index ? 29 or 3 ?

Example (dbc.indicesv) : 

.... indexname , columname , columnposition , accesscount .....  -->

myIndex1 , nu_phone, 1 , 29    --> row1

myIndex1 , cod_central,2,3      -> row2

myIndex2 , co_region,1,960  --> row3

myIndex2 , co_state,2, null -->     row4

myIndex2, co_store,3,247 -->     row5

The question : 

how many access have the index "myIndex1" ?   29 or 3 ?  

how many acces have the index "myIndex2? 960 or 247 or null ???

why the access to column co_state in the index "myIndex2" is null ?

I understand if you access to index (all columns)  then the number of accessed to columns of index should be the same , true ?

Thaks.

Teradata Employee

Re: How know if an index is used?

Hi,

you can also use DBQLobjects for that purpose.   There is ObjectType field to filter the index rows there.    

It would be interesting to compare numbers from dbc.qrylogobjects and from dbc.indicesv for your example.

With kind regards,

Vlad.

Enthusiast

Re: How know if an index is used?

I still do not know the answer ...

Teradata Employee

Re: How know if an index is used?

Hi,

were you able to turn on the DBQLobjects and see the results?   

It would not answer the question why counters for different columns are different.  But would answer the initial question about how many times the index is used.

Also note that DBQL provides more detailed information than AccessCounts.  You would know what users and queries are using that index.

Regards,

Vlad.

Teradata Employee

Re: How know if an index is used?

Look at the DBQLobjTBL or DBQLOBJTBL_HST.  There is a TypeOfUse column.  With values like this

  • 1 =   Found in the resolver
  • 2 =   Accessed during query processing
  • 4 =   Found in a conditional context
  • 8 =   Found in inner join condition
  • 16 =   Found in outer join condition
  • 32 =   Found in a sum node
  • 64 =   Found in a full outer join condition 

The values can also be summed up, if a column is encountered in more than one condition.  Such as

  • 3 =  1 + 2
  • 6 =  2  + 4
  • 14 =  2 + 4 + 8
  • 22 = 2 + 4 + 16
  • 34 = 2 + 32
  • 38 = 2 + 4 + 8 + 32
  • 46 = 2 + 4 + 8 + 32
  • 70 = 8 + 64