Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Episode 2: the EntMgr Green Light!!

Re: Episode 2: the EntMgr Green Light!!

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Sun, 9 Nov 2003 10:23:30 +1100
Message-ID: <3fad7aff$0$3498$afc38c87@news.optusnet.com.au>

"Domenic G." <domenicg_at_hotmail.com> wrote in message news:c7e08a19.0311081244.7f0acdb_at_posting.google.com...
> Niall,
>
> Thanks for your input. I have a few questions ...
>
> What is the purpose of a reverse-key index then? Surely the guys at
> Oracle who can actually see inside the code know more than us?

The purpose of reversing the key is to ensure that inserts to a monotonically incrementing sequence number are scattered across the entire base of the index, instead of all wanting to find a home in the last (right-most) leaf-node. For them all to collide on the last-most leaf node would result in massive amounts of contention, and your buffer busy waits would go through the roof as a result.

Sequences, in other words, produce a contention issue, not an un-balanced index issue. And reverse keys are one way (and not a particularly good way, either) of dealing with that.

> I'm only doing what Oracle taught me to do. They should know their
> product. Are you saying they're wrong?

Yup. But it's a bit of a cop-out to say 'Oracle taught me'. Thomas Kyte works for Oracle... and he's been saying 'don't rebuild' for years. So did I, and I've been saying it for years, too. Oracle's official documentation also said that it was possible to have a locally-managed SYSTEM tablespace iu 8.1.6... did you take that at face-value, too?

> Sometimes DBAs have nothing to do.

Really?

>At least by doing these things, I
> feel like I'm being proactive, even if the benefit is negligible or
> none. Besides, the system is idle overnight, so why can't I schedule
> these rebuilds? -- they're not locking anybody out or doing harm.

They are flooding your buffer cache with index blocks, the last of which are still there the next morning. So when users return and start doing work, they have to do extra I/O.

They re-compact your indexes... so the next bit of DML on the table causes those indexes to have to start growing again, meaning extent allocations take place which otherwise wouldn't.

They generate redo, which means more archives, which means longer recoveries.

There are a host of reasons why you don't just fiddle with these things to "look" pro-active. Actually being pro-active requires rather more work, and a keener understanding of what is actually going on inside Oracle than Oracle itself will ever 'teach' you.

> Wouldn't a more compact index reduce I/O? In university, I wrote C++
> code to implement b-tree indexes and I'm not sure what you're saying
> is true. The index is always better organized when it is rebuilt by
> scanning all the data in the table.

That's simply not true. Instead of just regurgitating what you learrned on some Oracle course, why don't you spend all that idle time you have doing some actual testing and research? Then you can find out what some of the best luminaries on the Oracle world have been saying for ages (Jonathan, Thomas, Steve etc).

>It depends on the datatype and
> the exact methodology. So while I do understand your point, my point
> is this -- without actually looking inside a block or seeing Oracle's
> code, you're guessing as much as I am.

That's just a recipe for 'abandon hope all ye who enter here'. These things are testable. And reproducible. You don't need to see source code to know it, either. You can measure the impact on the database of any action you take, and you can infer what the source code must say by looking at what the database actually does under any given instruction.

>I'm just following their
> recommendations, and that's all I can do since their source is not
> open.

No, you have the option of reading rather more widely, and thinking more critically, and discovering things for yourself.

To coin a phrase, only trained monkeys take what they are recommended by 'authority' to do at face value.

HJR Received on Sat Nov 08 2003 - 17:23:30 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US