Archives of the TeradataForum

Message Posted: Tue, 28 Mar 2006 @ 11:28:26 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

<-- Anonymously Posted: Tuesday, March 28, 2006 06:06 -->

Alls,

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 = 
     AND   TB.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 = 
     AND   TB.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?

Thank you

	<Prev	Next>		<<First	<Prev	Next>	Last>>