Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369

    Unanswered: COUNT rows - TBSCAN vs IXSCAN

    Could you please explain why the optimizer favors a table scan over an index scan for the query in the attached doc? Why is IXSCAN a lot more expensive?

    With TBSCAN - 405611 I/Os at a cost of 1.40235e+06
    With IXSCAN - 121215 I/Os at a cost of 3.59355e+06


    runstats are current and were done using "runstats on table... with distribution and indexes all"

    DB2 will do a table scan unless I force it to use the index.
    Attached Files Attached Files

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I think it's because the index is non-unique and the column is nullable. As you can see, the index cardinality does not match that of the table itself.

  3. #3
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Sorry, I realized I forgot to unset some registry variables before doing this test. Now, it's doing IXSCAN.


    Original Statement:
    ------------------
    SELECT COUNT(C1)
    FROM DB2INST1.TEST


    Optimized Statement:
    -------------------
    SELECT Q3.$C0
    FROM
    (SELECT COUNT(Q2.C1)
    FROM
    (SELECT Q1.C1
    FROM DB2INST1.TEST AS Q1) AS Q2) AS Q3

    Access Plan:
    -----------
    Total Cost: 3.69872e+06
    Query Degree: 1

    Rows
    RETURN
    ( 1)
    Cost
    I/O
    |
    1
    GRPBY
    ( 2)
    3.69872e+06
    121215
    |
    4.5e+07
    IXSCAN
    ( 3)
    3.24872e+06
    121215
    |
    4.5e+07
    INDEX: DB2INST1
    ITEST
    Q1


    Number of executions = 1
    Number of compilations = 1
    Worst preparation time (ms) = 37
    Best preparation time (ms) = 37
    Internal rows deleted = 0
    Internal rows inserted = 0
    Rows read = 8
    Internal rows updated = 0
    Rows written = 0
    Statement sorts = 0
    Statement sort overflows = 0
    Total sort time = 0
    Buffer pool data logical reads = 21
    Buffer pool data physical reads = 10
    Buffer pool temporary data logical reads = 0
    Buffer pool temporary data physical reads = 0
    Buffer pool index logical reads = 121015
    Buffer pool index physical reads = 202
    Buffer pool temporary index logical reads = 0
    Buffer pool temporary index physical reads = 0
    Buffer pool xda logical reads = 0
    Buffer pool xda physical reads = 0
    Buffer pool temporary xda logical reads = 0
    Buffer pool temporary xda physical reads = 0
    Total execution time (sec.microsec)= 22.137031
    Total user cpu time (sec.microsec) = 13.481659
    Total system cpu time (sec.microsec)= 0.038301
    Total statistic fabrication time (milliseconds) = 0
    Total synchronous runstats time (milliseconds) = 0
    Statement text = SELECT COUNT(C1) FROM DB2INST1.TEST



    I think it's DB2_EXTENDED_OPTIMIZATION=ON that made it choose TBSCAN over IXSCAN.


    Why is the cost of TBSCAN a lot more expensive than IXSCAN? Does it have to do with the number of index level? The number of index keys match the number of rows in the table. Any idea?

  4. #4
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Correction to my previous question:

    Why is the cost of IXSCAN a lot more expensive than TBSCAN?


    4.5e+07
    IXSCAN
    ( 3)
    3.24872e+06
    121215
    |
    4.5e+07
    INDEX: DB2INST1
    ITEST
    Q1


    vs.


    4.5e+07
    TBSCAN
    ( 3)
    1.40235e+06
    405611
    |
    4.5e+07
    TABLE: DB2INST1
    TEST
    Q1

Posting Permissions

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