number of tables in Datafiles
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: number of tables in Datafiles

  1. #1
    Join Date
    Jan 2002
    Posts
    58
    Is there a benefit to having one table per tablespace on the same disk, instead of having multiple tables in one tablespace. Is their a performance issue with this. thanks


  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    On the same disk? No.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Apr 2002
    Location
    Phoenix, AZ
    Posts
    175
    Performance issues related to No of Tables in a TBS is not defined as such. It might be conceptually wrong to ask the question but ..

    TBS has Datafile that could be spread on to differen Disks.
    There might be some performance degradation if a Table in a TBS is spread onto too many datafiles.

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by patnams
    There might be some performance degradation if a Table in a TBS is spread onto too many datafiles.
    How so?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Originally posted by patnams
    There might be some performance degradation if a Table in a TBS is spread onto too many datafiles.
    you mean the other way round?

  6. #6
    Join Date
    Apr 2002
    Location
    Phoenix, AZ
    Posts
    175
    What I meant was that when a table exists on multiple datafiles, it also means that it is fragmented that many times as the number of disks.

    (both Clustering and Indexed Organized Tables are based on concept of trying to storing data in nearly one physical location )

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by patnams
    What I meant was that when a table exists on multiple datafiles, it also means that it is fragmented that many times as the number of disks.
    I definitely disagree.

    Just because a table is in multiple extents does not mean it is fragmented.

    In addition, when you have multiple devices spinning to retrieve your data you will get much better performance then when everything is on one device.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Originally posted by patnams
    What I meant was that when a table exists on multiple datafiles, it also means that it is fragmented that many times as the number of disks.

    (both Clustering and Indexed Organized Tables are based on concept of trying to storing data in nearly one physical location )
    seems like you still belive this old myth of multiple extents is bad....

  9. #9
    Join Date
    Apr 2002
    Location
    Phoenix, AZ
    Posts
    175
    With so may moving parts available these, it is surely based on old myth.

    I've done a lot of experimenting with fragmentation before, but never could really attribute performance to fragmentation.

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