For new requirements of client,we need to setup non-key column search. I want to avoid Index creation so assessing a new table creation with the non key column of Original table as partition key would be better OR Materialized view would be better option here. As both new table and materialized view would occupy storage (And Client thinks it is wastegae of storage as we are saving same data twice per node) so what extra we may achieve by creating Materialized view?(Also its prompting not suitable for production). By any chance there is other way than creation of Index/materialized view/new table with Non key column (Of original table) as partition key to manage this kind of situation where Non key column search query would be introduced
Materialized views (MV) are classified as an "experimental feature" in Cassandra because there are scenarios where the view gets so out-of-sync that the only known workaround is to drop and recreate the view.
MVs are disabled by default which is why you get a warning if you try to create a view (CASSANDRA-13959). I've written about it in Should we reconsider using materialized views in Cassandra?.
The advantage with MVs is that Cassandra automatically creates and maintains the new table for you. However, there are some disadvantages with the first being that updates to the view are asynchronous so you cannot immediately see the data.
One misconception is that the data is not duplicated which isn't correct. The data in the base (source) table is duplicated to the view so it doesn't save on storage as you are expecting.
From your post, it is clear to me that you are already aware that secondary indexes are not the solution, particularly if performance matters, so the only option you are left with is to design a table for each application query. Cheers!