is null very slow on DB2

Breako · #1 (**permalink**) 08-04-11, 12:48

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

Marcus_A · #2 (**permalink**) 08-04-11, 12:57

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.

Peter.Vanroose · #3 (**permalink**) 08-04-11, 14:12

Without runstats, the two queries should have identical access paths.

tonkuma · #4 (**permalink**) 08-04-11, 19:38

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

tonkuma · #5 (**permalink**) 08-04-11, 20:54

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
;

Internet Services

Web Development

Digital Marketing

Consumer Tech