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: separate data/inidex

Re: separate data/inidex

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 19 Apr 2002 15:44:37 -0700
Message-ID: <a9q6kl0peo@drn.newsguy.com>


In article <3CC07A06.1A819B83_at_saic.com>, "Guy says...
>
>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.
>

If you

o have a single user system
o with no disk arrays
o have no other files at all on this disk o use raw so that you ->
o don't have anything buffering
o are running in a dust free laboratory...... o have a separate disk for each and every table/index in your system o .....

(all of the above and others that could be added must be ANDED together)

I think you see. The argument from above was valid in 1990 maybe but in 2002 it is really not anymore. with raid arrays, logical volume managers, striping, more then one user on the system, 36gig disks, etc etc etc -- it just doesn't matter any more.

Just did a benchmark. Had about 9 files, 3 tablespaces. Tablespaces were named "small", "medium" and "large". Indexes and data intermixed. One mount point, one virtual file system. Indexes and data just sort of sitting where ever. No point in spreading it "out". The underlying file system software did all of that. Never did push the file system over the edge...

>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 remove
>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 tablespaces) can improve
>performance by removing physical I/O contention.
>
>"Howard J. Rogers" wrote:
>
>> There is precisely zero physical reason for separating indexes and tables,
>> at least in routine database configurations. No performance gain 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 move'
>> 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)
>> > >
>> > >
>> >
>> >
>

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 
Received on Fri Apr 19 2002 - 17:44:37 CDT

Original text of this message

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