Hello Guys,
In the past I had a view defined as follows CREATE view abcd as SELECT whatever ...... --- query1 Some business requierments came up and I had to change it like this CREATE view abcd as SELECT whatever ...... --- query1 UNION SELECT whatever ......---- query2 Now I have a problem in the time for calculating the query when using a predicate -- this time makes sense SELECT * FROM abcd Query time ( Past) = X Query time (current) = X +Y -- (Y is the time which introduced by query2) But If I run the query -- This does not make sense SELECT * FROM abcd where predicate = 'predicate_a' Query time ( Past) = 1 /10 * X Query time (current) = X + Y -- I assume the time should be 1/10*X + Y --Note, Y is much smaller than X so I do not care too much about it, so X is the dominant factor I had a look on the execution plane and the predicate 'predicate_a' was pushed up on the top in the current situation i.e. In the past the excution plane was like this Filter using the predicate 'predicate_a' and then do the calculation of the rest of query1, this is why the time is reduced to 1/10 * X Now the execution plan is like this Calculate query1 and then calculate query2 and then Union the result and finally filter using predicate 'predicate_a', Why it is not like this Filter first using the predicate 'predicate_a' when calculating query1 Filter first using the predicate 'predicate_a' when calculating query2 Then do the union Sorry I did not post the execution plan but it is more than 5000 line Regards
