All Forums Database
eejimkos 73 posts Joined 01/12
03 Jun 2013
Behaviour of a query

Hello,
 
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
 
viewa =
sel * from
(
calendar_dim
inner join
(
sel * from table_a
inner join table_b
union all
sel * from table_c
inner join table_D
)
on...
)
 
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
 
sel *
  from viewa
 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.
 
sel a.*
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.
 
Thank you very much for your time.
 
 
 
 
 
 
 
 
 

You must sign in to leave a comment.