Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2006
    Posts
    119

    Unanswered: is null very slow on DB2

    hi,
    I have a table where two columns are a compound index. When I use "is null" a table scan is performed.

    SELECT * FROM MYTABLE WHERE COL_A is null AND COL_B is null;

    whereas

    SELECT * FROM MYTABLE WHERE COL_A = 0 AND COL_B =0;

    performs no table scan.

    In my table COL_A and COL_B together form an index.

    Any ideas?

    Thanks

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If there are a lot of rows where the values are null, DB2 may find it faster to do a table scan of the entire table. I assume you have done a runstats on the table.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Without runstats, the two queries should have identical access paths.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about this?
    Code:
    SELECT *
     FROM  MYTABLE
     WHERE COL_A is null AND COL_B is null
       OR  COL_A = 0 AND COL_A <> 0
       AND COL_B = 0 AND COL_B <> 0

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I did a small test.

    I couldn't recreate the issue(tablespace scan) after runstats.

    Sorry, I misunderstood the result.
    "col_name is null for Not null column"
    was optimized using not null constraints.
    DB2 generates the answer without reading the actual table.


    Summary of results: Executed after runstats.
    Code:
                        +--------------------------------------------+
                        |       column attributes                    |
    +-------------------+----------+-----------------+---------------+
    | predicates        | Not null | (1 / 1000) null | All rows null |
    +-------------------+----------+-----------------+---------------+
    | col_name is null  | <genrow> | <ixscan>        | <ixscan>      |
    |                   | Ex. 1    | Ex. 3           | Ex. 4         |
    +-------------------+----------+-----------------+---------------+
    | col_name = 1      | <ixscan> | -               | -             |
    |                   | Ex. 2    |                 |               |
    +-------------------+----------+-----------------+---------------+
    Test table and data:
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE
     test_index_nullvalue
    ( id       INTEGER NOT NULL
    , col_int  INTEGER
    , col_null INTEGER
    )
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    /*
     about 30 secs.
    */
    ------------------------------ Commands Entered ------------------------------
    INSERT INTO
    test_index_nullvalue
    WITH
     recurse(id , col_a , col_null) AS (
    VALUES (1 , 1 , NULLIF(0 , 0) )
    UNION ALL
    SELECT id + 1
         , NULLIF( MOD(id + 1 , 1000) , 500 )
         , col_null
     FROM  recurse
     WHERE id < 1000000
    )
    SELECT * FROM recurse
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    SELECT COUNT(*)        count_rows
         , COUNT(id)       count_id
         , COUNT(col_int)  cnt_col_int
         , COUNT(DISTINCT
                 col_int)  distinct_int
         , COUNT(col_null) cnt_col_null
     FROM  test_index_nullvalue
    ;
    ------------------------------------------------------------------------------
    
    COUNT_ROWS  COUNT_ID    CNT_COL_INT DISTINCT_INT CNT_COL_NULL
    ----------- ----------- ----------- ------------ ------------
        1000000     1000000      999000          999            0
    
      1 record(s) selected.
    Add Indexes and Runstats:
    CREATE INDEX test_index_nullvalue@_id ON test_index_nullvalue (id);
    CREATE INDEX test_index_nullvalue@_col_int ON test_index_nullvalue (col_int);
    CREATE INDEX test_index_nullvalue@_col_null ON test_index_nullvalue (col_null);
    RUNSTATS ON TABLE db2admin.test_index_nullvalue FOR INDEXES ALL;

    Result of runstats:
    Code:
    INDEX_NAME                     NLEAF       NLEVELS FIRSTKEYCARD FULLKEYCARD SEQUENTIAL_PAGES DENSITY     INDCARD     CLUSTERFACTOR AVGNLEAFKEYSIZE AVGLEAFKEYSIZE TABLE_NAME                    
    ------------------------------ ----------- ------- ------------ ----------- ---------------- ----------- ----------- ------------- --------------- -------------- ------------------------------
    TEST_INDEX_NULLVALUE@_ID              2062       3      1000000     1000000             2061          98     1000000         -1.00               4              4 DB2ADMIN.TEST_INDEX_NULLVALUE 
    TEST_INDEX_NULLVALUE@_COL_INT         1102       3         1000        1000             1101          96     1000000         -1.00               4              4 DB2ADMIN.TEST_INDEX_NULLVALUE 
    TEST_INDEX_NULLVALUE@_COL_NULL        1101       3            1           1             1100          99     1000000         -1.00               4              4 DB2ADMIN.TEST_INDEX_NULLVALUE
    Example 1: Not null column.
    <genrow>
    Code:
    SELECT id
     FROM  test_index_nullvalue
     WHERE id IS NULL
    ;
    Example 2: Not null column.
    <ixscan>
    Code:
    SELECT id
     FROM  test_index_nullvalue
     WHERE id = 1
    ;
    Example 3: (1 / 1000) rows are null.
    <ixscan>
    Code:
    SELECT COUNT(*)
     FROM  test_index_nullvalue
     WHERE col_int IS NULL
    ;
    Example 4: All rows are null.
    <ixscan>
    Code:
    SELECT COUNT(*)
     FROM  test_index_nullvalue
     WHERE col_null IS NULL
    ;
    Last edited by tonkuma; 08-04-11 at 22:23. Reason: Removed Example 5 to 7. Corrected my misunderstandings for the result.

Posting Permissions

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