On Feb 20, 5:11 pm, "James" <jwilk..._at_gmail.com> wrote:
> On Feb 20, 3:49 pm, "Valentin Minzatu" <valentinminz..._at_yahoo.com>
> wrote:
>
>
>
>
>
> > On Feb 20, 4:37 pm, "James" <jwilk..._at_gmail.com> wrote:
>
> > > Hi,
>
> > > I have a view that joins two partitioned (range) tables. They have
> > > the same partition key - quarter of the year.
>
> > > The view looks like this:
> > > create view my_view as(
> > > select a.id, a.name, a.quarter, b.id, b.name, b.quarter
> > > from tab1 a, tab2 b
> > > where a.id = b.id(+) and a.quarter = b.quarter(+))
>
> > > And I am invoking the view like this:
> > > select * from my_view where quarter = 4
>
> > > Due to the outter join, Oracle always scans all 8 partitions from both
> > > tables. What do I have to do to get it to realize that I am only
> > > asking for the data of the forth quarter and use the proper
> > > partition? Thanks!
>
> > > James.
>
> > Have you tried specifying PARTITION clause in your query?- Hide quoted text -
>
> > - Show quoted text -
>
> I can't really limit the partition inside the view. How do I specify
> the partition from the code that invokes the view?
>
> James.- Hide quoted text -
>
> - Show quoted text -
You can't - I thought there is only one partition you'be looking for
at all times. Can you post the execution plan for your query? What
version of Oracle is it running against?
Received on Tue Feb 20 2007 - 16:15:50 CST
