All Forums Database
teradatauser2 236 posts Joined 04/12
19 Apr 2013
Creating a new secondary index on table

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

20 Apr 2013

If you have DBQL Object Logging enabled then you can check it in DBC.DBQLObjTbl under column TypeOfUse.

Thanks & Regards,

Shrinivas Sagare

teradatauser2 236 posts Joined 04/12
21 Apr 2013

Hi Srinivas,
Could you please share the sql and explain this a little, that will be helpful.
Thanks !

pawan0608 101 posts Joined 12/07
22 Apr 2013

You Can use SQL like this
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.

teradatauser2 236 posts Joined 04/12
25 Apr 2013

hi pawan,
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.
thanks anyways.

29 Apr 2013

dbqlobjtl_sum, which tables is this can you please tell?

Muhammad Fahad.

teradatauser2 236 posts Joined 04/12
01 May 2013

this summarizes the data from dbc.DBQLObjTbl

abhijitvyas 51 posts Joined 08/06
01 May 2013

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

pawan0608 101 posts Joined 12/07
02 May 2013

Is it a custom table or a system defined DBQL table in DBC ?

Shelley 28 posts Joined 09/10
02 Jun 2013

It populates specific  columns in the dictionary tables.. Ie DBC.Indexes, etc

teradatauser2 236 posts Joined 04/12
27 Jul 2013

Hi Abhijit,
Could you elaborate your answer ? and share some query to find this out ? I am really struggling to get the index usage info do decide on suggesting PI changes
If other also have any more suggestions, please share

You must sign in to leave a comment.