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