I would like your help for the next issue.It is a real problem,but i will presente you like an example.
I have a view which is composed as below
sel * from
sel * from table_a
inner join table_b
sel * from table_c
inner join table_D
All the tables , table_a table_b table_c table_d have the same pi and partition columns(the 1st partition level is at the date) , stats are up to date .
My question is the following,
If i will try to do a qurey such as
where cal_Date = '2013-05-31'
-->> the explain plan works perfect. It takes only one partiiton from each table and produce the result.
The same happens with multiple date , /......where cal_Date in ('2013-05-31','','',.......)
(it takes the correct number of partitions ).
BUT , if i will try to inner join this view with a table , which contain 10 distinct dates , i was waiting to have 10 partitions from each table of the view inner join this spool with the external table.
from viewa a
inner join table_e b
on a.cal_Date = b.cal_Date
, it does an all amp retreive from each table of the view and then duplicates the external table ,The issue is that the 4 tables inside the view are very big,about 1 billion rows.
Solution on this?
I tried to make a volatile table (with one column = date ) which contains only the distinct date that i am expected but nothing.Still the same behaviour.