Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2005
    Posts
    108

    Unanswered: Why there is a Table Scan?

    Hi All,
    I am running DB2 V8.2 on Linux. I have the following table and indexes:

    Code:
     
    CREATE TABLE T1 (C1 CHAR(1), C2 CHAR(1));
    CREATE INDEX IX1 ON T1(C1);
    CREATE INDEX IX2 ON T1(C2);
    CREATE INDEX IX3 ON T1(C1,C2);
    I ran explain:
    Code:
    explain all for select c1,c2 from t1 where c1 > 'm' order by c2
    Here is part of the output of EXPLAIN:
    Code:
    Original Statement:
    ------------------
    select c1,c2 
    from t1 
    where c1 > 'm' 
    order by c2
     
    Optimized Statement:
    -------------------
    SELECT Q1.C1 AS "C1", Q1.C2 AS "C2" 
    FROM AH_03.T1 AS Q1 
    WHERE ('m' < Q1.C1) 
    ORDER BY Q1.C2
    Access Plan:
    -----------
     Total Cost:   0.106946
     Query Degree:  1
          Rows 
         RETURN 
         (   1) 
          Cost 
           I/O 
           |
           60 
         TBSCAN 
         (   2) 
        0.106946 
            0 
           |
           60 
         SORT   
         (   3) 
        0.106307 
            0 
           |
           60 
         IXSCAN 
         (   4) 
        0.0883347 
            0 
           |
           180 
     INDEX: AH_03    
           IX3 
     
     
    Extended Diagnostic Information:
    --------------------------------
    Diagnostic Identifier:  1
    Diagnostic Details:  EXP0022W  Index has no statistics.  The index
       "AH_03   "."IX3" has not had runstats run on it.
       This can lead to poor cardinality and predicate
       filtering estimates.
    Plan Details:
    -------------
     
     1) RETURN: (Return Result)
      Cumulative Total Cost:   0.106946
      Cumulative CPU Cost:   215633
      Cumulative I/O Cost:   0
      Cumulative Re-Total Cost:  0.0789491
      Cumulative Re-CPU Cost:  159184
      Cumulative Re-I/O Cost:  0
      Cumulative First Row Cost:  0.106946
      Estimated Bufferpool Buffers:  0
      Arguments:
      ---------
      BLDLEVEL: (Build level)
       DB2 v8.1.2.112 : s060429
      STMTHEAP: (Statement heap size)
       4096
      Input Streams:
      -------------
       4) From Operator #2
        Estimated number of rows:  60
        Number of columns:   2
        Subquery predicate ID:   Not Applicable
        Column Names:
        ------------
        +Q2.C2(A)+Q2.C1
     
     2) TBSCAN: (Table Scan)
      Cumulative Total Cost:   0.106946
      Cumulative CPU Cost:   215633
      Cumulative I/O Cost:   0
      Cumulative Re-Total Cost:  0.0789491
      Cumulative Re-CPU Cost:  159184
      Cumulative Re-I/O Cost:  0
      Cumulative First Row Cost:  0.106946
      Estimated Bufferpool Buffers:  0
      Arguments:
      ---------
      MAXPAGES: (Maximum pages for prefetch)
       ALL
      PREFETCH: (Type of Prefetch)
       NONE
      SCANDIR : (Scan Direction)
       FORWARD
      Input Streams:
      -------------
       3) From Operator #3
        Estimated number of rows:  60
        Number of columns:   2
        Subquery predicate ID:   Not Applicable
        Column Names:
        ------------
        +Q1.C2(A)+Q1.C1
     
      Output Streams:
      --------------
       4) To Operator #1
        Estimated number of rows:  60
        Number of columns:   2
        Subquery predicate ID:   Not Applicable
        Column Names:
        ------------
        +Q2.C2(A)+Q2.C1
     
     3) SORT  : (Sort)
      Cumulative Total Cost:   0.106307
      Cumulative CPU Cost:   214346
      Cumulative I/O Cost:   0
      Cumulative Re-Total Cost:  0.0707474
      Cumulative Re-CPU Cost:  142647
      Cumulative Re-I/O Cost:  0
      Cumulative First Row Cost:  0.106307
      Estimated Bufferpool Buffers:  1
      Arguments:
      ---------
      DUPLWARN: (Duplicates Warning flag)
       FALSE
      NUMROWS : (Estimated number of rows)
       60
      ROWWIDTH: (Estimated width of rows)
       8
      SORTKEY : (Sort Key column)
       1: Q1.C2(A)
      TEMPSIZE: (Temporary Table Page Size)
       4096
      UNIQUE  : (Uniqueness required flag)
       FALSE
      Input Streams:
      -------------
       2) From Operator #4
        Estimated number of rows:  60
        Number of columns:   2
        Subquery predicate ID:   Not Applicable
        Column Names:
        ------------
        +Q1.C2+Q1.C1
     
      Output Streams:
      --------------
       3) To Operator #2
        Estimated number of rows:  60
        Number of columns:   2
        Subquery predicate ID:   Not Applicable
        Column Names:
        ------------
        +Q1.C2(A)+Q1.C1
     
     4) IXSCAN: (Index Scan)
      Cumulative Total Cost:   0.0883347
      Cumulative CPU Cost:   178108
      Cumulative I/O Cost:   0
      Cumulative Re-Total Cost:  0.0707474
      Cumulative Re-CPU Cost:  142647
      Cumulative Re-I/O Cost:  0
      Cumulative First Row Cost:  0.0371217
      Estimated Bufferpool Buffers:  1
      Arguments:
      ---------
      MAXPAGES: (Maximum pages for prefetch)
       ALL
      PREFETCH: (Type of Prefetch)
       NONE
      ROWLOCK : (Row Lock intent)
       NEXT KEY SHARE
      SCANDIR : (Scan Direction)
       FORWARD
      TABLOCK : (Table Lock intent)
       INTENT SHARE
      Predicates:
      ----------
      2) Start Key Predicate
       Relational Operator:   Less Than (<)
       Subquery Input Required:  No
       Filter Factor:    0.333333
       Predicate Text:
       --------------
       ('m' < Q1.C1)
     
      Input Streams:
      -------------
       1) From Object AH_03.IX3
        Estimated number of rows:  180
        Number of columns:   3
        Subquery predicate ID:   Not Applicable
        Column Names:
        ------------
        +Q1.$RID$+Q1.C2+Q1.C1
     
      Output Streams:
      --------------
       2) To Operator #3
        Estimated number of rows:  60
        Number of columns:   2
        Subquery predicate ID:   Not Applicable
        Column Names:
        ------------
        +Q1.C2+Q1.C1
    What I don't understand is that there is a TBSCAN after SORT. The first step IXSCAN returns columns C1 and C2. These are exactly all I need except for ordering. So what does TBSCAN do? If DB2 uses TBSCAN, why does it need an IXSCAN at the begining? Note that the TBSCAN costs very little compared to IXSCAN. I understand that I did not run statistics before explain - the plan would be different if I had statistics. I am just curious about the TBSCAN. Please advise. Thank you in advance.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    When DB2 has multiple steps in the access plan and a temporary table (on disk or in memory) is created, then DB2 has to scan the entire results set of the temporary table that has been created. It is not scanning the original table. This is not a problem.

    In your design, you have too many indexes. Index IX1 is redundant since IX3 will work just as well for a query with a predicate that supplies only C1, or both C1 and C2.

    If IX1 happens to be a unique index, then IX3 is redundant.
    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
    Mar 2005
    Posts
    108
    Thanks Marcus_A. Yes, IX1 is redundant. I had IX1 first, but the query was bad. So I created IX3.

    It seems make sense that the TBSCAN is on a temporary table. But, how do you tell that it's on a temporary table? Is the answer by I/O cost? If the I/O cost is 0, it's a memory scan, right? And this kind of TBSCAN indicates that the SORT is not a "piped" sort? Thanks again.
    Last edited by DBA-Jr; 01-24-07 at 17:48.

  4. #4
    Join Date
    Dec 2005
    Posts
    273
    by the way:

    an index on T1 ( C2 , C1 ) might avoid the tablescan

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by DBA-Jr
    It seems make sense that the TBSCAN is on a temporary table. But, how do you tell that it's on a temporary table?
    The TBSCAN sits on top of the SORT. So it scans the sort results.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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