Re: Performance problem with partitioned indexes & tables

Yes, the key is not the partition key but that should be a big problem and the test query should not perform in 3 to 4 seconds. All Oracle has to do is go to each partition (seven) and use the local index. This (in theory) could be done in parallel and so should take 2 or 3 times longer as the table is striped and so some overlap should occurr.

e.g.

Select * from big_table where policyn = 'X'

takes 3 to 4 seconds but why. All Oracle is really doing ....

Select * from big_table where policyn = 'X' and partition_date < '19941213';
Select * from big_table where policyn = 'X' and partition_date between '19950101' and 19951213'; Select * from big_table where policyn = 'X' and partition_date between '19950101' and 19951213'; ...and so on to cover all partitions.... Select * from big_table where policyn = 'X' and partition_date >= '20020101';

If I do the above I will get the same results and each query takes 0.01 or 0.00 of a second. Multiply this by seven and you don't get 3 to 4 seconds.

In 3 to 4 seconds I can run some pretty big queries (i.e. 1000nds of rows)

So I don't understand Oracle's answer. I know there is an overhead but 4 seconds is MASSIVE and certainly not proportional. I've use teradata where local indexes were common place (i.e. Non-unqiue secondary indexs)and although OLTP was not so good it was not that bad.

willyk_at_kbi-gmbh.de (W. Klotz) wrote in message news:<3d50312f$0$26983$9b622d9e_at_news.freenet.de>...
> Do I understand it correct that the key your are searching for is not
> the key used for partitioning ?
>
> If so, what would you expect Oracle to do when searching for the
> records (in case you dont use global indexes) ?
>
>
>
> 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.
Received on Wed Aug 07 2002 - 11:49:54 CDT