Re: Index access into Partition View?

Are all the tables identical in structure (including order of columns)

Have you created that view as:

    select * from table1
    union all
    select * from table2
    etc.

Do all the tables have exactly the same sets of indexes.

Do you have 'partition_views_enabled=true' in the init.ora.

If any of our answers are NO, then you do not have a partition view, and Oracle may be instantiating your view as a temporary table before applying the query conditions - hence the tablescans.

If all answers are yes, try the same query but against a single table instead of the whole view - if it ignores the indexes then perhaps the optimiser thinks the index you expect to use is a bad index.

But to answer your question, it should be possible to use indexes to access a partition view.

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

garrett.vance_at_pgees.com wrote in message <7ej0ku$tnc$1_at_nnrp1.dejanews.com>...
>I have created a partition view in oracle 7.3.4; I am attempting to tune a
>query against this partition view, but I find that the optimizer seems to
>ignore indexes created on the base tables in favor of a full table scan.
>Will the optimizer ever choose index access into a base table of the
partition
>view, or will oracle ALWAYS make a full table scan of the base table???
Received on Thu Apr 08 1999 - 14:56:09 CDT