How many people here rebuid index regularly? - comp.databases.oracle.server

Message from discussion How many people here rebuid index regularly?
Path: g2news2.google.com!news2.google.com!border1.nntp.dca.giganews.com!nntp.giganews.com!local01.nntp.dca.giganews.com!nntp.bt.com!news.bt.com.POSTED!not-for-mail
NNTP-Posting-Date: Mon, 08 Oct 2007 15:37:39 -0500
From: "Jonathan Lewis" <jonat...@jlcomp.demon.co.uk>
Newsgroups: comp.databases.oracle.server
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>
Subject: Re: How many people here rebuid index regularly?
Date: Mon, 8 Oct 2007 21:37:36 +0100
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.3138
X-RFC2646: Format=Flowed; Original
Message-ID: <R4ednSBlRM2OCJfaRVnytwA@bt.com>
Lines: 55
X-Usenet-Provider: http://www.giganews.com
NNTP-Posting-Host: 86.130.241.129
X-AuthenticatedUsername: NoAuthUser
X-Trace: sv3-683QI5tLQlHn8G5sb9aAqnBkV2IXAiAVi4b7urg0QOj7RV7ZukUQFtDnSKjmT9Xv0HC7+I/EUXPzfFx!Dg5KpFvnqdHRGaBxlrnOmv/4XnAx438RoJl1tY3ygjKTiw948qCLSbzBO9Q1VWv1
X-Complaints-To: abuse@btinternet.com
X-DMCA-Complaints-To: ab...@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.35


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.

-- 
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


"Hasta" <hasta...@hotmail.com> wrote in message 
news:MPG.21729f89b3ff7a3e989694@news.dommel.be...
>
> Hmm..  If there is interest here, I could try to
> collect data from an actual production site, before
> and after a rebuild.
>
> Which data would you (collective) want to see ?
>
> Keep in mind that this would be from a customer
> production site. Therefore,
>
> * Nothing destructive, please
> * Collected data take a reasonable amount of space
> * Collection takes at most ten minutes or so,
>  preferably non blocking
> * You'll see changes induced by a one day activity
> (night job + daily activity)
>