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

Message from discussion How many people here rebuid index regularly?

Jonathan Lewis

View profile

More options 8 Oct 2007, 21:37

Newsgroups: comp.databases.oracle.server

From: "Jonathan Lewis" <jonat...@jlcomp.demon.co.uk>

Date: Mon, 8 Oct 2007 21:37:36 +0100

Local: Mon 8 Oct 2007 21:37

Subject: Re: How many people here rebuid index regularly?

  Reply to author
  |
  Forward | Print | View thread
  | Show original
  | Report this message | Find messages by this author
  

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)

Reply to author Forward