I read an article on redshift and views where there was mentioned something like soft partitioning. I am curious if this could work. U fortunately I am not a DBA and don't have access to our cluster administration . We are currently in developing some dimension and fact table as a star schema.
I have never heard about such thing like creating a view with several unioned queries with hardcoded values in Oracle but maybe it works in redshift not the same way.
You can create a view like this
create view my_view as
(
select 'action' as genre, a.* from movie_action a
union all
select 'horror', b.* from movie_horror b
)
So if you now query select * from my_view then the underlying code in the view get executed amd data gets retrieved. This is standard behavior amd there is no performance benefit like gor materialized view. But wahat happend if you mention the hardcoded value in the where clause. I read in the article that something like select * from my_view where genre = 'action' will behave differently. The optimiser know from which subquery should pull the data,so just the subquery for movie_action table will be executed thus performance boost cause less data to scan. Never heard in Oracle world but maybe it workd in Redshift. Could it be true? I can not create any views cause I have select privileges only.