Re: Partition view not working - help!

Bruce Bristol (bbristol_at_ix.netcom.com) wrote:
: I've set up a partition view on 7 tables. However, when I do an explain
: plan, it says it's hitting all 7 tables.
:
: I spoke to Oracle about this and they said that partition views only
: work on simple queries, when the where clause only contains the column
: on which the partition view is based.
:
: Anyone have info other than this?
:

I'm amswering from memory of some work I did a few months back on 7.3.2.3 under Solaris 2.5.1 so I don't guarantee that this is 100% accurate, but I believe what you've been told is true. Unfortunately, this limitation of partition views is not well documented, but you must partition your tables on an index of ONE column or else no filtering of tables will take place. The 7.3 Server Tuning Guide says this in an ass-backwards way with the phrase 'Partition views do not support concatenated partitioning keys'.

This does mean that if you are partitioning by date, which I'm assuming has many duplicate values within a partition, it may be difficult or impossible get any benefit from partition elimination. If the overhead of hitting the 6 indexes with no qualifying rows is too much, you are probably going to have to find a different solution.

/*+ opinions expressed here do not necessarily represent those of

    Oracle Corporation
*/

[snip...]
:
: Thank you!
:
: -Bruce
:
Received on Thu Nov 06 1997 - 00:00:00 CST