With Clause with UNIONS - how to enforce indexes

[pramsam1]

Hello,
I have a query as follows.
------------------------------------------------
with base as
(
select date, cust_id, amount
from multiple tables
union all
select date, cust_id, amount
from multiple tables
)

select * from base
where snapshot_date = '31-jul-2014'
-------------------------------------------------
This query takes too long. If I throw in "where snapshot_date = '31-jul-2014'" for both selects within the "with-clause", it is really fast.
It seems the with-clause is creating a view with all the snapshots, rather than just 31-jul-2014. Is there a way to force the "with-clause" to retrieve data for only snapshot_date = '31-jul-2014'as specified in the main query above without using the where clause inside "with-clause"?

Thanks very much all...

 

[LKBrwnDBA]

. . . Is there a way to force the "with-clause" to retrieve data for only snapshot_date = '31-jul-2014'as specified in the main query above without using the where clause inside "with-clause"?

Nope.
What does the explain plan look like?
Don't know if it will work, but if you have an index on Snapshot_Date, you could try adding "hints" to the "WITH" queries:

WITH Base
     AS (SELECT /*+ INDEX(MultipleTab1,M_Snapshot_Date_Index) */ Date, Cust_Id, Amount
           FROM Multiple Tables
         UNION ALL
         SELECT /*+ INDEX(MultipleTab2,M_Snapshot_Date_Index) */ Date, Cust_Id, Amount
           FROM Multiple Tables)
SELECT *
  FROM Base
 WHERE Snapshot_Date = TO_DATE('31-jul-2014','DD-mon-YYYY);

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb