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