Creating a new secondary index on table

Database
Enthusiast

Creating a new secondary index on table

Hi,

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

Thx

10 REPLIES
Enthusiast

Re: Creating a new secondary index on table

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

Enthusiast

Re: Creating a new secondary index on table

Hi Srinivas,

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

Thanks !

Enthusiast

Re: Creating a new secondary index on table

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.

Enthusiast

Re: Creating a new secondary index on table

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.

Enthusiast

Re: Creating a new secondary index on table

dbqlobjtl_sum, which tables is this can you please tell?

Enthusiast

Re: Creating a new secondary index on table

this summarizes the data from dbc.DBQLObjTbl

Enthusiast

Re: Creating a new secondary index on table

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

Enthusiast

Re: Creating a new secondary index on table

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

Enthusiast

Re: Creating a new secondary index on table

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