Does the where clause in a query that calls a view, affect the filters in the view itself?

Question

Ok so I have a view that takes approximately 3 seconds to execute, and using some sample data it manages to return a total of 11 rows.

Then I make a query that calls the view, such as the following one:

SELECT SUM(COL1), SUM(COL2), SUM(COL3)
FROM
(SELECT CLIENT,
        SUM(DECODE(COL1,'ABC',1,0)), 
        SUM(DECODE(COL2,'DEF',1,0)),
        SUM(DECODE(COL3,'ABC',1,0)),
 FROM MyView
 WHERE INDICATOR IN ('GREEN','YELLOW')
 GROUP BY CLIENT, TYPE
 UNION ALL
 SELECT 'TOTAL' TOTAL,
        SUM(DECODE(COL1,'ABC',1,0)), 
        SUM(DECODE(COL2,'DEF',1,0)),
        SUM(DECODE(COL3,'ABC',1,0)),
 FROM MyView
 WHERE INDICATOR IN ('GREEN','YELLOW')
 GROUP BY TYPE
 )
 WHERE TYPE = 'TYPE A'
 GROUP BY CLIENT

In this manner it manages to return rows grouped by client on top and on the bottom it shows the total of all the sums of all clients.

However when I run the entire query, it takes over 1 minute to execute, with only 11 rows to manage from the view.

If I run each part of the union indepedently they each take about 11 seconds and if I run them together they take about 23 seconds.

But with the outter most query it takes over a minute???

Am I missing something about how views work? I noticed that if I took out the where clauses from the entire query it takes only 12 seconds to run the entire query.

Any help is appreciated.

Answer 1

How about using group by rollup instead of your complicated expression?

SELECT CLIENT,
       SUM(DECODE(COL1, 'ABC', 1, 0)), 
       SUM(DECODE(COL2, 'DEF', 1, 0)),
       SUM(DECODE(COL3, 'ABC', 1, 0)),
FROM MyView
WHERE INDICATOR IN ('GREEN', 'YELLOW') AND
      TYPE = 'A'
GROUP BY ROLLUP(CLIENT)

I think it does what you want.