-
index in users tablespace
i want to transfer all the indexes created in USERS tablespace to diff tablespace, how do we do it?.
-
The simplest way is to rebuild them e.g.
Code:
ALTER INDEX xxx REBUILD TABLESPACE xxx
Regards
Jim
Oracle Certified Professional
"Build your reputation by helping other people build theirs."
"Sarcasm may be the lowest form of wit but its still funny"
Click HERE to vist my website!
-
will this operation clear the space alloted in users tablespace?.
-
The space in the tablespace is free, subseqent creates, extends and so on will use it.
Amar
"There is a difference between knowing the path and walking the path."

-
Re: index in users tablespace
Originally posted by fordikon
i want to transfer all the indexes created in USERS tablespace to diff tablespace, how do we do it?.
You know indexes and tables don't have to be separated.... If you are running under a RAID array, or even under different disks arrarys, seperating your indexes and tables will not gain your database any performance benefit. Index reads and and table reads are a sequential.
If you have to separate and are NOT running RAID separate your ojects which are HOT, this might be TABLES contenting with TABLES or INDEXES contenting with INDEXES.
But, there are MANY who will maintain that it IMPERITIVE to separate your indexes and tables... (even Official Oracle Documentation). It's not the case.
Under RAID it comes down to administrative choice, if you want to seperate you indexes for tables, fine, do so, but understand.... it's not a performance benefit.
Cheers,
OCP 8i, 9i DBA
Brisbane Australia
-
grjohnson,
I agree partially, but would put it another way: before LMT, the performance reason to separate "user" objects between tablespaces was to get them on to independant physical disks. If everything is lumped together under one RAID5 array (PLEASE not the log files!) nothing is gained. If you can afford several RAID1 pairs, then the index/data split is one way to carve up the db - perhaps the best if you have no clue about what may be the HOT areas.
(You said: "Index reads and and table reads are a sequential" - I suspect what you mean and what I understand from this are different!)
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
check this one out, here the oracle experts are talking on the same topic.
http://asktom.oracle.com/~tkyte/google.html
i think this link was posted by slimdave a few days ago
Amar
"There is a difference between knowing the path and walking the path."

-
Originally posted by adewri
here the oracle experts are talking on the same topic.
This thread is an excellent read (may take you a while, it's quite long) But the information provided by Howard Rogers and the examples on HOT segmets provided by Jonathan Lewis, are invaluable to dispelling the myth of INDEX and TABLE segment separation.
OCP 8i, 9i DBA
Brisbane Australia
-
now if only we could dispel that "high cache hit ratio=good, low cache hit ratio = bad" myth.
-
I'm with you on that one also slimdave.
How on UNIX which has it's OWN buffer cache and the Hard Disks which also have their own buffer cache can you take a Physical Read as READING from disk???????
OCP 8i, 9i DBA
Brisbane Australia
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|