Re: Separating data, index objects

From: DA Morgan <damorgan_at_psoug.org>
Date: Mon, 04 Jul 2005 14:56:27 -0700
Message-ID: <1120514204.987114@yasure>

Mark A wrote:

> <qazmlp1209_at_rediffmail.com> wrote in message 
> news:1120490229.230731.282800_at_f14g2000cwb.googlegroups.com...
> 

>>This is for Oracle version:9i.
>>
>>I understood that, separating data and index objects does not have much
>>impact on improving the performance. But, still I wanted to know it
>>just for information purpose.
>>

> 
> Depends on your bufferpool hit ratio (which depends mostly on the size of 
> bufferpool relative to total amount of data/indexes). 

What does this have to do with the OP's question? And what is the source of the information?

Separating them has no impact on performance but makes using transportable tablespaces a bit more complex.

To separate them just create them with separate TABLESPACE clauses.

CREATE TABLE t (
pid NUMBER(5))
TABLESPACE tbspdata;

ALTER TABLE t
ADD CONSTRAINT pk_t
PRIMARY KEY (pid)
USING INDEX
PCTFREE 0
TABLESPACE tbspindx;

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)

Received on Mon Jul 04 2005 - 16:56:27 CDT