All Forums Database
teradatauser2 236 posts Joined 04/12
17 Apr 2013
Secondary indexes

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 ?

Harpreet Singh 101 posts Joined 10/11
17 Apr 2013

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

17 Apr 2013

Multiload supports NUSI.

Prem 3 posts Joined 09/07
20 Apr 2013

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!!!

Adeel Chaudhry 773 posts Joined 04/08
24 Apr 2013

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.

-- If you are stuck at something .... consider it an opportunity to think anew.

29 Apr 2013

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

Muhammad Fahad.

Adeel Chaudhry 773 posts Joined 04/08
29 Apr 2013

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.

-- If you are stuck at something .... consider it an opportunity to think anew.

Kbos 20 posts Joined 04/13
29 Apr 2013

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 should consider this and other criterias to create the new SI.

Adeel Chaudhry 773 posts Joined 04/08
30 Apr 2013

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

-- If you are stuck at something .... consider it an opportunity to think anew.

vikas_yadav 19 posts Joined 09/12
30 Apr 2013

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 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

deva 2 posts Joined 05/13
08 May 2013

at what time these diff types of INDEXES will be useful can any one help me out



Shelley 28 posts Joined 09/10
09 May 2013

Partitioned primary indexes are great for large tables where you consistently access the data by some type or range, ie day, week, month as an example. These will reduce full table scans especially if you only need one or two days out of several months or years of data,.
secondary indexes are used as another form of  access to the data. Ideally you would use a Pi for most access as it is the most efficient  but if you have additional columns that you use to access data from the table you might want to try a secondary index on column. Make sure you test each SI after you create them (and collect stats) to ensure they are being used. If they are not being used they are a waste of resources

VandeBergB 182 posts Joined 09/06
09 May 2013

As was mentioned earlier, MLOAD supports NUSI's, but it won't support USI' that case you might look at other suggestions like Shelly's PPI input or drop and recreate as pre and post load sql

Some drink from the fountain of knowledge, others just gargle.

vrushang 4 posts Joined 11/10
24 Jul 2013

Knowing that tables are loaded with mload so there won’t be any join index so  SI would help to join tables .
I agree with Shelley. Creating a  valued order NUSI would be good, when you are running small range queries and USI when equality conditions when SI columns is part of where clause.
If there are multiple SI then avoid using OR operator as optimizer would not consider the SI and perform FTS. So for optimizer to SI use the AND operator ( NUSI Bit mapping)
Cons of SI
1)if stats not collected on the SI columns then it won’t be effective.
2) SI would need additional IO , thus if MLOAD is updating  the SI column it might be costly to create SI

Adeel Chaudhry 773 posts Joined 04/08
03 Feb 2014

Using TPT is the best option in this scenario.

-- If you are stuck at something .... consider it an opportunity to think anew.

You must sign in to leave a comment.