2 Replies Latest reply: Aug 26, 2014 9:26 AM by JohnWatson RSS

    function based index on column with not like

    user8684352

      Hi all,

      DB-Version 11203:

      Query is: Select * from table where column not like 'valu%';

      column is indexed;but index will not be used because of "not like".

      SQL-Advisor recommends creating a function based index. How do I create such an index.

      Should be "create index on table (columns not like 'valu%').

       

      Any help appreciated.

       

      FJH

        • 1. Re: function based index on column with not like
          sol.beach

          user8684352 wrote:

           

          Hi all,

          DB-Version 11203:

          Query is: Select * from table where column not like 'valu%';

          column is indexed;but index will not be used because of "not like".

          SQL-Advisor recommends creating a function based index. How do I create such an index.

          Should be "create index on table (columns not like 'valu%').

           

          Any help appreciated.

           

          FJH

          It depends

           

          how many rows in table?

           

          how many rows returned by SELECT?

           

          how often does query get invoked?

          • 2. Re: function based index on column with not like
            JohnWatson

            user8684352 wrote:

             

            Hi all,

            DB-Version 11203:

            Query is: Select * from table where column not like 'valu%';

            column is indexed;but index will not be used because of "not like".

            <snip>

            You query can use an index:

            orclz>

            orclz> create index name_i on emp(ename);

             

            Index created.

             

            orclz> set autot trace exp

            orclz> select /*+index(emp name_i) */ * from emp where ename not like 'M%';

             

            Execution Plan

            ----------------------------------------------------------

            Plan hash value: 4218283247

             

            ---------------------------------------------------------------------------------

            | Id  | Operation                          | Name  | Rows  | Bytes | Cost (%CPU

            ---------------------------------------------------------------------------------

            |  0 | SELECT STATEMENT                    |        |    12 |  456 |    2  (0

            |  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP    |    12 |  456 |    2  (0

            |*  2 |  INDEX FULL SCAN                  | NAME_I |    12 |      |    1  (0

            ---------------------------------------------------------------------------------

             

            Predicate Information (identified by operation id):

            ---------------------------------------------------

             

              2 - filter("ENAME" NOT LIKE 'M%')

             

            orclz>