Message from discussion
How many people here rebuid index regularly?
Path: g2news2.google.com!postnews.google.com!v3g2000hsg.googlegroups.com!not-for-mail
From: joel garry <joel-ga...@home.com>
Newsgroups: comp.databases.oracle.server
Subject: Re: How many people here rebuid index regularly?
Date: Mon, 08 Oct 2007 15:08:21 -0700
Organization: http://groups.google.com
Lines: 161
Message-ID: <1191881301.346940.212200@v3g2000hsg.googlegroups.com>
References: <Y1hNi.7131$DF2.4804@trndny09>
<MPG.216ffe82b29bfb0e98968f@news.dommel.be>
<1191581490.312563.10430@o80g2000hse.googlegroups.com>
<MPG.2170841994c1cc35989691@news.dommel.be>
<1191612916.550302.168250@57g2000hsv.googlegroups.com>
<DPadnWQ-lar_XZva4p2dnAA@bt.com>
<MPG.21729f89b3ff7a3e989694@news.dommel.be>
<R4ednSBlRM2OCJfaRVnytwA@bt.com>
NNTP-Posting-Host: 75.49.200.201
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
X-Trace: posting.google.com 1191881301 32192 127.0.0.1 (8 Oct 2007 22:08:21 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Mon, 8 Oct 2007 22:08:21 +0000 (UTC)
In-Reply-To: <R4ednSBlRM2OCJfaRVnytwA@bt.com>
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1),gzip(gfe),gzip(gfe)
X-HTTP-Via: 1.0 ISA2K4-OC1
Complaints-To: groups-abuse@google.com
Injection-Info: v3g2000hsg.googlegroups.com; posting-host=75.49.200.201;
posting-account=ps2QrAMAAAA6_jCuRt2JEIpn5Otqf_w0
On Oct 8, 1:37 pm, "Jonathan Lewis" <jonat...@jlcomp.demon.co.uk>
wrote:
> Since no-one else has volunteered any suggestion, I'd
> be interested in seeing what results you get from
> the code at:http://www.jlcomp.demon.co.uk/index_efficiency.html
>
> Picking just one potentially interesting index, as it does
> a fast full scan with aggregate of all entries.
>
> Code changes needed:
> supply the index name in the 'select from user_objects'
> (which may have to be dba_objects)
>
> Where I have 'column is not null' change the column
> names (and add predicates if necessary) to cover the
> columns in the chosen index.
>
> A note of the number of leaf_blocks in the index (from
> dba_indexes/user_indexes) before and after would also
> be useful, and the block size.
I have a situation where the production schema had a major delete at
the end of last year, and has slowly been adding since. When I exp/
imp the schema in question to a test db, the size is a good 20%
smaller. I then look at the largest index that OEM tells me has
significantly fewer blocks with your program, and I see a much broader
distribution of number of rows per index blocks.
>From production:
[snippage]
ROWS_PER_BLOCK BLOCKS
-------------- ----------
113 1877
114 4984
115 3167
116 28250
117 2096
118 4314
119 2301
120 5501
121 2526
122 9402
123 1803
ROWS_PER_BLOCK BLOCKS
-------------- ----------
124 3403
125 1255
126 2421
127 919
128 2528
129 985
130 1775
131 714
132 2354
133 727
134 1589
[snippage of more rows like this]
ROWS_PER_BLOCK BLOCKS
-------------- ----------
----------
sum 172843
176 rows selected.
SQL> select leaf_blocks from user_indexes where
index_name='IC_MOVEMENTS_2';
LEAF_BLOCKS
-----------
172697
>From test:
ROWS_PER_BLOCK BLOCKS
-------------- ----------
113 1
121 1
131 1
160 1
165 1
188 210
189 2
192 1
193 697
197 1
198 2
ROWS_PER_BLOCK BLOCKS
-------------- ----------
199 52109
200 230
201 288
202 468
203 692
204 2554
205 10419
206 8775
207 3780
208 1040
209 463
ROWS_PER_BLOCK BLOCKS
-------------- ----------
210 32797
211 3
212 3
213 5
214 2
215 5
216 1
217 3012
218 1
219 1
220 1
ROWS_PER_BLOCK BLOCKS
-------------- ----------
223 358
224 1
225 1
227 1
228 1
230 1
231 222
233 1
235 1
----------
sum 118154
42 rows selected.
SQL> select leaf_blocks from user_indexes where
index_name='IC_MOVEMENTS_2';
LEAF_BLOCKS
-----------
118334
Therefore, exp/imp must indeed be the answer to life, indices and
everything! :-)
The question that is begged: Can-or-should I just compare the
leaf_blocks on all indices between production and test and rebuild
those with the largest difference? Seems too simple. (Especially
since I know if I mention this to this customer they will just want me
to exp/imp the whole db, MS-think abounds). I've never felt any need
to do anything to these indices, except due to the space issue -
between hardware upgrades and such, this is the first time they've
gone a couple of years with no maintenance.
jg
--
@home.com is bogus.
http://www.signonsandiego.com/uniontrib/20071006/news_1b6halo.html
|
