One user is asking to create a secondary index on a column of a table. I created a test table and tested it, it shows good result. But , before creating a secondary index, i need to do an analysis as to how many times is that column used in the where clause in all queries ?based on that , i can take a decesion.
Is there a query to take out this count ? or what is a method for this kind of analysis ?
Its a little urgent, so a early reply is appreciated
You Can use SQL like this
SEL * FROM dbc.DBQLObjTbl
WHERE ObjectType = 'Idx'
AND ObjectNum = 4 /* or whatever is your secondary index number 8, 12, 16 ... */
AND ObjectDatabaseName = <your database name>
AND ObjectTableName = <your table name>
You will see that 'TypeofUse' have value '2' - Accessed during query processing
you can join this Table with DBQLogTbl table or other DBQL tables based on ProcID and QueryId to see other information about the query like SQL, resource usage, processing time, step time etc.
using this table doesn't help much, not sue how this table logs this data, insead i used a table dbqlobjtl_sum, this keeps summary of indexes etc. that helped.
another option that you have is enable DBS parameter ObjectUsecountCollectrate and analyze values of access count and last access timestamp. You will see both access count and lastaccesstimestamp value in dbc.tables as well as dbc.indices.This will be also useful to analyze the tables and indexes which are not frequently accessed based on the usage