Re: 7.3 Inconsistent Partition Elimination.

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 12 Jan 1999 21:56:18 -0000
Message-ID: <916178138.19434.5.nnrp-08.9e984b29@news.demon.co.uk>

Martin Farber wrote in message <369A8457.BE6E778F_at_computer.net>...
>
> CREATE TABLE MY_COUNT
> UNRECOVERABLE AS
> SELECT /*+ ALL_ROWS */ COUNT(*) -- A LONG list of columns in
>real life
> FROM PV
> WHERE Partition# = 15;
>
> SELECT STATEMENT HINT: ALL_ROWS
> SORT
> VIEW
> UNION-ALL PARTITION
> TABLE FULL PART1
> TABLE FULL PART2
> TABLE FULL PART3
> TABLE FULL PART4
> TABLE FULL PART5
> ...

Your interpretation of the execution path is correct - on the other hand just because explain plan quotes this path, it isn't necessarily true.

Try looking at the trace file after alter session set sql_trace true: 7.3.4 shows the actual execution path which may be different from the notional one.

It's not just partition views where the plan for CTAS differs from the plan of the straight select. One (daft) suggestion - try setting the hint to first_rows

Second suggestion -
Create a view

        create view v1 as
            (select * from t1 union all .... union all select * from t5)

then create as select from view (don't ask why - this sometimes fixes PVs).

Are you also using the parallel query option ? It may help if you make the select a parallel select.

> We came across a similar problem if we hit the CHECK constraint with
>a
> value from a join:
>
> SELECT /*+ ALL_ROWS ORDERED */ COUNT(*)
> FROM PART_INFO PI
> , PV
> where PI.Partition# = PV.Partition#
> and PI.Partition_name = 'PARTITION 15'
> /
>
>
> Again, NO FILTERS!!! Without the filters, the VIEW is useless!
>Indexes

This is documented behaviour - Oracle needs a literal, (or a literal that can be used through transitivity rules (a=b and b=c so a = c) although there are ways to achieve a joined table effect using view-based partitioning instead of constraint-based partitioning.

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk Received on Tue Jan 12 1999 - 15:56:18 CST