Re: Partitioned views

Hi Jan,

Changing the statement to what you suggest works and both views partition.

Can you explain why this works. Do you need to supply an absolute value for the contraint for the optimser to partition the view.

The SQL is actually being generated by Business Objects so I have very little control over what SQL get generated.

Regards
Ronnie

Jan H Malmberg wrote:

> Ronnie Doggart wrote in message <3573A264.90C3451E_at_netcomuk.co.uk>...
> >Hi All,
> >
> >I have a database where I have two partitioned views (view_a, view_b)
> >each doing a union all on 12 tables each and constrained using date. If
> >I do a:
> >
> >select * from view_a where date = '01-June-1998';
> >
> >Then view_a partitions OK.
> >
> >select * from view_b where date = '01-June-1998';
> >
> >Then view_b partitions OK.
> >
> >However if I issue the following query:
> >
> >select * from view_a, view_b where view_a.date = '01-June-1998' and
> >view_b.date = view_a.date and view_b.id = view_a.id;
> >
> >Then view_a partitions but view_b does not and preforms full table scans
> >on all tables.
> >
> >What am I doing wrong !
> >
> >Regards
> >Ron
> >
>
> Try
>
> select * from view_a, view_b
> where view_a.date = '01-June-1998'
> and view_b.date = '01-June-1998'
> and view_b.id = view_a.id;
>
> This will give the optimizer the necessary information.
>
> Jan
Received on Wed Jun 03 1998 - 02:39:03 CDT