8 Replies Latest reply: Jun 24, 2012 9:39 AM by Nikolay Savvinov RSS

    Why CBO don't use function-base index when I use like and bind variable

    894637
      Hello

      I have litle problem with function-base index and like with bind variable.
      When I use like with bind variable, the CBO don't use my function-base index.
      For example when I create table and index:

      ALTER SESSION SET NLS_SORT='BINARY_CI';
      ALTER SESSION SET NLS_COMP='LINGUISTIC';
      alter session set nls_language='ENGLISH';

      -- DROP TABLE TEST1;
      CREATE TABLE TEST1 (K1 VARCHAR2(32));
      create index test1_idx on test1(nlssort(K1,'nls_sort=BINARY_CI'));

      INSERT INTO TEST1
      SELECT OBJECT_NAME FROM ALL_OBJECTS;
      COMMIT;

      When I run:
      ALTER SESSION SET NLS_SORT='BINARY_CI';
      ALTER SESSION SET NLS_COMP='LINGUISTIC';
      SELECT * FROM TEST1 WHERE K1 = 'abcd';
      or
      SELECT * FROM TEST1 WHERE K1 LIKE 'abcd%';

      CBO use index.

      PLAN_TABLE_OUTPUT
      ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      SQL_ID 4vrmp7cshbvqy, child number 1
      -------------------------------------
      SELECT * FROM TEST1 WHERE K1 LIKE 'abcd%'

      Plan hash value: 1885706448

      -----------------------------------------------------------------------------------------
      | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
      -----------------------------------------------------------------------------------------
      | 0 | SELECT STATEMENT | | | | 1 (100)| |
      | 1 | TABLE ACCESS BY INDEX ROWID| TEST1 | 2 | 98 | 1 (0)| 00:00:01 |
      |* 2 | INDEX RANGE SCAN | TEST1_IDX | 2 | | 1 (0)| 00:00:01 |
      -----------------------------------------------------------------------------------------

      Predicate Information (identified by operation id):
      ---------------------------------------------------

      2 - access("TEST1"."SYS_NC00002$">=HEXTORAW('6162636400') AND
      "TEST1"."SYS_NC00002$"<HEXTORAW('6162636500') )


      but when I run
      SELECT * FROM TEST1 WHERE K1 LIKE :1;
      CBO don't use index

      PLAN_TABLE_OUTPUT
      ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      SQL_ID 9t461s1669gru, child number 0
      -------------------------------------
      SELECT * FROM TEST1 WHERE K1 LIKE :1

      Plan hash value: 4122059633

      ---------------------------------------------------------------------------
      | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
      ---------------------------------------------------------------------------
      | 0 | SELECT STATEMENT | | | | 89 (100)| |
      |* 1 | TABLE ACCESS FULL| TEST1 | 2 | 48 | 89 (3)| 00:00:02 |
      ---------------------------------------------------------------------------

      Predicate Information (identified by operation id):
      ---------------------------------------------------

      1 - filter("K1" LIKE :1)


      What should I change to force CBO to use index.
      I don't wont use index hint in query.


      My oracle version:
      Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
      PL/SQL Release 11.2.0.1.0 - Production
      CORE     11.2.0.1.0     Production
      TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
      NLSRTL Version 11.2.0.1.0 - Production
        • 1. Re: Why CBO don't use function-base index when I use like and bind variable
          Frank Kulash
          Hi,

          If you use a bind variable, then the compiler has to find a plan that will work for all possible values of the bind variable.
          Some of those possible values begin with a wild-card ('%' or '_').
          If the value does begin with a wild-card, then the optimizer can't use the index.
          • 2. Re: Why CBO don't use function-base index when I use like and bind variable
            894637
            OK. But why if I create normal index (create index test1_idx on test1(K1)) and return to default nls settings this same query use index.

            PLAN_TABLE_OUTPUT
            ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
            SQL_ID 9t461s1669gru, child number 0
            -------------------------------------
            SELECT * FROM TEST1 WHERE K1 LIKE :1

            Plan hash value: 598212486

            ------------------------------------------------------------------------------
            | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
            ------------------------------------------------------------------------------
            | 0 | SELECT STATEMENT | | | | 1 (100)| |
            |* 1 | INDEX RANGE SCAN| TEST1_IDX | 1 | 18 | 1 (0)| 00:00:01 |
            ------------------------------------------------------------------------------

            Predicate Information (identified by operation id):
            ---------------------------------------------------

            1 - access("K1" LIKE :1)
            filter("K1" LIKE :1)

            Note
            -----
            - dynamic sampling used for this statement (level=2)


            when index is function-base (create index test1_idx on test1(nlssort(K1,'nls_sort=BINARY_CI')))

            PLAN_TABLE_OUTPUT
            ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
            SQL_ID 9t461s1669gru, child number 1
            -------------------------------------
            SELECT * FROM TEST1 WHERE K1 LIKE :1

            Plan hash value: 4122059633

            ---------------------------------------------------------------------------
            | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
            ---------------------------------------------------------------------------
            | 0 | SELECT STATEMENT | | | | 89 (100)| |
            |* 1 | TABLE ACCESS FULL| TEST1 | 3 | 54 | 89 (3)| 00:00:02 |
            ---------------------------------------------------------------------------

            Predicate Information (identified by operation id):
            ---------------------------------------------------

            1 - filter("K1" LIKE :1)

            Note
            -----
            - dynamic sampling used for this statement (level=2)



            when I create index with upper function "index test1_idx on test1(upper(K1))" the query use index

            SELECT * FROM TEST1 WHERE upper(K1) LIKE :1

            Plan hash value: 1885706448

            -----------------------------------------------------------------------------------------
            | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
            -----------------------------------------------------------------------------------------
            | 0 | SELECT STATEMENT | | | | 1 (100)| |
            | 1 | TABLE ACCESS BY INDEX ROWID| TEST1 | 4481 | 157K| 1 (0)| 00:00:01 |
            |* 2 | INDEX RANGE SCAN | TEST1_IDX | 806 | | 1 (0)| 00:00:01 |
            -----------------------------------------------------------------------------------------

            Predicate Information (identified by operation id):
            ---------------------------------------------------

            2 - access("TEST1"."SYS_NC00002$" LIKE :1)
            filter("TEST1"."SYS_NC00002$" LIKE :1)

            Note
            -----
            - dynamic sampling used for this statement (level=2)
            • 3. Re: Why CBO don't use function-base index when I use like and bind variable
              Nikolay Savvinov
              Hi,

              suppose you run your query with :1='%', then Oracle would end up retrieving the entire table via an index, row by row, which will be hundreds of times slower than a full table scan. How should the optimizer know this won't happen? Considering the information you provided, the optimizer is making a very rational choice.

              Best regards,
              Nikolay
              • 4. Re: Why CBO don't use function-base index when I use like and bind variable
                894637
                OK. I understand that.
                But why index test1_idx on test1(upper(K1)) work in this situation?
                • 5. Re: Why CBO don't use function-base index when I use like and bind variable
                  Etbin
                  Just a thought:
                  Your function based index might not be used because nlssort function is probably making the index bigger than the table (few columns there)
                  look at:
                  The string returned, also known as the collation key, is of RAW data type. The length of the collation key resulting from a given char value for a given collation may exceed 2000 bytes, which is the maximum length of the RAW value returned by NLSSORT. In this case, NLSSORT calculates the collation key for a maximum prefix, or initial substring, of char so that the calculated result does not exceed 2000 bytes. For monolingual collations, for example FRENCH, the prefix length is typically 1000 characters. For multilingual collations, for example GENERIC_M, the prefix is typically 500 characters. The exact length may be lower or higher depending on the collation and the characters contained in char.
                  within http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions113.htm#SQLRF00678

                  Regards

                  Etbin
                  • 6. Re: Why CBO don't use function-base index when I use like and bind variable
                    Nikolay Savvinov
                    hi,

                    the optimizer doesn't have a way of knowing the cardinality exactly. so it has to guess. guessing is random. sometimes you are lucky, sometimes you're not.

                    it's not random in the scientific sense (i.e. if you run it several times on the same platform and same database version you will get the same result), so if you really want to know you can go get 10053 event optimizer tracing and find out what's going on behind the scene. but the important thing is for you to compose to query in such a way that the optimizer would be able to determine the cardinality exactly.

                    Best regards,
                    Nikolay
                    • 7. Re: Why CBO don't use function-base index when I use like and bind variable
                      Solomon Yakobson
                      Nikolay Savvinov wrote:
                      the optimizer doesn't have a way of knowing the cardinality exactly.
                      Issue has nothing to do with cardinality. Look at explain plan:
                      SQL> @?\rdbms\admin\utlxpls
                      
                      PLAN_TABLE_OUTPUT
                      -----------------------------------------------------------------------------------------
                      Plan hash value: 1885706448
                      
                      -----------------------------------------------------------------------------------------
                      | Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
                      -----------------------------------------------------------------------------------------
                      |   0 | SELECT STATEMENT            |           |     2 |    98 |     3   (0)| 00:00:01 |
                      |   1 |  TABLE ACCESS BY INDEX ROWID| TEST1     |     2 |    98 |     3   (0)| 00:00:01 |
                      |*  2 |   INDEX RANGE SCAN          | TEST1_IDX |     2 |       |     2   (0)| 00:00:01 |
                      -----------------------------------------------------------------------------------------
                      
                      Predicate Information (identified by operation id):
                      
                      PLAN_TABLE_OUTPUT
                      -----------------------------------------------------------------------------------------
                      ---------------------------------------------------
                      
                         2 - access(NLSSORT("K1",'nls_sort=''BINARY_CI''')>=HEXTORAW('6162636400')
                                    AND NLSSORT("K1",'nls_sort=''BINARY_CI''')<HEXTORAW('6162636500') )
                      
                      15 rows selected.
                      
                      SQL> 
                      Now look at access path. Especially at HEXTORAW('6162636400'). What is '6162636400'? It is:
                      SQL> select nlssort('abcd','nls_sort=''BINARY_CI''') from dual;
                      
                      NLSSORT('A
                      ----------
                      6162636400
                      
                      SQL> 
                      In other words, optimizer is smart enough to scan literal 'abcd%' and realize wild card is at the end, so index can be used. Then it drops wild card and calculates nlssort of 'abcd' and uses it in range scan. This can't be done when using bind variables. All optimizer offers for bind variables is peeking which is not suitable in this case.

                      SY.
                      • 8. Re: Why CBO don't use function-base index when I use like and bind variable
                        Nikolay Savvinov
                        hi,

                        the optimizer doesn't have a way of knowing the cardinality exactly. so it has to guess. guessing is random. sometimes you are lucky, sometimes you're not.

                        it's not random in the scientific sense (i.e. if you run it several times on the same platform and same database version you will get the same result), so if you really want to know you can go get 10053 event optimizer tracing and find out what's going on behind the scene. but the important thing is for you to compose to query in such a way that the optimizer would be able to determine the cardinality exactly.

                        Best regards,
                        Nikolay