DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 26

Thread: Each table in a different tablespace

  1. #1
    Join Date
    Oct 2001
    Location
    Kuwait
    Posts
    23

    Question 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

  2. #2
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796

    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."

    Amar's Blog  Get Firefox!

  3. #3
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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"

  4. #4
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    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."

    Amar's Blog  Get Firefox!

  5. #5
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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"

  6. #6
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    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."

    Amar's Blog  Get Firefox!

  7. #7
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    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."

    Amar's Blog  Get Firefox!

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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"

  9. #9
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    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."

    Amar's Blog  Get Firefox!

  10. #10
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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
  •  


Click Here to Expand Forum to Full Width