Path: text.usenetserver.com!out04b.usenetserver.com!news.usenetserver.com!in04.usenetserver.com!news.usenetserver.com!nx01.iad01.newshosting.com!newshosting.com!meganewsservers.com!feeder2.on.meganewsservers.com!216.196.98.140.MISMATCH!border1.nntp.dca.giganews.com!nntp.giganews.com!local02.nntp.dca.giganews.com!nntp.bt.com!news.bt.com.POSTED!not-for-mail NNTP-Posting-Date: Sat, 02 Feb 2008 05:03:23 -0600 From: "Jonathan Lewis" Newsgroups: comp.databases.oracle.server References: <16988112-03bf-439b-8ded-1d82196d9a6f@c4g2000hsg.googlegroups.com> <5d867996-2db3-4376-bd3b-ec63e461b1e9@d21g2000prf.googlegroups.com> <3e940969-e124-485e-8294-f49e47c6d0ad@d70g2000hsb.googlegroups.com> <3837acc6-5746-4452-b08d-879bbceecb2c@c4g2000hsg.googlegroups.com> <65910543-91a0-47e0-a9f1-c38a6dbcdbd5@k2g2000hse.googlegroups.com> <46e21d58-11d6-479e-bc90-4e0b5a5e7cf2@l1g2000hsa.googlegroups.com> <2a6a33e2-8c78-4178-856a-98582534c681@e6g2000prf.googlegroups.com> <805c5d03-8e41-4fa9-b81f-6fd153f64060@v67g2000hse.googlegroups.com> Subject: Re: long running select min(timestamp) query Date: Sat, 2 Feb 2008 11:01:21 -0000 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2900.3138 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198 X-RFC2646: Format=Flowed; Original Message-ID: Lines: 71 X-Usenet-Provider: http://www.giganews.com NNTP-Posting-Host: 81.151.8.142 X-AuthenticatedUsername: NoAuthUser X-Trace: sv3-ALqiuG15o+cWatdcJ8Ql4OAje/OLOrb+9EovnPFCj5bbIv7X6HYk14LaM+GKxJRwsWHLSqZOTqFEMbj!F1JNCBXO/gPSUqJyJ8p7+uKI7xSJWb+bpqP2n51eXnmS9K6agJRb4PS6H5cTfytEC+iZjFKbHg== X-Complaints-To: abuse@btinternet.com X-DMCA-Complaints-To: abuse@btinternet.com X-Abuse-and-DMCA-Info: Please be sure to forward a copy of ALL headers X-Abuse-and-DMCA-Info: Otherwise we will be unable to process your complaint properly X-Postfilter: 1.3.37 Bytes: 4516 Xref: usenetserver.com comp.databases.oracle.server:441020 X-Received-Date: Sat, 02 Feb 2008 06:03:24 EST (text.usenetserver.com) wrote in message news:805c5d03-8e41-4fa9-b81f-6fd153f64060@v67g2000hse.googlegroups.com... > Anyways, Charles has helped me out bigtime by pointing me in the right > direction: >>> http://richardfoote.files.wordpress.com/2007/12/index-internals-rebuilding-the-truth.pdf >>> Pages 133-135 show what is happening in your situation. It appears >>> that this is one of the few cases where an index may need to be >>> rebuilt... > > I rebuilt the index, performed the search, got a result instantly, > only 3 consistent gets!!! > > One followup question, that you'll all likely laugh at. I got into > this "mess" by having a table that we frequently perform delete from > TABLE where DATE < ? - this situation is going to continue to arise. > Should i simply schedule frequent index rebuilds? I understand > partitioning the data is probably the way to go, but what is frequent > rebuilds the simplest solution here (by simple, i mean least knowledge/ > testing/sql involved)? > > Thanks again to all your help!!! Sincerely, > Bob Sorry about getting into this so late - but your problem is a classic 'delete the left hand end' issue, and one of the reasons why you want to use the COALESCE command after a big delete. It's probably somewhere in Richard's presentation, but when an index leaf block is emptied, it is linked to the free list, but also stays in place in the index structure. Your min() query was doing a '(min/max)' full scan, which means it was going directly to the left (low) end of the index in expectation of finding the value there. However, since you've done a thorough delete of a lot of low-value rows, the run-time engine got to the bottom block, and had to walk a long walk through a lot of leaf blocks before finding the first leaf block with any data in it. A call to coalesce will collapse together adjacent leaf blocks to reduce leaf block counts, and detach empty leaf blocks from the structure so that subsequent queries don't have to walk through them. The cost / benefit balance is: each coalesce requires a full walk of the index - so don't do it when you have a large index with only a small amount of recoverable space. failing to coalesce (for your query) requires a lot of empty leaf blocks to be walked - how many times do you want to let this happen, and how slow can the query be, before you coalesce. -- Regards Jonathan Lewis http://jonathanlewis.wordpress.com Author: Cost Based Oracle: Fundamentals http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html