Re: Performance problem with partitioned indexes & tables

Everyone seems against local indexes but why.

Lets do some really rough calculations on a query that returns 100 rows using the index key only.

On a none partitioned table the query hits 100 rows in the index and returns 100 rows from the data space.

Therefore the IOs might be:

4 for the index (lets assume this returns the 100 rowids) + 100 ios as the table is big and it is probable that each row is in a different block.

That's a total of 104 IOs

Now for the local index lets assume 10 partitions each returning 10 rows (keeping it simple)

The IOs for each partition might be:

4 + 10 giving a total of 140 IOs.

Now as the data is striped (or perhaps even better each partition is on a different disk) each of the 10 partitions can be run in parallel and so the actual response time could be quicker.

In fact the worse case is when no rows are returned as the comparison on ios is 4 to 40 (90%). However, the more rows being returned the less the overhead. e.g. 500 rows would give 4+500 to (4+50)*10 - 504:540 (7%).

Now this percentage is machine resources not elapse time which in theory could actually improve on a striped system. Also a lot of index IOs are not real IOs as we have a good chance of hitting cache.

And if you've chosen a good partition key then many queries should use the partition key as so these should perform better. So it is not unreasonable to conculde that overall performance might even improve.

There I would expect anything from a 5 to 10% improvement in performance to a 30% to 40% degrading in performance.

All this leads me to believe that local indexes have a good future and there must be some people out there who a using them successfully.

Michael.Burden_at_CGEY.COM (Michael Burden) wrote in message news:<3bbc0756.0208060032.250b13d_at_posting.google.com>...
> 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 Thu Aug 08 2002 - 04:27:07 CDT