| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
09-20-07, 13:52
|
|
Registered User
|
|
Join Date: Mar 2007
Posts: 24
|
|
|
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'
|
|
09-20-07, 14:22
|
|
Registered User
|
|
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.
|
|
09-20-07, 15:09
|
|
Registered User
|
|
Join Date: Mar 2007
Posts: 24
|
|
|
|
Quote:
|
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)
|
|
09-20-07, 16:12
|
|
:-)
|
|
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 4,449
|
|
I don't know about DB2 for Z/OS, but on LUW the optimizer will turn "LIKE 'BEN%'" into "BETWEEN 'BEN' AND 'BENZZZZZZZ'" automatically.
|
|
09-20-07, 16:14
|
|
Registered User
|
|
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.
|
|
09-20-07, 16:17
|
|
Registered User
|
|
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'.
|
|
09-20-07, 16:32
|
|
Registered User
|
|
Join Date: Mar 2007
Posts: 24
|
|
I'll use the BETWEEN then, and to be safe I'll right-fill with x'FF'. 
|
|
09-20-07, 16:41
|
|
Registered User
|
|
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.
|
|
09-20-07, 20:26
|
|
Registered User
|
|
Join Date: May 2003
Location: USA
Posts: 5,196
|
|
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
|
|
09-21-07, 04:16
|
|
Registered User
|
|
Join Date: Jan 2003
Posts: 1,570
|
|
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 04:20.
|
09-21-07, 05:29
|
|
:-)
|
|
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 4,449
|
|
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
|
|
09-21-07, 06:50
|
|
Registered User
|
|
Join Date: Jan 2003
Posts: 1,570
|
|
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
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
which is faster?
