×

Loading...
Ad by
  • 推荐 OXIO 加拿大高速网络,最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务
Ad by
  • 推荐 OXIO 加拿大高速网络,最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务

DB2 on UNIX: How to improve the performance of MERGE

I need to MERGE a small table S(0.25M records) to a huge table T(39M records), they join on the composite primary key, both tables have expactly same columns of the composite primary key.

But the MERGE is aborted with "File system full" error message, the reason is because of the table-scans of the huge table and it uses lots of temporary space.

The huge table is a partitioned table, the data will be merged to the last partition of the huge table.

How can I avoid the table-scan of the huge table? Should I drop the Primary Key of the small table?

Any suggestion or idea are welcome!
Report

Replies, comments and Discussions:

  • 工作学习 / 学科技术讨论 / DB2 on UNIX: How to improve the performance of MERGE
    I need to MERGE a small table S(0.25M records) to a huge table T(39M records), they join on the composite primary key, both tables have expactly same columns of the composite primary key.

    But the MERGE is aborted with "File system full" error message, the reason is because of the table-scans of the huge table and it uses lots of temporary space.

    The huge table is a partitioned table, the data will be merged to the last partition of the huge table.

    How can I avoid the table-scan of the huge table? Should I drop the Primary Key of the small table?

    Any suggestion or idea are welcome!
    • If you have partitioned field in condition, it should avoid scan whole table.
      • Thanks! The partition fields are in the condition, but the target table(huge table) is still scaned. I removed the ORDER BY from SELECT statement for source table, index access to target table is applied. Just don't know why.
        • Then you may need give more spare space to the database server.
          • Good. It's great remind.