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 Fri Aug 09 2002 - 17:47:53 CDT
