7.3 Inconsistent Partition Elimination.

   Help!

   We're not ready to go to Oracle 8 yet, but we would hope that there is a

   solution to the current situation, although it seems to elude Oracle Support:

   We've got a classic Oracle 7 Partitioned View, a bunch of tables UNION
   ALL'd together with an * for their select lists, and check constraints. They work fine

   if you perform a simple SELECT:

   SELECT /*+ ALL_ROWS */ COUNT(*)
   FROM PV
   WHERE Partition# = 15;

   SELECT STATEMENT HINT: ALL_ROWS
   SORT
   VIEW
   UNION-ALL PARTITION
   FILTER
   TABLE FULL PART1
   FILTER
   TABLE FULL PART2
   FILTER
   TABLE FULL PART3
   FILTER
   TABLE FULL PART4
   FILTER
   TABLE FULL PART5
...

   When it works, it's a beautiful thing!!! Unfortunately, a couple of    situations I've encountered shoot it all to hell. The simplest being, making it

   into a simple CREATE TABLE:

   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
...

   Look Ma, NO FILTERS!!! I can't figure out why it KNOWS that it's a    Partitioned View, but it doesn't attempt to perform any Partition Elimination!!!

   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

   might help,
   but they seem like an incredible waste of time and space since every    value in a table is identical, as per the check constraint!

   Any thoughts?

We're currently running 7.3.4.2.0 on HP/UX 10.20, but I've since tried it at
the same level on Solaris 2.6 with the same results.

Oracle had me download the patchset for 7.3.4.3, but they can't tell me if it
addresses my problem. Anyone have any success?

Please email me directly:

farber_at_computer.net

Thanks!

<<MFF>> Received on Mon Jan 11 1999 - 17:08:07 CST