I have a large table with around 500 Mi records and have Primary Index created on Column 1 to enable better perfroamce for Dashboards. But during development , most of the queries either happen on Column2 or a combination of columns2,3 and 4. Please recommend if having multiple Non Unique Secondary indexes is a good option to improve query performace and load times ? I believethe secondary Index will be stored and will consume some database space and is an expensice operation, is it true ? is there any other alternative than creating a secondary index to improve the performace?
Yes, the cost of a NUSI (or any non-PI index) is similar to a separate table that is being maintained with triggers, and depending on how much data is being loaded it might be quicker to drop the index before a load and recreate it afterward. It certainly can speed up access to specific key values other than the PI, but you will want to test it first to see what the storage and load requirements are. Another option instead of NUSI is a Single Table Join Index: if the users are always going after some subset of data, it may be practical to store those columns in the STJI and avoid accessing the main table altogether. See the Database Design manual at info.teradata.com - it has several chapters on indexes.
As well as the suggestions from @GJColeman you might want to look at a PPI on the column(s) frequently used for selection.
PPI is usually a lower cost than NUSI for loading data and can give tremendous read performance.