Re: Performance problem with partitioned indexes & tables

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.