Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2007
    Posts
    24

    Unanswered: which is faster?

    DB2 for z/OS v9.1:

    On an adequately indexed table with about 7 million rows, which one of these would be faster?

    Code:
    WHERE REC_NAME LIKE 'BEN%'
    Code:
    WHERE REC_NAME BETWEEN 'BEN' AND 'BEN99999999999999999'

  2. #2
    Join Date
    May 2003
    Posts
    113
    Quote Originally Posted by Fumigator
    DB2 for z/OS v9.1:

    On an adequately indexed table with about 7 million rows, which one of these would be faster?

    Code:
    WHERE REC_NAME LIKE 'BEN%'
    Code:
    WHERE REC_NAME BETWEEN 'BEN' AND 'BEN99999999999999999'
    I vote for the 'between' predicate, but please be aware that this two predicate s are different. Unless you are sure about the data won't fall off the between range.

  3. #3
    Join Date
    Mar 2007
    Posts
    24
    Unless you are sure about the data won't fall off the between range.
    I don't follow-- the data is on an EBCDIC mainframe so all values beginning with "BEN" will be snagged with the BEN and the BEN9999999 deal, correct? (I could also right-fill with high-values which would do the same thing)

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I don't know about DB2 for Z/OS, but on LUW the optimizer will turn "LIKE 'BEN%'" into "BETWEEN 'BEN' AND 'BENZZZZZZZ'" automatically.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    May 2003
    Posts
    113
    actually, all the value between 'BEN ' and 'BEN9999999'.
    It is HEX value of the string. the lower key will use 'BEN' and padding with BLANKS. in ebcdic(some of the ebcdic ccsid), blanks are '40x', and '9' s 'F9'x.

    So what you get is between '40'x and 'F9'X. That include all digits,a-z, A-Z. In general case, your between predicate is fine.

    But if your table is unicode/ascii(can be true even your mainframe zos is in ebcdic), a-z, and A-Z will be excluded.


    if REC_NAME is a longer string than ''BEN99999999999999999', a blank will be padded on 'BEN99999999999999999' too.

  6. #6
    Join Date
    May 2003
    Posts
    113
    Quote Originally Posted by n_i
    I don't know about DB2 for Z/OS, but on LUW the optimizer will turn "LIKE 'BEN%'" into "BETWEEN 'BEN' AND 'BENZZZZZZZ'" automatically.
    LUW is in a single encoding system, which is usually unicode/ascii. "BETWEEN 'BEN' AND 'BENZZZZZZZ'" will include 'BEN123' and 'BENABC', but it should not include 'BENabc'.

  7. #7
    Join Date
    Mar 2007
    Posts
    24
    I'll use the BETWEEN then, and to be safe I'll right-fill with x'FF'.

  8. #8
    Join Date
    May 2003
    Posts
    113
    Quote Originally Posted by Fumigator
    I'll use the BETWEEN then, and to be safe I'll right-fill with x'FF'.
    I believe you know this already. just like to make sure that you fill up enough characters. For exam if you column is varchar(5), you need to fill up all 5 characters. otherwise,

    "col between 'BEN' and 'BEN9' " will not give you 'BEN98' back.

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by Fumigator
    DB2 for z/OS v9.1:

    On an adequately indexed table with about 7 million rows, which one of these would be faster?

    Code:
    WHERE REC_NAME LIKE 'BEN%'
    Code:
    WHERE REC_NAME BETWEEN 'BEN' AND 'BEN99999999999999999'
    In the early days of DB2 for MVS, the BETWEEN was definitely faster becasue DB2 calculated the access path at bind time, and DB2 did not know where the % was in the literal string, so it had to assume the worst (if the % is at the front then the b-tree of the index cannot be used).

    However, IBM changed that to evaluate the the location of the % in the literal of a LIKE statement at runtime to correct this problem.

    Most people use dynamic SQL on DB2 for LUW, so DB2 can always evaluate where the % is located at runtime, unless it happens to be a statically bound program like an SQL SP.

    But now, on both Z/OS and LUW, DB2 will choose the same access path for BETWEEN and LIKE (with no leading % sign), although it may take a very small amount of time longer for DB2 to make sure the % is not the leading character of the literal in the LIKE predicate.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  10. #10
    Join Date
    Jan 2003
    Posts
    1,605
    Quote Originally Posted by n_i
    I don't know about DB2 for Z/OS, but on LUW the optimizer will turn "LIKE 'BEN%'" into "BETWEEN 'BEN' AND 'BENZZZZZZZ'" automatically.
    Hi,
    I have tested on DB2/Windows/v8fp9. Access plan:
    Code:
    Original Statement:
    ------------------
    SELECT * 
    FROM ADMIN.TEST 
    WHERE REC_NAME LIKE 'BEN%'
    
    
    Optimized Statement:
    -------------------
    SELECT Q1.REC_NAME AS "REC_NAME" 
    FROM ADMIN.TEST AS Q1 
    WHERE (Q1.REC_NAME LIKE 'BEN%')
    I don't believe DB2/LUW does 'BENZZZZZZZ' as max range. If using code-page different then English the last character in alphabet is not necessarily Z letter. So BEN% is not the same as between rage with maximum BENZZZZZZZ string.
    Hope this helps,
    Grofaty
    Last edited by grofaty; 09-21-07 at 05:20.

  11. #11
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Code:
    C:\Temp>db2level
    DB21085I  Instance "DB2" uses "32" bits and DB2 code release "SQL08024" with
    level identifier "03050106".
    Informational tokens are "DB2 v8.1.11.973", "s060120", "WR21365", and FixPak
    "11".
    Product is installed at "D:\Software\SQLLIB".
    
    
    C:\Temp>db2expln -d XXXXXX -t -q "select username from XXXXXX.users where username like 'BEN%'"
    
    DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991, 2002
    Licensed Material - Program Property of IBM
    IBM DB2 Universal Database SQL Explain Tool
    
    DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991, 2002
    Licensed Material - Program Property of IBM
    IBM DB2 Universal Database SQL Explain Tool
    
    ******************** DYNAMIC ***************************************
    
    ==================== STATEMENT ==========================================
    
            Isolation Level          = Cursor Stability
            Blocking                 = Block Unambiguous Cursors
            Query Optimization Class = 5
    
            Partition Parallel       = No
            Intra-Partition Parallel = No
    
            SQL Path                 = "SYSIBM", "SYSFUN", "SYSPROC", "XXXXXX"
    
    
    SQL Statement:
    
      select username
      from XXXXXX.users
      where username like 'BEN%'
    
    
    Section Code Page = 1252
    
    Estimated Cost = 12.880592
    Estimated Cardinality = 1.000272
    
    Access Table Name = XXXXXX.USERS  ID = 2,957
    |  Index Scan:  Name = XXXXXX.USERS  ID = 1
    |  |  Regular Index (Not Clustered)
    |  |  Index Columns:
    |  |  |  1: USERNAME (Ascending)
    |  #Columns = 1
    |  #Key Columns = 1
    |  |  Start Key: Inclusive Value
    |  |  |  |  1: 'BEN                 ...'
    |  |  Stop Key: Inclusive Value
    |  |  |  |  1: 'BENZZZZZZZZZZZZZZZZZ...'
    |  Index-Only Access
    |  Index Prefetch: None
    |  Lock Intents
    |  |  Table: Intent Share
    |  |  Row  : Next Key Share
    |  Sargable Index Predicate(s)
    |  |  Return Data to Application
    |  |  |  #Columns = 1
    Return Data Completion
    
    End of section
    ---
    "It does not work" is not a valid problem statement.

  12. #12
    Join Date
    Jan 2003
    Posts
    1,605
    n_i,
    on your table there is index! I have executed the same command with index on username:
    Code:
    C:\>db2expln -d sample -t -q "select username from XXXXXX.users where username like
    
    DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991, 2002
    Licensed Material - Program Property of IBM
    IBM DB2 Universal Database SQL Explain Tool
    
    DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991, 2002
    Licensed Material - Program Property of IBM
    IBM DB2 Universal Database SQL Explain Tool
    
    ******************** DYNAMIC ***************************************
    
    ==================== STATEMENT ==========================================
    
            Isolation Level          = Cursor Stability
            Blocking                 = Block Unambiguous Cursors
            Query Optimization Class = 5
    
            Partition Parallel       = No
            Intra-Partition Parallel = No
    
            SQL Path                 = "SYSIBM", "SYSFUN", "SYSPROC", "IGOR"
    
    
    SQL Statement:
    
      select username
      from XXXXXX.users
      where username like 'BEN%'
    
    
    Section Code Page = 1250
    
    Estimated Cost = 12,895358
    Estimated Cardinality = 9,700000
    
    Access Table Name = XXXXXX.USERS  ID = 2,3
    |  Index Scan:  Name = XXX.USERNAME  ID = 1
    |  |  Regular Index (Not Clustered)
    |  |  Index Columns:
    |  |  |  1: USERNAME (Ascending)
    |  #Columns = 1
    |  #Key Columns = 1
    |  |  Start Key: Inclusive Value
    |  |  |  |  1: 'BEN                 '
    |  |  Stop Key: Inclusive Value
    |  |  |  |  1: 'BENŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻ'
    |  Index-Only Access
    |  Index Prefetch: None
    |  Lock Intents
    |  |  Table: Intent Share
    |  |  Row  : Next Key Share
    |  Sargable Index Predicate(s)
    |  |  Return Data to Application
    |  |  |  #Columns = 1
    Return Data Completion
    
    End of section
    It looks like optimizer uses code-page info to get maximum character. In my case the maximum character is Ž (like letter Z but with some extra line at top of letter).

    Interesting... but index must be created.

    Regards,
    Grofaty

Posting Permissions

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