-
Each table in a different tablespace
Hi all ,
Recently we got a new Oracle system from a third party
vendor. I see each table been put in a different tablespace.
Is there any real benifit in doing so other than restore of
one table may be easier.
This idea I have seen in DB2 systems. Can someone give more
suggestion why Vendors go for such logic.
Thanking in Advance
Oracle DBA
National Bank Of Kuwait
-
Re: Each table in a different tablespace
Originally posted by gsmathew
Recently we got a new Oracle system from a third party
vendor. I see each table been put in a different tablespace.
You should put this question to your third party vendor... and ask for explanation as to why this has been done...
Having large tables in their own tablespaces is fine but even small tables having their own tablespace is not a good idea. In fact this can lead to lot of maintainance work and wastage of space.
Amar
"There is a difference between knowing the path and walking the path."

-
Probabaly to speed up acess.
BTW how many tables u have in the system ( user tables )
and u have so many datafile ( min ).
hence lots of mantainence is required.
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Originally posted by abhaysk
Probabaly to speed up acess.
Unless the datafiles are spread accross different disks...
Amar
"There is a difference between knowing the path and walking the path."

-
Originally posted by adewri
Unless the datafiles are spread accross different disks...
Not necessarily.
Atleast in this type of case.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Originally posted by abhaysk
Not necessarily.
Atleast in this type of case.
And why not ??
If all the datafiles are going to be on the same disk, then keeping tables in different datafiles for speed is of no use as the I/O in both the cases is going to be same...
So unless datafiles are spread across different disks its no use from better speed prespective as Abhay has suggested.
Amar
"There is a difference between knowing the path and walking the path."

-
Originally posted by abhaysk
Atleast in this type of case.
And what is the type here ??
Amar
"There is a difference between knowing the path and walking the path."

-
Originally posted by adewri
And why not ??
If all the datafiles are going to be on the same disk, then keeping tables in different datafiles for speed is of no use as the I/O in both the cases is going to be same...
As far as I/O is concerened, Yes it wont help.
But take a senariao having say 10 big tables of about (200 M) data in each table & in same tablespace X with capacity 3 GB ( 2 datafile ).
And say 10 different tablespaces for 10 tables with 300 M size ( 1 datafile ).
Now for the query "Select * from T1" ( FTS )... for the OS to load data into cache/paging mem or watever and to display it would be rather be faster for 2 case mentioned above than case 1.
In this sense i meant fast acess and not I/O
There is no overhead for any thing in datafile to search and pull data.
Hope i am correct. If not i dunno OS or oracle then.
Any ways its my logical thought.
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Originally posted by abhaysk
There is no overhead for any thing in datafile to search and pull data.
IMHO over head will be same in both cases.
Why do you say "There is no overhead for any thing in datafile to search and pull data". Put some light on this...
Amar
"There is a difference between knowing the path and walking the path."

-
Originally posted by adewri
Why do you say "There is no overhead for any thing in datafile to search and pull data". Put some light on this...
As i mentioned below of 2 scenarios.
Case 1:
consider its DW or any OLTP. inserts , updates on diff tables will be goining on daily. and data in the datafile for different tables will be spread like any thing.
so data for table T1 will exist in mem locations (disk level)
N+x1, N+x2, N+x3 and so
so goes for table T2 ( N+y1, N+y2 .. )
U never know where in the huge datafile ( of size 1.5GB ) will be stored
in such scinerios, since rows in table are not continuous on hard disk...the time required to bring them to cache mem wud take X milli secs say...
Case 2:
U have only 1 datafile with 300M as i mentioned ( So OS would allocate a contigious block on Hard disk to it )
Now the rows in one particular table would be evenly distributed at any given point of time.
any say to retrive the data from harddisk to cache mem would take y milli secs ( i hope this would be in micro secs for this case ).
So now the time Y would be obiviously less than X.
if you talk by oracles logical mem structure, u really wont get the difference ( I blv ).
Amar correct me if i am wrong.
Thanks
Abahy
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
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
|