X-Received: by 10.224.160.65 with SMTP id m1mr5525712qax.2.1367420532449; Wed, 01 May 2013 08:02:12 -0700 (PDT) X-Received: by 10.49.86.200 with SMTP id r8mr177930qez.25.1367420532421; Wed, 01 May 2013 08:02:12 -0700 (PDT) Path: news.cambrium.nl!textnews.cambrium.nl!feeder2.cambriumusenet.nl!feed.tweaknews.nl!209.197.12.242.MISMATCH!nx01.iad01.newshosting.com!newshosting.com!news-out.readnews.com!transit3.readnews.com!209.85.216.87.MISMATCH!s14no1633000qam.0!news-out.google.com!ef9ni40844qab.0!nntp.google.com!s14no1661454qam.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail Newsgroups: comp.databases.oracle.server Date: Wed, 1 May 2013 08:02:12 -0700 (PDT) In-Reply-To: Complaints-To: groups-abuse@google.com Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=15.227.185.71; posting-account=qJFqbQkAAACYQSLN0-cvP6ydkRfuOu6u NNTP-Posting-Host: 15.227.185.71 References: User-Agent: G2/1.0 MIME-Version: 1.0 Message-ID: <276b5617-4820-4293-ac7f-83d3bb41d590@googlegroups.com> Subject: Re: Index Information used_space and dba_segments From: Mark D Powell Injection-Date: Wed, 01 May 2013 15:02:12 +0000 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Xref: news.cambrium.nl On Monday, April 29, 2013 11:59:07 AM UTC-4, Jonathan Lewis wrote: > wrote in message news:cd6f7a8c-bf39-4686-a= 896-3f1a172f43aa@googlegroups.com... | Hi, | | In Oracle 11g, I analyzed an= index and checked index_stats. | | dba_segments indicates that the size of= an index was about 420MB. | | used_space in index_stats indicated that the= index used about 30MB, | and that a compression of 2 columns would save ab= out 15%. | | I then compressed the index (Rebuild..compress 2), | expecting= that the size of the index in dba_segments | to decrease. However, it did = not (It was about | 430MB after the compression). | | Leaving aside the iss= ue of compressing the index..I thought | that the fact that used_space was = much lower than the index's | space in dba_segments indicated that the inde= x was likely | sparse. | | If so, I expected rebuilding this index to decre= ase its size | in dba_segments. Why did it not? | | Regarding the argument = that analyzing an index is outdated...I | found that index_stats is general= ly accurate in predicting the | amount of space a compression will save (At= least, for an | estimation of a space savings of some 40%+ or more for a |= compression). | | | Nothing wrong with using analyze index to find the opt= imum compression count - though you can probably work it out logically if y= ou're very familiar with the data. After the rebuild with compression, what= was the space_used figure when you re-analyzed. Could you give us the full= before and after figures from index_stats. What do you have set for pctfre= e for the index ? There were a couple of anomalies with a mismatch between = dba_extents and dba_segments in some versions of Oracle - related in partic= ular to parallel rebuilds I think - have you cross checked the two views ? = -- Regards Jonathan Lewis http://jonathanlewis.wordpress.com/all-postings A= uthor: Oracle Core (Apress 2011) http://www.apress.com/9781430239543 On our 10.2.0.5 environments the extent information in dba_segments is ofte= n different from what is shown via dba_extents which seems to be correct. = I do not have a bug number since we have never bothered to report the issue= to support, but I am pretty sure this was fixed by 11.2 What kind of tablespace storage allocation is in use: dictionary, uniform e= xtents, or auto-allocate? What degree of parallelism was used on the index= rebuild? Use of parallel rebuilds can cause an index allocation to grow w= ell beyound what would be used via a non-parallel rebuild operation. HTH -- Mark D Powell --