Re: Performance problem with partitioned indexes & tables

It's me who should apologies. We've spent three weeks doing tests, so to abandon local indexes within the first few days was a little disappointing.

I've added more details about the theory (or my view of the theory) later in the thread regarding local indexes vs global indexes.

The execution plan didn't highlight anything special. It seemed to be what one might expect. i.e. A normal access method using the local index as a child to the partition iterator. Stats also didn't show anything.

The reason I get frustrated is that I believe local indexes are getting a bad press. I was hoping someone would say - we use them and have no real problems. or Performance was slightly worse or better etc etc...and so on.

Richard Foote <Richard.Foote_at_oracle.com> wrote in message news:<3D5226D0.F8520BA_at_oracle.com>...
> 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.
> > > --
> --
Received on Fri Aug 09 2002 - 06:06:36 CDT