Ixora Answers - Best index for LIKE predicates

From:	Steve Adams
Date:	09-Nov-2001 05:39
Subject:	Best index for LIKE predicates

This looks like a database design error. I suspect that each of the handling bytes should have been separate columns. Nevertheless, the best index to support the current query is ...

        create index new_index on calypso.psevent (handling, event_class) compress;

You have to do either a full table scan or fast full index scan to satisfy those LIKE predicates, and this index will be very compact because of the compression.

The other option to consider would be a materialized view with query rewrite enabled.

I have one table with 1 million records. One column has these values.

        SQL> select distinct( HANDLING) from calypso.ps_event;

        HANDLING
        ----------------------------------------------------------
        ###########################################N#
        ###########################################Y#
        #######################################Y##Y##
        #Y###########################################
        #Y#######################################Y###
        #Y#######################N###############Y###
        #YN##########################################
        YY###########################################
        YYN##########################################

This query is executed 54000 times per day and takes all database resources. What could be advice, for indexing or otherwise to improve select performance?

        SELECT
          event_class,
          handling,
          count(*)
        FROM
          ps_event
        WHERE
          (handling LIKE 'N%') OR
          (handling LIKE '_N%') OR
          (handling LIKE '__N%') OR
          (handling LIKE '___N%') OR
          (handling LIKE '____N%') OR
          (handling LIKE '_____N%') OR
          (handling LIKE '______N%') OR
          (handling LIKE '_______N%') OR
          (handling LIKE '_______________________________________N%') OR
          (handling LIKE '________________________________________N%') OR
          (handling LIKE '_________________________________________N%') OR
          (handling LIKE '__________________________________________N%') OR
          (handling LIKE '___________________________________________N%') OR
          (handling LIKE '____________________________________________N%')
        GROUP BY
          event_class,
          handling;