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: Granting table privileges

Re: Granting table privileges

From: Peter <no_email_at_no_email.com>
Date: Thu, 10 Apr 2003 11:20:33 +1000
Message-ID: <b72guq$rch$1@bunyip.cc.uq.edu.au>


You live and learn :) and I guess learning is what makes a good DBA.

I guess if people are aware that by default, separating tables and indexes is not necessary then that is good.

From an administrative point of view it does quite often make things easier and as database administrators this can sometimes be quite important.

The bottom line is if you are having performance issues then tune your database to fix it; find the hot spots and take appropriate action.

"Howard J. Rogers" <howardjr20002_at_yahoo.com.au> wrote in message news:BUQka.10233$1s1.169729_at_newsfeeds.bigpond.com...
> 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 - 20:20:33 CDT

Original text of this message

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