Results 1 to 2 of 2
  1. #1
    Join Date
    May 2005
    Posts
    4

    Question Unanswered: DB2 Performance Issues / tablespace /OR <-> IN, BETWEEN

    Hello all,

    Not being a experienced db admin I am looking for some help on the following questions.

    1.) Is it possible to gain performance by replacing OR by IN or maybe by BETWEEN in the where clause. I don't think so, because the optimizer should
    do what its name suggests: optimize my queries.

    2.) I heard that creating many tables in one tablespace decreases performance because there can only be one active access to one tablespace
    at a given time. If the tables are created in different tablespaces access can take in parallel. Is that true ?

    Any help would be highly appreciated.
    A link to a online manual regarding these topics would be great.

    Many thanks in advance
    mike

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by nachtvogel
    1.) Is it possible to gain performance by replacing OR by IN or maybe by BETWEEN in the where clause.
    While OR and IN predicates may often result in the same execution plan, BETWEEN will most certainly produce a different plan. The best way to prove it is to try.

    Quote Originally Posted by nachtvogel
    2.) I heard that creating many tables in one tablespace decreases performance because there can only be one active access to one tablespace
    at a given time. If the tables are created in different tablespaces access can take in parallel. Is that true ?
    Yes and no. It may have been somewhat true for DB2 on Z/OS until recently but in general it is not. Tabespaces are logical constructs, while I/O performance depends on physical layout of tablespace containers. You can put containers for multiple tablespaces on a single physical device and thus create a bottleneck. Alternatively, you can have a single tablespace with a single container created on a dual-channel array consisting of a few dozen physical disks and not worry about I/O performance at all.

Posting Permissions

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