Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1

    Unanswered: Handling case-insensitive searches.

    What is considered the "best practices" method of search a mixed case data column? I could cast both the search text and the data as uppercase, but this would seem to sacrifice the benefits of any indexing and severely impact performance.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  2. #2
    Join Date
    Nov 2006
    Location
    Indianapolis
    Posts
    90
    store the data as you like, use function based indexes to speed up the queries
    (as long as your SQL matches the function)

    Standardize on some columns that make sense - like you don't want to use functions on status or code columns (they should all be one case)

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    As you've posted this question on the Oracle forum, perhaps you could consider something like this: I've created a table which has mishmash of upper and lower case. Then, I've altered a session in order to make my queries case insensitive.
    Code:
    SQL> select * from brisime;
    
    COL
    --------------------
    Little Foot
    LITTLE foot
    little FOOT
    
    SQL> alter session set nls_comp = ANSI;
    
    Session altered.
    
    SQL> alter session set nls_sort = GENERIC_BASELETTER;
    
    Session altered.
    
    SQL> select * from brisime where col = 'little foot';
    
    COL
    --------------------
    Little Foot
    LITTLE foot
    little FOOT
    As long as it will be fine for a session, it wouldn't work on the entire system. It is not recommended to enable this by default, but rather write the ON LOGON database trigger which would then distinguish users which have to have this option enabled from the others.

    Note that this won't work on all Oracle versions; I'm quite *sure* that it is supported from 9i onwards (being too lazy to search for it).

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Thanks to both of you.
    Some good pointers there, Littlefoot. I'll look into the settings you suggest.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    I would strongly suggest using a function based index. If you set the NLS parameters, you will effect ALL queries during that session, not just this one. This is what a function based index was created for.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Littlefoot, very nice addition.

  7. #7
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    One minor point with function based indexes is that they create an additional hidden column which may be significant for v.large tables.

    Alan

  8. #8
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by AlanP
    One minor point with function based indexes is that they create an additional hidden column which may be significant for v.large tables.
    Interesting, do you have any references for that?

  9. #9
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    My error, in Jonathan Lewis's book Practical Oracle 8i he says it creates a hidden column but looking on his web site he corrects the statement saying that it doesnt actually add the column to the table data, it just creates an entry into Oracle data dictionary and is a kludge.

    Alan

  10. #10
    Join Date
    Jun 2003
    Posts
    2

    Problems with 9i when using "nls_comp = ANSI"

    The examples above work great in 9i...if you DON'T use "LIKE" in your Where clause.

    Maybe I'm not doing it right, but "alter session set nls_comp = ANSI" doesn't seem like a perfect solution in 9i.

    Check out the following example.

    CREATE TABLE ZZZ_TestCase
    (FIRSTCOLUMN VARCHAR2(20 BYTE));

    Insert into ZZZ_TestCase (FirstColumn)
    Values ('Little Foot');
    Insert into ZZZ_TestCase (FirstColumn)
    Values ('LITTLE foot');
    Insert into ZZZ_TestCase (FirstColumn)
    Values ('little FOOT');
    Insert into ZZZ_TestCase (FirstColumn)
    Values ('LITTLE FOOT');
    Insert into ZZZ_TestCase (FirstColumn)
    Values ('little foot');

    --Make sure nls_comp = BINARY (default)
    alter session set nls_comp = BINARY;

    --*** Simple select statements, part 1
    select * from ZZZ_TestCase where FirstColumn = 'little foot';
    --1 record returned
    select * from ZZZ_TestCase where FirstColumn like 'l%';
    --2 records returned
    select * from ZZZ_TestCase where FirstColumn like 'L%';
    --3 records returned

    alter session set nls_comp = ANSI;
    alter session set nls_sort = GENERIC_BASELETTER;

    --*** Simple select statements, part 2
    select * from ZZZ_TestCase where FirstColumn = 'little foot';
    --5 records returned...instead of 1...yeah!
    select * from ZZZ_TestCase where FirstColumn like 'l%';
    --2 records returned...boo! I wanted 5
    select * from ZZZ_TestCase where FirstColumn like 'L%';
    --3 records returned...boo! I wanted 5
    Last edited by PappyBlueRibs; 12-12-06 at 19:15.

  11. #11
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    As far as I can recall, this approach won't work with LIKE.

  12. #12
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    function based index has got my vote! 'tis in use on 1.5million row table and queries are lightning fast.

Posting Permissions

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