|
View previous topic :: View next topic
|
| Author |
Message |
vishalbshah
New User
Joined: 01 Dec 2006
Posts: 61
Location: Pune
|
|
|
|
Hi,
I have customer name search table where I have below indexes
Unique Index
----------------
Customer number INTEGER
Name Type CHAR (10)
Name Sequence number SMALLINT
Non-unique Index
--------------------
Forename key CHAR (60)
Surname key CHAR (40)
Now, I am writing static and dynamic SQL to search for Customer number based on the Supplied Forename and Surname. To support Wildcard anywhere in the names I am using RTRIM in Dynamic and Static SQL as I want to Trim the SPACES on right side.
Dynamic SQL
RTRIM(FORE_NAME) LIKE CAST(? AS CHAR(60))
STATIC SQL
RTRIM (FORE_NAME) LIKE :WW-FORE-NAME ---> this contains "shah%____" ( "____" represents spaces)
I want to know if Use of RTRIM negates the index usage and would it use Table space scan for Dynamic or STATIC SQL ?
I am running EXPLAIN and I am getting TS for direct values and IS for parameter markers (i.e. ?).
Could someone please, suggest ![/code] |
|
| Back to top |
|
 |
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
- RTRIM does make this predicate non-indexable.
- DB2 might decide to do a non-matching IS because it would be less I/O, but it would still be a full IxScan.
- Are you sure you want "shah%____" in WW-FORE_NAME ? I can tell you this predicate will ALWAYS be FALSE. because you are looking for something that starts with shah and ends with 4 spaces. RTRIM(x) will never end in 4 spaces.
You probablty want "shah%%%%%%" in WW-FORE-NAME and then there is no need for using RTRIM() :
If RTRIM("shahkalala____") is like "shah%%%%%", then "shahkalala_____" is also like "shah%%%%%". |
|
| Back to top |
|
|
vishalbshah
New User
Joined: 01 Dec 2006
Posts: 61
Location: Pune
|
|
|
|
Hi,
As per my requirement I need to support wildcard anywhere in the Forname so for e.g. "VIS%L" should search for all names starting with "VIS" and ending with "L" , why i am using RTRIM is because I don't want DB2 to search for EXACT spaces after "L".
to answer your question :
"shah%____" is the value in my wroking storage variable , when I dont USE RTRIM and just use LIKE DB2 is searhing for exact trailing spaces and it does not find the record if the trailing spaces mismatch with DB2 column value, which is why I am using RTRIM , I initially used VARCHAR STRIP but came to know that that is not using INDEX.
Could you please, tell me which option is better RTRIM with LIKE and single %( which would trim the trailing SPACES or just LIKE with Padded %%% (in the end instead of spaces) to make use of index defined for Forename ? |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
There is no way you can make RTRIM(column) like ? indexable, unless you use index on expression. (google it).
What you can do is make 2 predicates :
| Code: |
move 'VIS%L' to ws-fore-name1
move all '%' to ws-fore-name1(6:)
move 'VIS%L' to ws-fore-name2
...
where FORE_NAME like :ws-fore-name1
and rtrim(fore_name like rtrim(:ws-fore-name2) |
the first predicate will be indexable, but sometimes will return too many rows.
the second predicate will remove those rows that do not end on L |
|
| Back to top |
|
|
vishalbshah
New User
Joined: 01 Dec 2006
Posts: 61
Location: Pune
|
|
|
|
hi ,
I did not understand why you used RTRIM twice once column name and the other one on the variable.
I was only using it on column name as :
RTRIM (FORE_NAME) LIKE :ws-fore-name
is it not the right syntax ?
i have tried below SYNTAX as well :
FORE_NAME LIKE RTRIM(:ws-fore-name)
do i need to used RTRIM on both sides ?
I understood as per your suggestion
where FORE_NAME like :ws-fore-name1
AND rtrim(fore_name like rtrim(:ws-fore-name2)
the first predicate becomes stage1 and indexable so DB2 would use index here. please, confirm ! |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
| Back to top |
|
|
vishalbshah
New User
Joined: 01 Dec 2006
Posts: 61
Location: Pune
|
|
|
|
Hi,
It's never about faith , please don't try and create any confusion here...
why i am trying to be sure is because I am finding difference between EXPLAIN and RTRIM I uses and the response i got from GUY C
the EXPLAIN I ran has RTRIM on column name and it gave me result saying that RTRIM is not impacting the Index Usage. but from the response which I am receiving here says use of RTRIM would not allow Index usage defined on the column.
As i want to be 100% sure before I make use of this, I am raising questions at different posts , hoping that i get consistent answers.
I think I have full liberty to confirm my answers, So I request you to respond to the question rather than creating any misunderstandings ! |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
,
| Quote: |
| I think I have full liberty to confirm my answers, So I request you to respond to the question rather than creating any misunderstandings ! |
You are not in the position of requesting anything
and since You mistrust the answers you receive here why are You still around ? |
|
| Back to top |
|
|
vishalbshah
New User
Joined: 01 Dec 2006
Posts: 61
Location: Pune
|
|
|
|
Hi,
The reason i am not sure about the answer is below result from EXPLAIN on the Query which I ran
| Code: |
EXPLAIN ALL SET QUERYNO = 992222
FOR
SELECT DISTINCT CUST_NO, NAM_TYP_NO, NAM_SEQ_NO
FROM CUD_PERS_NAM_SRCH
WHERE SRNM_SRCH_KEY = 'ANANAT'
AND[b] RTRIM(FRNM_SRCH_KEY) LIKE 'GNSN% ' [/b]
AND FRNM_SEQ_NO = 1
AND UNIT_NO BETWEEN 0000 AND 9999
;
SELECT *
FROM PLAN_TABLE
WHERE QUERYNO = 992222
ORDER BY QBLOCKNO, PLANNO
;
+---------+---------+---------+---------+---------+---------+---------+---------
TNAME TABNO ACCESSTYPE MATCHCOLS ACCESSCREATOR ACCESSNAME
+---------+---------+---------+---------+---------+---------+---------+---------
CUD_PERS_NAM_SRCH 1 I 2 CUDUTGB XCUD1160
0 0
SELECT IX.COLCOUNT, PT.MATCHCOLS
FROM SYSIBM.SYSINDEXES IX, PLAN_TABLE PT
WHERE IX.CREATOR = PT.ACCESSCREATOR
AND IX.NAME = PT.ACCESSNAME
AND PT.QUERYNO = 992222
---------+---------+---------+
COLCOUNT MATCHCOLS
---------+---------+---------+
9 2
|
As you can see above EXPLAIN clearly suggest that 2 Columns from the index were used which are SRNM_SRCH_KEY and FRNM_SRCH_KEY (even though RTRIM was used ) . I am not 100% sure with Guy C's answer that use of RTRIM negates the Index usage.
By the way the index XCUD1160 contains below columns
| Code: |
SRNM_SRCH_KEY
FRNM_SRCH_KEY
FRNM_SEQ_NO
UNIT_NO
DOB
GNDR
CUST_NO
NAM_TYP_NO
NAM_SEQ_NO
|
|
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
apparently not, I guess the DB2 optimizer is getting smarter every day.
Does your query returns any row? |
|
| Back to top |
|
|
vishalbshah
New User
Joined: 01 Dec 2006
Posts: 61
Location: Pune
|
|
|
|
Hi Guy C,
Apologies for confusion as I mentioned in my initial mail this SQL is Dynamically prepared. so I have the lenght of the variable calculated which I am populating in the SQLDA, So I don't need RTRIM on Righthand side.
FYI, Dynamic SQL
RTRIM(FORE_NAME) LIKE CAST(? AS CHAR(6))
This makes use of Index properly.
For Static SQL I agree with you on that RTRIM is not making use of Index.
Could I please, ask why in your first example have provided 2 predicates to get the required result ?
move 'VIS%L' to ws-fore-name1
move all '%' to ws-fore-name1(6
move 'VIS%L' to ws-fore-name2
where FORE_NAME like :ws-fore-name1
and rtrim(fore_name) like rtrim(:ws-fore-name2)
I am trying to understand use of ws-fore-name1, is it just to force index usage defined on the FORE_NAME ? ( as the data we want is actually stisfying predicate 2 i.e. rtrim(fore_name) like rtrim(:ws-fore-name2)) |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
you are confusing 2 things :
1) rtrim(column) like ? is (according to your explain, and my latest experiments) indexable.
so using two predicates : 1 indexable and 1 (i thought) not indexable is not necessary.
2) the result of rtrim(column) like 'ab%c___' will always be false.
you can use cast('ab%c ___' as char(4)) which could give some results.
I thought using rtrim('ab%c___') would be exactly the same.
And although the result should be the same,for some strange reason this criteria is stage2 (non-indexable)
That'll teach me to trust my knowledge of the DB2-optimizer.
So sorry if my mistakes/assumptions made you confuse things. |
|
| Back to top |
|
|
vishalbshah
New User
Joined: 01 Dec 2006
Posts: 61
Location: Pune
|
|
|
|
That Makes Sense, Thank You!
Just one thing like 'ab%c%%%' (indexable) and rtrim('ab%c___') (stage -2 and Non-indexable) would have different results:
"abecxyz" would be a match as per 'ab%c%%%' whereas it would not be a match as per rtrim('ab%c___') |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| yes I noticed , that's why I editted my post afterwards. |
|
| Back to top |
|
|
|
|
