Re: The Admin's point of view on DB users best practices

"Howard J. Rogers" <hjr_at_dizwell.com> wrote:

>Random thoughts in line.
>HJR
>--
>--------------------------------------------
>Oracle Insights: www.dizwell.com

>> 3) Create indexes based on the most frequently used predicate columns.
>
>Yes, but within that... what about concatenated indexes? What about the use
>of skip-scanning and key compression, which together suggest that if there's
>a choice of leading edge to a concatenated index, the least cardinal one
>should go first.

Allright, I will take that into consideration.

>> 8) If a fresh table or materialized view is created and populated,
>> gather statistics immediately afterwards (the Admin will do it sure,
>> but only later or on weekends).
>
>I'm not so sure about this. In 9iR2, we can do dynamic sampling for things
>that don't have their own statistics, so the lack of statistics does not
>absolutely have to be the show-stopper it might have been. On the other
>hand, having people fire off full scans as statistics are collected at a
>time and place of *their* choice might not be such good news for other
>people attempting to use the database. Statistics collection should surely
>be a planned activity, not a knee-jerk response to the fact that a segment's
>just been created.

Actually I already set dynamic_sampling=true in our 9i DB. Just forgot.. You are right, it is to decide from case to case if statistics should necessarily be collected immediately or at query time.

By the way: will SYS objects be affected by dynamic_sampling? AFAIK there should be no statistics of SYS objects.

>> 9) If a table or materialized view is recreated or repopulated with a
>> similar amount of data, do not gather any statistics. Rather save
>> statistics data from the table prior to the recreation and transfer
>> them back again afterwards.
>>
>> 10) Avoid right-handed indexes based on a growing sequence.
>
>Frequently, you won't have a choice about whether the sequence is indexed or
>not. The issue is not "avoid the index", but how best to avoid the
>performance issues that may arise from such an index. Presumably you will
>discuss reverse key indexes and hash partitioning?

Good point, but hash partitioning (or partitioning in general) is kind of too advanced for these people; that is more an Admin stuff - just did that on a 7 Mio. rows table about ten days ago.

>> 23) The only tablespace that should be explicitly named is the one for
>> indexes.
>
>This one I just don't get. Are you still talking about when you create
>segments? Why should indexes be treated any differently from any other form
>of segment? Segments of whatever type should always be housed in an
>explicitly-named tablespace, because relying on users' default tablespaces
>is dangerous. And why separate indexes into a separate tablespace anyway,
>just because they are an index??!!! ;-)

I will have to correct me: "The only TS that is allowed to be named explicitly is INDEX". This is due to historical reasons.

In my experience, one can not rely on people using the right TS. If necessary, the Admin will move things around.
>> a) Guidelines for use of temporary tables, examples (improving
>> performance).
>
>Don't, is my advice, generally. If you're going to use a temporary table as
>a form of temporary de-normalisation, I'd consider a materialised view
>instead, though it would depend upon the circumstances. But there are many
>ways to physically de-normalise data and they should all be investigated.

Mat. views are already being used to keep denormalized data.

What I meant was *temporary* tables with the lifetime of a session or a statement.

>Byt the way... I don't recall seeing a mention of key compression on
>indexes. Should always be looked at, even if only to dismiss it as
>non-viable.

What is their main advantage: space or performance?

>I would also suggest that designers should nearly always make their unique
>and primary key constraints deferrable, even if not deferred. Saves a lot of
>I/O when indexes don't disappear without warning.

I don't quite understand the implication of not disappearing indexes here. Could you elaborate?

Thanks a lot
Rick Denoire Received on Sun Feb 15 2004 - 08:57:35 CST