Behaviour of a query

Database

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.

Tags (3)