Re: Performance problem with partitioned indexes & tables

Hi Michael,

I'm sorry, I don't mean to add to your frustration (I guess I haven't lost sounding like Oracle since I've left :)

I did say that I was somewhat surprised by the additional overhead as you only have the 7 partitions and that the execution plan may reveal something untoward (in the form of an inappropriate partition scan or some such).

Does it ?

Regards

Richard

Michael Burden wrote:
>
> Thanks for the reply but my point IS the overhead is NOT 4 seconds.
>
> I can split the query into 7 bits of sql that will scan all the
> partitions in 0.03 of a second so why does Oracle take 4 seconds. If I
> can do it it why can't oracle. Hence my point about emailing myself
> the results round the world faster than Oracle can return the results.
> It does not take 4 seconds to iterate round an internal table (not
> even on disk) of 7 partitions and hit seven indexes. I'm sorry but
> that is my point and you have just stated what Oracle say. Perhaps
> that's why I can't get through Oracle. I know there is an overhead but
> our batch jobs were going 100 times slower. If that's the normal
> overhead then the warning message should be flashing red lights on the
> front of their web page.
>
> Anyway partition tables using global indexes seem pretty limited and
> the books agree with me here. The big or really useful advantages only
> come with local indexes.
>
> I really beleive there is more to this and that there is either a
>
> BUG.
> Something wrong in the setup like stats.
> Or a logical reason that explains the problem or something I've
> missed.
>
> Richard Foote <Richard.Foote_at_oracle.com> wrote in message news:<3D50D88C.A6D9F360_at_oracle.com>...
> > Hi Michael,
> >
> > Lets just think about this for a tick.
> >
> > If you have 7 partitions then your local indexes have 7 partitions as
> > well. This means we now have 7 index structures, each one pointing to
> > rowids in their respective, local table partitions.
> >
> > If in a where condition you specify a column in your local index but do
> > not specify the partitioned column(s), then the particular value could
> > be found in any of the table partitions.
> >
> > Therefore, Oracle has no choice but to read all 7 local indexes in order
> > to satisfy the query. This will potentially be less efficient than
> > reading 1 global index. This is not a bug but a logical ramification of
> > having local indexes that are being searched upon without specifying the
> > partitioning column(s). Note the more partitions, the heavier the cost.
> > I'm a little surprised that the overheads in your case are as dramatic
> > as you suggest (with the 7 partitions). I would have a close look at the
> > execution plan and see if anything else untoward is occurring.
> >
> > The solution is a global index (which can of course can be partitioned
> > as well), which means you only have the one index to traverse. There are
> > pros and cons to both local and global and again it's a question of
> > horses for courses which requires a bit of research.
> >
> > Don't worry too much about the warning you suggest, it's all documented
> > !!
> >
> > Good Luck
> >
> > Richard
> >
> >
> >
> > Michael Burden wrote:
> > >
> > > We have recently partitioned a large table (500 Million) rows over 7
> > > partitions using local indexes
> > >
> > > However, we experienced major performance problems with small row fetches
> > > (i.e. < 100) using non-unique indexes. The issue is that an SQL statement
> > > executes an all partition fetch which, in this test case, returns no rows.
> > > Total number of rows scanned on the base tables was zero because the key
> > > didn't exist. However the SQL statement takes 3 to 4 seconds to respond.
> > > This means our major batch programs which usually sees fetches in the order
> > > of 100ths of a second are now taking a touch longer. Obviously we have
> > > reverted to global indexes in the mean time, and reported it to Oracle as a
> > > problem and set up a smaller test table to prove the problem did not go
> > > away.
> > >
> > > I changed the SQL to hit a single partition and this returned the
> > > performance back to expected. Now the point is I only have seven partitions
> > > so I can write the program to hit each partition separately sort the results
> > > put them in an email send them round the world to myself open and read the
> > > mail before Oracle returns the results using it's all partition scan method.
> > >
> > > So I believed this to be a problem and Oracle's reply is that there is a
> > > performance overhead with partition indexes and we should use global
> > > indexes.
> > >
> > > Does anyone know whether this is a known bug/issue/problem and has any one
> > > else experienced something similar. If it is then there should be a warning
> > > message stating:
> > >
> > > DONT USE PARTITIONED INDEXES AS THEY CAN SERIOUSLY DAMAGE PERFORMANCE -
> > > UNTIL IT'S FIXED.
> > --