Cost of DECODE in the Where part

Hi all; Can the list throw any experience on the cost of having a DECODE in the where part of a select (or any other come to that) statement. EG the tel number is linked to a or b depending on party :- WHERE . . . . . and DECODE(upper(party),'A',a_num,b_num) = t.telephony_num . . . . . . . as against say:- . . . . . . . and ( ( a_num = t.telephony_num and ( party = 'A' or party = 'a' ) ) or ( b_num = t.telephony_num and ( party != 'A' and party != 'a' ) ) ) . . . . . . . Party column is in a big table and would be scanned. More generally, are Informix functions in the "where" becoming acceptable with this list ? Regards Ian

On Feb 1, 11:15 am, "ian" <ipel...@yahoo.com> wrote: > Hi all; > > Can the list throw any experience on the cost of having a DECODE in > the where part of a select (or any other come to that) statement. > > EG the tel number is linked to a or b depending on party :- > > WHERE . . . . . > and DECODE(upper(party),'A',a_num,b_num) = t.telephony_num > . . . . . . . > > as against say:- > . . . . . . . > and ( ( a_num = t.telephony_num and ( party = 'A' or party = > 'a' ) ) > or ( b_num = t.telephony_num and ( party != 'A' and party != > 'a' ) ) > ) > . . . . . . . > > Party column is in a big table and would be scanned. > > More generally, are Informix functions in the "where" becoming > acceptable with this list ? > > Regards > Ian Use the and/or statement and not the other so the optimizer can use the index. (party in ('A', 'a') and a_num = t.telephony_num ) or (party in ('A', 'a') and a_num = t.telephony_num ) reads a little easier. A union statement might run even faster. select * from ... where (party in ('A', 'a') and a_num = t.telephony_num ) union select * from ... where (party not in ('A', 'a') and a_num = t.telephony_num )

bozon wrote: > On Feb 1, 11:15 am, "ian" <ipel...@yahoo.com> wrote: >> Hi all; >> >> Can the list throw any experience on the cost of having a DECODE in >> the where part of a select (or any other come to that) statement. >> >> EG the tel number is linked to a or b depending on party :- >> >> WHERE . . . . . >> and DECODE(upper(party),'A',a_num,b_num) = t.telephony_num >> . . . . . . . >> >> as against say:- >> . . . . . . . >> and ( ( a_num = t.telephony_num and ( party = 'A' or party = >> 'a' ) ) >> or ( b_num = t.telephony_num and ( party != 'A' and party != >> 'a' ) ) >> ) >> . . . . . . . >> >> Party column is in a big table and would be scanned. >> >> More generally, are Informix functions in the "where" becoming >> acceptable with this list ? >> >> Regards >> Ian > > Use the and/or statement and not the other so the optimizer can use > the index. > > (party in ('A', 'a') and a_num = t.telephony_num ) or > (party in ('A', 'a') and a_num = t.telephony_num ) > > reads a little easier. A union statement might run even faster. > > select > * > from ... > where > (party in ('A', 'a') and a_num = t.telephony_num ) > union > select > * > from ... > where > (party not in ('A', 'a') and a_num = t.telephony_num ) Note: This OP needs to be asked what product he is using. This exact same question was raised in the Oracle forum. -- Daniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org

Anytime you toss a function into the where clause you are generally eliminating the possibility of using the index to resolve the query. Mind you I am not being categorical because there are alternative indexing methods which may require the use of a function. Something I have not explored. You can use this to your advantage when trying to force a hash join by doing something like 'tab1.col1+0=tab2.col1+0' j. -----Original Message----- From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org]On Behalf Of ian Sent: Thursday, February 01, 2007 11:16 AM To: informix-list@iiug.org Subject: Cost of DECODE in the Where part Hi all; Can the list throw any experience on the cost of having a DECODE in the where part of a select (or any other come to that) statement. EG the tel number is linked to a or b depending on party :- WHERE . . . . . and DECODE(upper(party),'A',a_num,b_num) = t.telephony_num . . . . . . . as against say:- . . . . . . . and ( ( a_num = t.telephony_num and ( party = 'A' or party = 'a' ) ) or ( b_num = t.telephony_num and ( party != 'A' and party != 'a' ) ) ) . . . . . . . Party column is in a big table and would be scanned. More generally, are Informix functions in the "where" becoming acceptable with this list ? Regards Ian _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list

OMG! Quick, call the net-police! What's the number for 911? You dare to question the bonafides of a descendant of Admiral Sir Edward Pellew? Shame on you, Horatio would call you out were he still alive. j. (Note: I AM assuming it's our own dear Ian) "There is no such thing as a stupid question, although there are plenty of inquisitive idiots out there". -----Original Message----- From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org]On Behalf Of DA Morgan Sent: Thursday, February 01, 2007 6:23 PM To: informix-list@iiug.org Subject: Re: Cost of DECODE in the Where part bozon wrote: > On Feb 1, 11:15 am, "ian" <ipel...@yahoo.com> wrote: >> Hi all; >> >> Can the list throw any experience on the cost of having a DECODE in >> the where part of a select (or any other come to that) statement. >> [deletia] Note: This OP needs to be asked what product he is using. This exact same question was raised in the Oracle forum. -- Daniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org