Archives of the TeradataForum
Message Posted: Tue, 28 Mar 2006 @ 11:28:26 GMT
<-- Anonymously Posted: Tuesday, March 28, 2006 06:06 -->
we have a problem on using views in TD V2R5.1.
We have a table FACT (1 billion rows) (with a NUSI on Col_A, Col_B) on wich there is a view VF:
REPLACE VIEW VF_U AS SELECT * FROM FACT;
The following query (Q1) against view VF (where TA and TB are dimension tables):
SELECT VF.Col_C, VF.Col_F, VF.Col_A, TA.Col_C FROM VF INNER JOIN TA ON TA.Col_A = VF.Col_A INNER JOIN TB ON TB.Col_A = VF.Col_B WHERE TA.Col_B =
runs in one second --> Secondary Index on FACT is used via nested join.
If we defined another view VF_U (FACT and FACT_2 have identical structure, same indexes but different data, data are mutually exclusive) like the following:
REPLACE VIEW VF_U AS SELECT * FROM FACT UNION ALL /* data are different */ SELECT * FROM FACT_2;
The same query Q1 but with the new View (same filters):
SELECT VF_U.Col_C, VF_U.Col_F, VF_U.Col_A, TA.Col_C FROM VF_U INNER JOIN TA ON TA.Col_A = VF_U.Col_A INNER JOIN TB ON TB.Col_A = VF_U.Col_B WHERE TA.Col_B =
doesn't use anymore NUSI on (Col_A, Col_B) defined on the two tables FACT and FACT_2.
Instead optimizer runs 2 Full Table scan on the all rows of the 2 tables FACT and FACT_2 (2 billion of rows) and then use the filters on a Very Huge Spool. Response time is very high.
Some hints to avoid this behaviour and to force the use of NUSI with a UNION ALL view?
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|