Re: Can Oracle do a query plan like this?

Hi.

 Why don't you create an index ( b,a ) and aprtition the table by b  instead of a?
 It looks to me that in your case you must define an additional  index ( on b field ).
 I think that if your queries are selecting on b then partitioning by a  is missing the point.

 HTH. Michael.

In article <87og9v3us5.fsf_at_HSE-Montreal-ppp33976.qc.sympatico.ca>,   Greg Stark <greg-spare-1_at_mit.edu> wrote:
>
> So it sounds like you're saying "no", Oracle is unable to do what I
want?
>
> Just to make it clear, a full index scan would not be useful. I have a
> situation like:
>
> id,user
> -------
> 1,frank
> 1,bob
> 1,a million other people
> 2,frank
> 2,a million other people
> 3,frank
> 3,a million other people
>
> And I need to get all the rows that have "frank" in the second column
of the
> index. It would be much faster for Oracle to do this by iterating
through all
> the values of the first column doing a range scan for frank. An full
index
> scan may be faster than a full table scan but it would still be
unusable for
> the OLTP application.
>
> I need to partition on the first value too. Surely Oracle is capable
of using
> such an index on a partitioned table? Otherwise partitioned tables are
> useless, you would basically always need to have lots of global
indexes.
>
> markp7832_at_my-deja.com writes:
>
> > Greg Stark <greg-spare-1_at_mit.edu> wrote:
> > >
> > > If I have an index on (a,b) and a has relatively low cardinality
is there
> > > any way to convince Oracle to use this index when I do a
query "WHERE
> > > b=:p"? That is to repeatedly try the various values of a looking
for this
> > > value of b.
> > >
> > > I don't want to index just b because I want this design to work
when we
> > > partition the table on a. In the case of a partitioned table with
a local
> > > index on a,b would Oracle then be willing to use the index?
> > >
> > > Assume each value of a has more than enough records to make it
worthwhile
> > > to do an index scan over a full table scan.
> >
> > As a general rule Oracle can not use a multi-valued index to solve
a query
> > if the first (or leading) columns are missing from the where clause.
> > However, if your query selected A and B and no other values from
the table
> > then Oracle might decide to use the index to retrieve the values.
If it does
> > not then you could probably force Oracle's CBO to do a fast full
index scan
> > using the hint. If you do not retrieve column A then I am not sure
the hint
> > will work or not; you will have to try it.
> >
> > [I do not remember the hint off the top of my head but I am pretty
sure
> > it starts with FFS, maybe FFS_INDEX?] You should be able to find
the
> > hints in the Application Developers Guide or the Performance and
Tuning
> > Manual.
>
> --
> greg
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sun Feb 06 2000 - 14:38:32 CST