Re: Granting table privileges

OK, here we go (hint: you might want to search Google for a thread called 'Oracle Myths' from sometime in the last half of last year)...

"Peter" <no_email_at_no_email.com> wrote in message news:b706qa$5so$1_at_bunyip.cc.uq.edu.au...
> I'm not experienced enough to know exactly how much benefit it would be,
but
> surely if you have a table on one physical mountpoint and the index to the
> table on another physical mountpoint then this would reduce contention.

Only if you presume that a table and its index are liable to contend in the first place. But they don't. If you update a record in a table, then Oracle updates the table and *then* makes the corresponding change to the index. The segment modifications are *serialised*, therefore, and cannot possibly contend.

Now your change to the table might contend with my change to the index, but that just means two segments are having contention issues. It's just as likely to be the case that my update to table A contends with your updates to table B. It's the contention issue you need to deal with, and the fact that it may or may not involve indexes has nothing to do with them being indexes.

> Obviously, you would seek to reduce I/O hotspots. Just because you have
the
> option to use a default index tablespace, doesn't mean you have to use it.
> Having the option to set a different index tablespace to your default
> tablespace just means that you have extra flexibility.

No, it's worse than that. It would imply that indexes need especially kept separate from tables. Which (a) isn't true and (b) misses the real point: deal with contention, however it arises, regardless of the nature of the segments involved. Having a default index tablespace would suggest something special about indexes.

I'll buy the default index tablespace if we also have a default IOT tablespace, a default cluster tablespace, a default materialised view tablespace and so on.

There are default undo and temporary tablespaces because those segments are subject to especially heavy I/O. The same is not the case with indexes, intrinsically.

>Oracle obviously has
> a great deal of flexibility currently. Having your indexes in different
> tablespaces also allows you greater flexibility when it comes to archiving
> as well. If disk space is a consideration then why bother archiving an
index
> when it is redundant information? Simply being able to drop the indexes
and
> then the tablespace means greater flexibility.

Now that's a different issue. Practically, yes, you may well separate indexes from tables for these administrative conveniences. And it's possible that those administrative benefits might warrant the creation of a default index tablespace. But the Oracle path has been clear: separate tablespaces are generally provided by default when there's a *performance* implication.

>Over half of one our
> databases is taken up in indexes, this means a lot of time is spent
> archiving indexes when they could simply be dropped and then archived. The
> time used to rebuild the index isn't an issue if the database had to be
> restored.

As I say, that's a good argument for separating the two sorts of segments, but it isn't one of performance.

On a separate issue, though related, it's not quite as easy just to drop a bunch of indexes and re-create them as you might think. Indexes which are used to enforce integrity constraints can't be dropped, for example, unless you first disable the constraint... and until 9i, disabling the constraint means it's open slather on the table: you pray your users don't introdduce violating records, but you can't be sure. And then there are indexes associated with foreign key constraints (still generally needed in 9i, despite what the doco might suggest)... remove them, and hideous table locking issues arise. In general, it's probably better to archive and restore/recover indexes than drop-and-re-create them. Usually.

But as for performance... not an issue, per se.

Regards
HJR Received on Wed Apr 09 2003 - 03:43:36 CDT