| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: separate data/inidex
RSH wrote:
> This is a very sophomoric dissection, if I may call it that, of this
> issue.
>
> You know full well that spindle diversity, particularly between (in my
> triage) tables and indices, and then secondly, further separation of
> concomitantly used tables and their indices, is a key to performance.
> Further, of course, making sure tedious items like REDO and TEMP don't
> caught in the crossfire.
>
> It is a trite and juvenile statement in asserting "well, then, every table
> should have its own tablespace", etc. A careful analysis of just what is
> being used, how much, by whom, in what volume, and when, is how I plan out
> my storage on a physical and logical basis.
>
> For example, flat tables loaded by SQL*Loader should reside separately
> from their derivative tables. It goes without saying that the residence of
> the flat ASCII feeder files should not be on the same spindles, either.
> The derivative tables and the tablespaces that hold them should be
> diversified based on their volume of concurrent demand, and needless to
> say, the tablespaces holding their indices ought not to be on the same
> spindles. Of course, if one has a 95% DSS application, the worry of
> simultaneous update and read activity and disk heads flying all over the
> place is not as much of a concern. And with humungous DB_BLOCK_BUFFERS and
> DB_BLOCK_SIZE and the rest, the performance hit would not be too bad.
>
> SYSTEM, TEMP, and the REDO log files should be judiciously isolated from
> this madness on other devices.
>
> Of course, if you have only one disk or two, you are quite right in saying
> it wouldn't make much difference, apart from the fact that defining the
> tablespaces now, despite their residence, would make rebuilding the
> database on a larger platform, with more diverse storage available, would
> be eased greatly.
>
> I guess I missed the part where the original poster of this question said
> that only one session / connection to the database would be in use. Lord
> knows, I probably did, in which case I apologize profusely.
>
> RSH.
>
> "Howard J. Rogers" <dba_at_hjrdba.com> wrote in message
> news:a9r92f$vjm$1_at_lust.ihug.co.nz...
>> Guy D. Kuhn wrote: >> >> > There is a very "physical" reason for separating indexes and tables, >> > naming >> > disk heads. They can only be over one disk cylinder at a time. If >> > the disk is reading the index, it can not be reading the data block. >> >> There is ZERO physical reason for separating indexes and tables, period. >> >> > >> > If you only have one session accessing the table at a time, no problem. >> > Likewise, if you have enough memory for enough db_block_buffers to
>> > contention, no problem. >> > >> > But since an index access requires reading the index at least twice and >> > the data block at once, placing them on different disks (not
>> > can improve performance by removing physical I/O contention. >> >> >> Not true. The only reason for separating indexes and tables into separate >> tablespaces is management issues. Anything else is just buying in to old >> myths. >> >> On this basis, every damn table needs to be in its own tablespace on its >> own disk. After all, you've got lots of tables, and each of them are >> being accessed simultaneously (never mind that index accesses and table >> accesses are not simultaneous). So if you permit two tables to reside in >> the same tablespace, why not a table and its index??? >> >> Performance-wise, there's nothing in it. >> >> Regards >> HJR >> >> >> > >> > "Howard J. Rogers" wrote: >> > >> >> There is precisely zero physical reason for separating indexes and >> >> tables, at least in routine database configurations. No performance
>> >> arises if >> >> you do. There are real *management* issues (tables *need* backing up, >> >> indexes don't). And the rebuild command is fine to move the indexes >> >> somewhere else if you decide to do it; but then the 'alter table X
>> >> command will do much the same by shunting the tables off somewhere >> >> new, too. >> >> >> >> Regards >> >> HJR >> >> >> >> "Troy Meyerink" <meyerink_at_usgs.gov> wrote in message >> >> news:Gus70n.CA3_at_igsrsparc2.er.usgs.gov... >> >> > You need to create an index tablespace and then move the existing >> >> > indexes >> >> by >> >> > issuing an >> >> > 'alter index rebuild' statement. >> >> > >> >> > Troy Meyerink >> >> > Oracle DBA >> >> > Raytheon >> >> > >> >> > >> >> > "Titi" <thierry.constant2_at_wanadoo.fr> wrote in message >> >> > news:3cbf2607$0$15182$626a54ce_at_news.free.fr... >> >> > > Hi, >> >> > > >> >> > > For a user, I have data and index in one tablespace. >> >> > > I want to separate data and index ( good idea ??) >> >> > > >> >> > > How can do that ??? >> >> > > >> >> > > Thanks in advance ... >> >> > > ( oracle 8.1.7) >> >> > > >> >> > > >> >> > >> >> > >>
You seem to be getting rather worked up about something, and I can't think why.
You assert there is a performance improvement in separating indexes from tables by housing them in separate tablespaces and placing the associated datafiles on separate disks.
For that to make a performance difference, you must postulate that the table and the index are accessed simultaneously - because if they are accessed serially (ie, one after the other), then housing them on the same disk cannot induce performance woes.
Guess what? They are accessed serially.
Don't take my word for it: test it for yourself. Don't forget to post the results back here. Also have a look at what Thomas Kyte wrote in this same thread... he essentially agreed that the separation of indexes from their tables is a management nicety, not a performance necessity.
Regards
HJR
Received on Sun Apr 21 2002 - 02:32:43 CDT
 |
 |