I have the following questions regarding NUSI and the use of views.
Let's consider for simplicity, we have the following table:
CREATE SET TABLE T1 (p_key INTEGER, c1_ind INTEGER, c2_ind VARCHAR(16), c3 INTEGER, PRIMARY KEY (p_key)) INDEX ind (c1_ind , c2_ind);
If we access the data in this table using a simple select with a filter on NUSI columns, then the NUSI will be used. F.e.:
select * from T1 where c1_ind = 1 and c2_ind = 'a'
In "Explain Plan", we can see "...we do an all-AMPs RETRIEVE step from T1 by way of index # 4".
But if we create a view for this table:
CREATE VIEW t1_view ("c1_ind","c2_ind","c3") AS SELECT TOP 100 PERCENT t1.c1_ind, t1.c2_ind, t1.c3 FROM T1;
and then do the same select but on view:
select * from t1_view where c1_ind = 1 and c2_ind = 'a'
the NUSI is not used and this query works much slower.
Why the NUSI is not used in this case? Or do we have to do some additional steps so that the NUSI will be visible for the view?
Many thanks for any hint!
When you put "top 100 percent" in your view definition, you force the query to go through the sampling code and create a spool file. This eliminates the possibility of using the indexes on the underlying table.
What is the purpose for the sampling clause when it appears you want all the data?
I've just seen that this "TOP 100 PERCENT" is used in the application to create ordered views when working on Teradata (I haven't copied the "order by.." in my initial message, sorry) (also, see this discussions https://forums.teradata.com/forum/database/order-by-date)
But okay, if there is no way of using "TOP 100 PERCENT" and indices at the same time, the only way would be to do some rebuild and to "order by" not in "create view" statements, but during the select from these views.