Path: news.easynews.com!easynews!newsfeed-east.nntpserver.com!nntpserver.com!news.maxwell.syr.edu!intgwpad.nntp.telstra.net!news.telstra.net!newsfeeds.bigpond.com!not-for-mail Message-ID: <3D5226D0.F8520BA@oracle.com> From: Richard Foote Organization: Oracle Corporation X-Mailer: Mozilla 4.75 [en] (Windows NT 5.0; U) X-Accept-Language: en MIME-Version: 1.0 Newsgroups: comp.databases.oracle.misc Subject: Re: Performance problem with partitioned indexes & tables References: <3bbc0756.0208060032.250b13d@posting.google.com> <3D50D88C.A6D9F360@oracle.com> Content-Type: multipart/mixed; boundary="------------A83BEF882BC0FC11E5A7E3B1" Lines: 136 Date: Thu, 08 Aug 2002 18:07:44 +1000 NNTP-Posting-Host: 144.139.227.235 X-Trace: newsfeeds.bigpond.com 1028793962 144.139.227.235 (Thu, 08 Aug 2002 18:06:02 EST) NNTP-Posting-Date: Thu, 08 Aug 2002 18:06:02 EST Xref: easynews comp.databases.oracle.misc:85310 X-Received-Date: Thu, 08 Aug 2002 01:10:34 MST (news.easynews.com) --------------A83BEF882BC0FC11E5A7E3B1 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit Hi Michael, I'm sorry, I don't mean to add to your frustration (I guess I haven't lost sounding like Oracle since I've left :) I did say that I was somewhat surprised by the additional overhead as you only have the 7 partitions and that the execution plan may reveal something untoward (in the form of an inappropriate partition scan or some such). Does it ? Regards Richard Michael Burden wrote: > > Thanks for the reply but my point IS the overhead is NOT 4 seconds. > > I can split the query into 7 bits of sql that will scan all the > partitions in 0.03 of a second so why does Oracle take 4 seconds. If I > can do it it why can't oracle. Hence my point about emailing myself > the results round the world faster than Oracle can return the results. > It does not take 4 seconds to iterate round an internal table (not > even on disk) of 7 partitions and hit seven indexes. I'm sorry but > that is my point and you have just stated what Oracle say. Perhaps > that's why I can't get through Oracle. I know there is an overhead but > our batch jobs were going 100 times slower. If that's the normal > overhead then the warning message should be flashing red lights on the > front of their web page. > > Anyway partition tables using global indexes seem pretty limited and > the books agree with me here. The big or really useful advantages only > come with local indexes. > > I really beleive there is more to this and that there is either a > > BUG. > Something wrong in the setup like stats. > Or a logical reason that explains the problem or something I've > missed. > > Richard Foote wrote in message news:<3D50D88C.A6D9F360@oracle.com>... > > 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. > > -- --------------A83BEF882BC0FC11E5A7E3B1 Content-Type: text/x-vcard; charset=UTF-8; name="Richard.Foote.vcf" Content-Transfer-Encoding: 7bit Content-Description: Card for Richard Foote Content-Disposition: attachment; filename="Richard.Foote.vcf" begin:vcard n:;Richard x-mozilla-html:FALSE adr:;;;;;; version:2.1 email;internet:Richard.Foote@oracle.com fn:Richard Foote end:vcard --------------A83BEF882BC0FC11E5A7E3B1--