Secondary indexes

Database
Junior Supporter

Secondary indexes

Hi,

I have a situation in which i have a table on which there is a BO query running and taking time to execute. The user suggested to create a seconday index on that table. This is giving very good result. However, the problem is that , that table is being loaded  by mload in an etl job. Now, i cannot create a secondary inde on this table as mload will have issues.

This is a normal design flow. But, in this case, how do we create a secondary index ? do we need to create another table from original table and then create a SI, but the table is big one and loaded daily. ? what can be anothe/betterr approach ?

Thx!

14 REPLIES
Enthusiast

Re: Secondary indexes

You can drop SI before Multiload run and recreate after table is loaded. What is frequency of load?

Enthusiast

Re: Secondary indexes

Multiload supports NUSI.

Fan

Re: Secondary indexes

How is the table size? Frequency of insert to the table?...If this is a small table table size (or) frequency of insert doesn't matter much for this issue.

From the above sentences I can say you are running a reporting query. 

It is definitely will decrease the perform of a insert as it simulatenously has to rebuild the NUSI table.

Instead you can first collecting statistics on the joining columns before implementing NUSI/USI.

Hope this helps!!!

Teradata Employee

Re: Secondary indexes

Which ETL tool you are using? Either you can do that in pre-SQL/post-SQL .... or have a combined script from dropping and loading SIs .... or even better option is to use TPT, if possible.

HTH!

Enthusiast

Re: Secondary indexes

is there any way i can find that on which columns i shall make SI on a table

any view or any query please share

Teradata Employee

Re: Secondary indexes

Its not about view or query .... rather it more relates to the use of the table in question. How the table is going to be accessed? Which columns will be used in joins? What type of queries are to be run on that table? And also other stats such as the table size, number of columns etc. are important.

HTH!

Enthusiast

Re: Secondary indexes

I agree Adeel. It depends how the table is gonna be accessed, in addition the fields you are crossing when creating the query...also if is a table that stores a lot data....you should consider this and other criterias to create the new SI.

Teradata Employee

Re: Secondary indexes

Also, SI will have some (may be very minor) performance impact as well while loading of data.

Enthusiast

Re: Secondary indexes

you can use PPI also but for that you will need to create new table with PPI define on that  and you have to do insert select from the old table to use PPI.it will help you in future when you will run  mload job on new table because PPI can work better then SI if it's  implemented correctly