Re: Partitioned views

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 Tue Jun 02 1998 - 09:53:34 CDT