1 2 Previous Next 20 Replies Latest reply: Nov 9, 2007 5:41 AM by 3520 RSS

    Case Insensitive Indexes

    598967
      In relation to switching on case insensitive queries using
      alter session set NLS_COMP=LINGUISTIC;
      Can anyone answer the following?
      >
      Yes, it works.... but I can't for the life of me figure out how to build a linguistic index that the LIKE clause will actually use. Building an index thus, for example:

      create index bin_ai on names(NLSSORT("NAME",'nls_sort=''BINARY_AI'''));

      makes an index which does get used to good effect by queries such as
      select name from names where name = 'Johny Jacobson';
      but not by
      select name from names where name like 'Johny%';

      Hence, in a real-world test with 100,000 records, the LIKE query runs about 100 times slower than the '=' query (3 sec compared to 0.03 sec). Not very scalable. Is there a way to speed this up??


      Also is it possible to set session variables such as nls_comp on a database/schema/user level?
        • 1. Re: Case Insensitive Indexes
          damorgan
          An Oracle version number to four decimal places might help you get a useful answer.
          • 2. Re: Case Insensitive Indexes
            108476
            Hi,
            select name from names where name like 'Johny%';
            Performance when using the SQL "like" clause can be tricky because the wildcard "%" operator can invalidate the index. For example a last_name index would be OK with a "like 'SMI%'" query, but unusable with "like '%SMI%'.

            One obscure trick for indexing queries "like '%SON'" is to create a REVERSE index and them programmatically reverse the like clause to read "like 'NOS%'", effectively indexing on the other side of the text.

            You might want to look at Oracle*text indexes, if your database has low DML:

            http://www.dba-oracle.com/oracle_tips_like_sql_index.htm

            If you are 10gr2:

            Oracle 10g release 2 has now introduced a case insensitive search method for SQL that avoids index invalidation and unnecessary full-table scans. You can also employ Oracle text indexes to remove full-table scans when using the LIKE operator. Prior to Oracle10g release 2 case insensitive queries required special planning:

            1 - Transform data in the query to make it case insensitive (note that this can invalidate indexes without a function-based index):

            create index upper_full_name on customer ( upper(full_name));

            select full_name from customer
            where upper(full_name) = 'DON BURLESON';

            2 - Use a trigger to transform the data to make it case insensitive (or store the data with the to_lower or to_upper BIF.

            3 - Use Alter session commands:

            alter session set NLS_COMP=ANSI;
            alter session set NLS_SORT=GENERIC_BASELETTER;
            select * from customer where full_name = 'Don Burleson'


            Hope this helps. . .

            Don Burleson
            Oracle Press author
            • 3. Re: Case Insensitive Indexes
              94799
              create a REVERSE index and them programmatically reverse the like clause to read "like 'NOS%'"
              Sorry, Don, not clear what you are saying here, can we have an example?
              • 4. Re: Case Insensitive Indexes
                Richard Foote
                Hi There

                The LIKE operator is not supported with Linguistic indexes until 11g.

                Cheers

                Richard Foote
                • 5. Re: Case Insensitive Indexes
                  598967
                  @darmorgan - sorry my stupid.. i'm running:
                  Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit

                  @richard - So if that's the case then there's no way to enable case insensitive searches without getting a huge performance hit.. or ensuring that every single like clause has UPPER's inside it?
                  • 6. Re: Case Insensitive Indexes
                    damorgan
                    You got it. I don't know why Don gave you an answer that didn't address the obvious.
                    • 7. Re: Case Insensitive Indexes
                      3520
                      create a REVERSE index and them programmatically
                      reverse the like clause to read "like 'NOS%'"

                      Sorry, Don, not clear what you are saying here, can
                      we have an example?
                      Example is in my blog post [url http://gplivna.blogspot.com/2007/03/where-bad-performance-starts-my.html]here.
                      There is also discussion with Laurent Schneider when actually REVERSE function can be and cannot be used.

                      Gints Plivna
                      http://www.gplivna.eu

                      P.S. Just realized that Don is talking about REVERSE index and not REVERSE function. So my example (with limitations!!!) however is using Reverse function.

                      Message was edited by:
                      gintsp
                      Added P.S.
                      • 8. Re: Case Insensitive Indexes
                        3520
                        One obscure trick for indexing queries "like '%SON'"
                        is to create a REVERSE index and them
                        programmatically reverse the like clause to read
                        "like 'NOS%'", effectively indexing on the other side
                        of the text.
                        Hi Don!

                        I'd like to see how you achieve that with REVERSE index. May I ask for example? A simple excerpt with autotrace using SQLPlus would be sufficient.

                        Gints Plivna
                        http://www.gplivna.eu
                        • 9. Re: Case Insensitive Indexes
                          94799
                          Thanks Gints, I guess there is an ambiguity around describing a function-based index on REVERSE (column_name) as a REVERSE index, which is a different thing altogether.
                          • 10. Re: Case Insensitive Indexes
                            3520
                            a function-based index on REVERSE
                            (column_name) as a REVERSE index, which is a
                            different thing altogether.
                            Yes indeed!

                            Gints Plivna
                            http://www.gplivna.eu
                            • 11. Re: Case Insensitive Indexes
                              Richard Foote
                              One will work, the other won't !!

                              Cheers

                              Richard
                              • 12. Re: Case Insensitive Indexes
                                108476
                                Google is our friend:

                                http://www.orafaq.com/node/1918

                                Part of this was "lifted" from my site, but that's OK:

                                Another trick for indexing queries with a leading wildcard character (like '%SON') is to create a REVERSE index - and then programmatically reverse the LIKE-clause to read "LIKE 'NOS%'", effectively indexing on the other side of the text, clumsy, yet effective.

                                Steps:

                                1. Create reverse key index on columns that will be searched. For example, create a reverse key index on Cust_Name of the customer table:


                                CREATE INDEX Cust_Name_reverese_idx
                                ON customer(Cust_Name)
                                REVERSE;

                                2. Programmatically reverse the SQL LIKE-clause to read '%saliV%':

                                Existing Query:


                                SELECT *
                                FROM customer
                                WHERE Cust_Name LIKE '%Vilas%'

                                New Query:


                                SELECT *
                                FROM customer
                                WHERE Cust_Name LIKE '%saliV%';
                                • 13. Re: Case Insensitive Indexes
                                  94799
                                  Google is our friend
                                  So I see.

                                  Thanks for the example - unfortunately mrvilas1's example appears to be nonsensical. How can a query on cust_name LIKE '%saliV%' return rows matching '%Vilas%' (unless they match both by some coincidence).
                                  • 14. Re: Case Insensitive Indexes
                                    108476
                                    Hi James,
                                    unfortunately mrvilas1's example appears to be nonsensical.
                                    Oops, I see that now . . . . Thanks!

                                    The query should be like ('%sakiV') . . . .

                                    But you get the general concept, right? It's a kludge, I said so!

                                    Personally, I only mentioned it as an aside, and I've only found a need to do it in a few shops with specialized needs.

                                    Prior to Oracle10g release 2 case insensitive queries required special planning:

                                    1 - Transform data in the query to make it case insensitive (note that this can invalidate indexes without a function-based index):

                                    create index upper_full_name on customer ( upper(full_name));

                                    select full_name from customer
                                    where upper(full_name) = 'DON BURLESON';

                                    2 - Use a trigger to transform the data to make it case insensitive (or store the data with the to_lower or to_upper BIF.

                                    3 - Use Alter session commands:

                                    alter session set NLS_COMP=ANSI;
                                    alter session set NLS_SORT=GENERIC_BASELETTER;
                                    select * from customer where full_name = 'Don Burleson'
                                    In Oracle10g release 2 we see this new approach to case insensitive searches. (This example is derived from Tom Kyte's Oracle OpenWorld 2004 presentation on Oracle10g release 2 new features)

                                    Initialization parameters:

                                    NLS_SORT=binary_ci
                                    NLS_COMP=ansi

                                    Sample index create:

                                    create index
                                    caseless_name_index
                                    on
                                    customer
                                    (
                                    nlssort( full_name, 'NLS_SORT=BINARY_CI')
                                    );

                                    alter session set nls_sort=binary_ci;
                                    select * from customer where full_name = 'Don Burleson';
                                    1 2 Previous Next