Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2006
    Posts
    2

    Unanswered: SELECT from VIEW with UNION ALL goes for a Tablespace Scan instead of using Indexes

    Im supporting DB2 v7.1 on OS/390.

    SELECT from VIEWs based on UNION ALL of tables is not using Indexes available on the base tables, but instead going for a tablespace scan. EXPLAIN as well as TMON confirm the tablespace scan.

    CREATE INDEX T1IX1 ON TABLE1(COLA)
    ;
    CREATE INDEX T2IX1 ON TABLE2(COLA)
    ;
    CREATE VIEW VIEW1
    AS
    (SELECT * FROM TABLE1
    UNION ALL
    SELECT * FROM TABLE2
    )
    ;
    SELECT COLA FROM VIEW1
    WHERE COLA = ?
    ;

    TABLE1 and TABLE2 have INDEXes on COLA and runstats are current. These tables have around 10 million records each and the query goes for a tablespace scan and does not use the Indexes on COLA. Any joins on this VIEW based on COLA also go for tablespace scan causing a big performance issue.

    I did some research and found that its a known problem with v7.1 and IBM has provided fix in the form of (1) PTF UQ62215 for APAR PQ55393 and (2) PTF UQ54302 for APAR PQ47178. Here are the links:

    http://www-1.ibm.com/support/docview...id=swg1PQ55393
    http://www-1.ibm.com/support/docview...id=swg1PQ47178

    The real problem, however, is that our System folks have confirmed that we are already at a higher PTF level with FMID HDB7710 which is inclusive of PTFs UQ62215 & UQ54302.

    Can anyone please help to figure out what could be missing here. The PTF level of running DB2 was found out by the system folks using SMP/E, though, when I do DIAGNOSE DISPLAY MEPL, I dont see these PTFs in there.

    Are there any other PTFs that needs to go on top of this? Or is there some global parameter/ setting that would control this?

    Any thoughts would be highly appreciated.

    Thanks,

  2. #2
    Join Date
    Dec 2004
    Posts
    7

    Tablespace Scan

    Hi,

    How selective is the value you're using for the variable? If it spans more than (roughly) 20% of the table, DB2 will probably not bother to use the index and shoot for a TS scan instead.

    Best regards,

    Willy

    Quote Originally Posted by SA_KU2000
    Im supporting DB2 v7.1 on OS/390.

    SELECT from VIEWs based on UNION ALL of tables is not using Indexes available on the base tables, but instead going for a tablespace scan. EXPLAIN as well as TMON confirm the tablespace scan.

    CREATE INDEX T1IX1 ON TABLE1(COLA)
    ;
    CREATE INDEX T2IX1 ON TABLE2(COLA)
    ;
    CREATE VIEW VIEW1
    AS
    (SELECT * FROM TABLE1
    UNION ALL
    SELECT * FROM TABLE2
    )
    ;
    SELECT COLA FROM VIEW1
    WHERE COLA = ?
    ;

    TABLE1 and TABLE2 have INDEXes on COLA and runstats are current. These tables have around 10 million records each and the query goes for a tablespace scan and does not use the Indexes on COLA. Any joins on this VIEW based on COLA also go for tablespace scan causing a big performance issue.

    I did some research and found that its a known problem with v7.1 and IBM has provided fix in the form of (1) PTF UQ62215 for APAR PQ55393 and (2) PTF UQ54302 for APAR PQ47178. Here are the links:

    http://www-1.ibm.com/support/docview...id=swg1PQ55393
    http://www-1.ibm.com/support/docview...id=swg1PQ47178

    The real problem, however, is that our System folks have confirmed that we are already at a higher PTF level with FMID HDB7710 which is inclusive of PTFs UQ62215 & UQ54302.

    Can anyone please help to figure out what could be missing here. The PTF level of running DB2 was found out by the system folks using SMP/E, though, when I do DIAGNOSE DISPLAY MEPL, I dont see these PTFs in there.

    Are there any other PTFs that needs to go on top of this? Or is there some global parameter/ setting that would control this?

    Any thoughts would be highly appreciated.

    Thanks,

  3. #3
    Join Date
    Jul 2006
    Posts
    2
    Hi, Thanks for your response.

    This COLA is part of primary key and has a very high cardinality. When the SELECT is executed only againt TABLE1 or TABLE2, it does use INDEXes and responds in sub-select.

    Regards,

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Have you raised this issue with IBM folks ?

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Dec 2004
    Posts
    7

    Tablespace Scan

    Could we have a look at the table and index definitions? An extract of the stats from the catalog would also be helpful.

    Thx!

    Willy

Posting Permissions

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