| PK Index on different TableSpace [message #54457] |
Tue, 19 November 2002 06:30  |
Yuvarajan JT
Messages: 9
Registered: February 2002
|
Junior Member |
|
|
Is it good to move a PK Index on to a seperate Index tablespace?
What is the benefit in doing the same?
What will be the problem, if PK Index is in the same tablespace as that of the table.
Please Help,
Yuvarajan JT
|
|
|
|
|
|
| Re: PK Index on different TableSpace [message #54477 is a reply to message #54457] |
Tue, 19 November 2002 12:08  |
Trifon Anguelov
Messages: 514
Registered: June 2002
|
Senior Member |
|
|
It is advisable to separate table from index data on separate disk drives and if possible on separate controllers. But again depends on your disk storage, RAID level, index usage.
If you have an index on a table then both the read and write operations will have to access both structures in the same transaction. But if you place the tables are stored on different disks separated from index datafiles, then the OS do simultaneous operations reducing the contention in the I/O system.
See this arguments regarding separating the both objects from Here
Hope that helps,
clio_usa
OCP - DBA
Visit our Web site
|
|
|
|
|
|
| Re: PK Index on different TableSpace [message #54500 is a reply to message #54457] |
Wed, 20 November 2002 04:18 |
Yuvarajan JT
Messages: 9
Registered: February 2002
|
Junior Member |
|
|
Is this also applicable to the PKey Index?
In general PK Indexes are created with the CreateTable Script.
Do I have to do something like the one below
1. Create Table T(ID, Name) Tablespace TEST;
2. Create Index on ID of Table T Tablespace INDX;
3. Alter table add constraint ID = PKey
instead of
1. Create Table T(ID = PKey, Name) Tablespace Test;
Are you recommending PKey Index on seperate Tblsp?
Yuvarajan JT
|
|
|
|
| Re: PK Index on different TableSpace [message #54520 is a reply to message #54457] |
Wed, 20 November 2002 08:55 |
Sanjay
Messages: 236
Registered: July 2000
|
Senior Member |
|
|
I always use a simple create table script and then add the PK constraint and put it in another tablespace.
Example:
CREATE TABLE CUSTOMER_DIM (
REGION_CD NUMBER (4) NOT NULL,
CUST_NUM CHAR (10) NOT NULL,
CUST_FIRST_NAME VARCHAR2 (20),
CUST_LAST_NAME VARCHAR2 (20),
CUST_COMPANY_NAME VARCHAR2 (35),
FED_TAX_ID CHAR (9),
CHANGE_DT DATE)
TABLESPACE DM_DATA1 NOLOGGING
STORAGE ( INITIAL 100K NEXT 100K PCTINCREASE 0 MAXEXTENTS 1024);
ALTER TABLE CUSTOMER_DIM
ADD CONSTRAINT XPKCUSTOMER_DIM
PRIMARY KEY (REGION_CD, CUST_NUM)
USING INDEX
TABLESPACE DM_IDX1 STORAGE(INITIAL 100K NEXT 100K PCTINCREASE 0 MAXEXTENTS 1024);
CREATE INDEX CUSTOMER_DIM_IDX1 ON
CUSTOMER_DIM(CUST_NUM, CUST_LAST_NAME, CUST_FIRST_NAME)
TABLESPACE DM_IDX1 STORAGE(INITIAL 100K NEXT 100K PCTINCREASE 0 MAXEXTENTS 1024);
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
