Very interesting. Now we need someone who has used local indexes with
no, or a least minor, peformance issues.
Peter L <abc_at_123.com> wrote in message news:<jkh8lucqcrfamii6vhlcis4q6hfk6uv99k_at_4ax.com>...
> On 6 Aug 2002 01:32:07 -0700, Michael.Burden_at_CGEY.COM (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.
>
> We hit exactly the same problems and had to abandon our use of local
> indexes as a result. The Oracle consultants we spoke to acknowledged
> that there was a problem, other than expected overheads, but we never
> got a solution.
Received on Mon Aug 12 2002 - 16:51:43 CDT
