Informix

 View Only
Expand all | Collapse all

Select with Ignore-Case

  • 1.  Select with Ignore-Case

    Posted Thu May 07, 2020 03:35 AM
    Hi!

    We often need Selects with ignore-Case (e.g. searching for a Name).
    So our solution is always a lower(column) in the query.
    This works fine, but this is always a database-killer at big tables.

    I know hat I can make a complete Case-Insensitive-Database, but that´s too much.

    Functional-Index with lower doesn´t work. I´ve seen the solution with separate functions.
    But this is also a strange think.
    Should we really make for every char-type (char/lvarchar/varchar) an separate function?
    When you change the datatype, you´ve to change the function at the index also?

    A second "lower-column" is currently a workaround for us.

    Is there any better solution for this?
    How do you handle this

    ------------------------------
    Kind Regards
    Stefan
    ------------------------------

    #Informix


  • 2.  RE: Select with Ignore-Case

    Posted Thu May 07, 2020 03:49 AM

    Hi Stefan,

     

     

    Could you please detail what is wrong/doesn't work well with the functional index?

     

    If I were to make such an index, I would directly test declaring the inbound argument as LVARCHAR and pray for the cast to work. (In a staging environment, not in prod �� )

    The functional should be the best solution because precisely you will have an index on that column.

     

    Else you have the RegEx (regular expressions) datablade, starting from 12.10 xC8, that will allow to "say" dynamically ignorecase or not in you query statement, but you probably won't benefit from the index

     

    Regex is a great datablade. You can find Mark A's presentation in the IIUG presentations

     

    Hope this is a start of help ��

    Eric

     






  • 3.  RE: Select with Ignore-Case

    Posted Thu May 07, 2020 04:47 AM

    Hi!

    Let´s make an example:
    CREATE TABLE Persons (
        LastName varchar(128,30),
        FirstName varchar(128,20),
        Address varchar(255),
        City char(50),
        info lvarchar(500),

        id serial
    );

    When I want to lower-search for this 5 columns, I need the following functions:
    create function my_lower_varchar128_30( s varchar(128,30) ) returning varchar(128,30) 
        return lower(s) ; end function;

    create function my_lower_varchar128_20( s varchar(128,20) ) returning varchar(128,20) 
        return lower(s) ; end function;

    ... 3 more for my_lower_varchar255, my_lower_char_50, my_lower_lvarchar255.

    Create the indexes (use always the correct function-Name!):
    create index i_persons1 on persons(my_lower_varchar128_30(LastName));
    create index i_persons2 on persons(my_lower_varchar128_20(FirstName));
    ...

    And at the select you´ve also to use the correct function-Name:
    SELECT *
    FROM persons
    WHERE my_lower_varchar128_30(LastName) MATCHES "abc*"
    OR my_lower_varchar128_20(FirstName) MATCHES "xy*"

    I hope there´s a better solution, because that´s hard for administration and implementation.
    The problem is, if someone changes the columns length oder the datatye (e.g. char->varchar).



    ------------------------------
    Kind Regards
    Stefan
    ------------------------------



  • 4.  RE: Select with Ignore-Case

    Posted Thu May 07, 2020 07:28 AM

    Stefan

     

     

    Here is what I had in mind and that I confirmed:

    You need a function for each data type, but not including the length. Also CHAR and VCHAR combine well, i.e you can define your function inbound and outbound as varchar (255) and it will work with index on a CHAR, up to 255.

    On the other hand, using a "char" defined function for a varchar also works

     

    So the real limits seems to be the limits of an index key (i.e 387 bytes for 2k pages etc).

     

    Conclusion: You have the choice either to choose 'char' defined or 'varchar' defined inbound and outbound, and your limit is the size of your page, ie 387B for 2 K

     

    Here is a small script with which you can play to check all this. Don't forget to check the set explain on output

     

    BR

    Eric

     

    drop table if exists Persons;

    CREATE TABLE Persons (

        LastName varchar(255,30),

        FirstName varchar(128,20),

        Address varchar(255),

        City char(50),

        info lvarchar(500),

        id serial

    );

     

    drop function if exists my_lower_varchar;

    create function if not exists my_lower_varchar( s varchar(255) ) returning varchar(255) with (not variant )

        return trim( lower(s)) ; end function;

     

    drop function if exists my_lower_char;

    create function if not exists my_lower_char( s char(387) ) returning char(387) with (not variant )

        return trim( lower(s)) ; end function;

     

     

    drop function if exists my_lower_lvarchar;

    create function if not exists my_lower_lvarchar( s lvarchar(4096) ) returning lvarchar(4096) with (not variant )

        return trim(lower(s)) ; end function;

     

     

    insert into persons values ("King","Laura","Main Street 667","Boston","Laura is one of the founders of RDS, the Informix 4GL ancestor",0);

    insert into persons values ("Sippl","Roger","Bohannon Drive 4100","Menlo Park","Roger founded Informix jointly with Laura",0);

    insert into persons values ("Saranga","Mike","2nd avenue 3200 ","Portland","Mike designed the new multi-threaded architecture of Informix Dynamic Server",0);

    insert into persons values ("White","Phil","Bohannon Drive 4100","Menlo Park","Phil is a very well known CEO of Informix, but we won't add more comments",0);

    insert into persons values ("Rodrigues Ferreira Almeida Silva Ferreira Martins Nascimento","Antonio","Avenida da Republica 667-B","Lisboa","Antonio was a big fan of Informix, whose CEO obliged him to move to Oracle, but finally came back to Informix",0);

     

    create index i_persons0 on persons(my_lower_varchar(City));         -- use a "varchar" function even for a char

    create index i_persons1 on persons(my_lower_varchar(LastName));     -- use a "varchar" function for a varchar

    create index i_persons2 on persons(my_lower_varchar(FirstName));    -- use a "varchar" function for a varchar with another size

    create index i_persons3 on persons(my_lower_char(address));         -- use a "char" function for a varchar column: it works

    create index i_persons4 on persons(my_lower_varchar(address));      -- use a "varchar" function for a varchar column: it works too

    --create index i_persons5 on persons(my_lower_lvarchar(info));      -- I could not create the index on lvarchar, I have "too big index error" like message, which is due to limited size of index key (387B on 2kpages"

    update statistics high for table persons;

    set explain on;

    select "OnLastName",* from persons where my_lower_varchar(LastName) = "sippl";

    select "OnFirstName",* from persons where my_lower_varchar(FirstName) = "roger";

    select "OnAddress-1",* from persons where my_lower_char(Address) = "Bohannon Drive 4100" ;

    select "OnAddress-2",* from persons where my_lower_varchar(Address) = "Bohannon Drive 4100";

    select "OnCity",* from persons where my_lower_varchar(city) = "menlo park";

    select "OnInfo",* from persons where my_lower_lvarchar(info) matches "*ceo*";

    set explain off;

     






  • 5.  RE: Select with Ignore-Case

    IBM Champion
    Posted Thu May 07, 2020 07:32 AM
    Eric:

    Using function overloading all three of your functions could be named my_idx_lower()! Then it will work for all three data types interchangeably even if modifying the data types of an indexed column!

    Art

    Art S. Kagel, President and Principal Consultant
    ASK Database Management


    Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference.  Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.








  • 6.  RE: Select with Ignore-Case

    Posted Thu May 07, 2020 08:13 AM

    @Art Kagel true, that was underlying in my conclusion, since the script shows one can interchange data types.

    I saw your solution after I wrote mine *lol*

     

    And using LVARCHAR is the one that needs less letters to type in the commands

    At least one can see how it works

     

    Cheers

    Eric

     






  • 7.  RE: Select with Ignore-Case

    IBM Champion
    Posted Thu May 07, 2020 08:52 AM
    Or just cast the variables ?

    Paul Watson
    Oninit LLC
    +1-913-387-7529
    www.oninit.com
    Oninit®️ is a registered trademark of Oninit LLC





  • 8.  RE: Select with Ignore-Case

    Posted Thu May 07, 2020 11:56 AM
    Hi!

    Thanks. 
    That looks like a good solution.
    I changed the function to one "my-lower" and so we can always use the the same name.

    Index-Size
    One question to the size of the index:
    796bytes is the maximum at 4k pages.

    Does this mean, that every entry in the index has 796bytes when I have a char(20) - column?
    drop function if exists my_lower(char);
    create function if not exists my_lower( s char(796) ) returning char(796) with (not variant )
    return lower(s); end function;

    Lvarchar
    Additional Info: When I change the lvarchar-function to 796, I can also use it the info-Colum:
    drop function if exists my_lower(lvarchar);
    create function if not exists my_lower( s lvarchar(796) ) returning lvarchar(796) with (not variant )
    return lower(s); end function;

    I tried it also with a lvarchar(1200) and my_lower(lvarchar) function. There was no error at creation/select.
    You can´t select data >796bytes, but this is as expected.

    ------------------------------
    Kind Regards
    Stefan
    ------------------------------



  • 9.  RE: Select with Ignore-Case

    IBM Champion
    Posted Thu May 07, 2020 07:22 AM
    Regex has been available as a third party blade since V10 

    Cheer
    Paul

    Paul Watson
    Oninit LLC
    +1-913-387-7529
    www.oninit.com
    Oninit®️ is a registered trademark of Oninit LLC





  • 10.  RE: Select with Ignore-Case

    IBM Champion
    Posted Thu May 07, 2020 07:19 AM
    Stefan:

    I don't understand why you think that you need a separate indexing function for every column that you want to index. The only requirement for the function over which a functional index is built is that it be NON-VARIANT. Yes, the built-in LOWER() function is not marked as NON-VARIANT, but you just create one single NON-VARIANT function named say idx_lower() that simply calls LOWER() on its single LVARCHAR argument and returns the result as LVARCHAR and you can use that function for as many functional indexes mapping mixed case to lower case as you need for all character types: CHAR, VARCHAR, and LVARCHAR! Towit:

    CREATE FUNCTION idx_lower( instring LVARCHAR ) RETURNING LVARCHAR as result;
    RETURN lower( instring );
    END FUNCTION WITH (NOT VARIANT);

    CREATE INDEX find_nocase ON mytable( idx_lower( name_col ) );

    What am I missing?

    Art

    Art S. Kagel, President and Principal Consultant
    ASK Database Management


    Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference.  Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.








  • 11.  RE: Select with Ignore-Case

    Posted Thu May 07, 2020 11:59 AM
    Hi!

    >I don't understand why you think that you need a separate indexing function for every column that you want to index. 
    Yes and this was my mistake!
    And I´m happy, that I was wrong. ;-)


    ------------------------------
    Kind Regards
    Stefan
    ------------------------------



  • 12.  RE: Select with Ignore-Case

    Posted Thu May 07, 2020 12:09 PM
    ===
    I know hat I can make a complete Case-Insensitive-Database, but that´s too much.
    ===

    We have been using case insensitive databases for some apps that need this type of thing and this has been working well for us.  No need to worry about code all over the place for this, no need to worry about adding columns to a table just to handle that efficiently, etc.

    I wanted to make sure you realize that it is not the whole database that becomes case insensitive - only the columns of type NCHAR, NVARCHAR.  In our case it was feasible to go with data types like these where needed (all name fields, identifier code fields, etc.)  as part of a major conversion/upgrade.  I understand it may not be feasible in every situation.

    Hal Maner
    M Systems International, Inc.





  • 13.  RE: Select with Ignore-Case

    Posted Fri May 08, 2020 03:20 AM

    Hi!

    >I wanted to make sure you realize that it is not the whole database that becomes case insensitive - only the columns of type NCHAR, NVARCHAR
    Thanks for the info. This could be also a solution.

    But you can only do this at database creation?
    We have some very big databases TBs and we can´t reimport those databases.



    ------------------------------
    Kind Regards
    Stefan
    ------------------------------



  • 14.  RE: Select with Ignore-Case

    Posted Mon May 11, 2020 09:28 AM

    I looked and looked and could not find any "ALTER DATABASE" type option for this.  It may only be at creation time.  I always did it at creation/re-import time.

     

    Hal Maner

    M Systems International, Inc.

     






  • 15.  RE: Select with Ignore-Case

    IBM Champion
    Posted Mon May 11, 2020 09:41 AM
    Yes, the only database level attribute that you can change is the logging mode. All others like case sensitivity and code set are immutable once the database is created.

    Art

    Art S. Kagel, President and Principal Consultant
    ASK Database Management


    Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference.  Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.








  • 16.  RE: Select with Ignore-Case

    IBM Champion
    Posted Thu May 07, 2020 12:21 PM
    Hi Art,

    yes, the functional index with lower() works fine - I create this at a bigger customer installation long time ago :-) 

    Best Rgards
    Henri

    ------------------------------
    Henri Cujass
    leolo IT, CTO
    Germany
    ------------------------------