Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> 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
![]() |
![]() |