Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557

    Question Unanswered: Performance difference between drop tbs vs drop table

    I have done an unthinkable and read the manual on tablespaces. And I have stumbled on something that I can not find an explanation for and hope that you guys can help me out here. here is what I found.

    This topic can be found in: Administration Guide: Planning

    If you need to drop and redefine a particular table often, you may want to define the table in its own table space, because it is more efficient to drop a DMS table space than it is to drop a table.

    Question is:"Why is it faster to drop a table space with the table in it, then simply drop redefine a particular table?"

    I know it got to be very simple, but I am drawing a blank on it.

    Thank you in advance.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  2. #2
    Join Date
    Apr 2008
    Posts
    39
    When we drop a table space, it will also drop all the tables as long as it does not have other related objects in other tablespaces(like data in one table space and indexes in another table spaces).

    When we drop a table space, DB2 does not log all of the row and page deletions as it does for a drop table operation. Hence dropping a table space is much efficient than dropping a table.

    Hope this helps.

    Thanks,
    Madhavi.

  3. #3
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    right, that I am aware of. I was just wondering if I was missing some other point.

    Thank you.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •