Results 1 to 2 of 2
  1. #1
    Join Date
    May 2008
    Posts
    24

    Unanswered: index with functions

    hi,
    i have a sql where it uses function LENGTH like below
    select * from tab1
    where
    ...
    and LENGTH(tab1.c1) = 0

    i have a index on tab1.c1, but i doubt if it can make anyuse.

    So should i have a index with LENGTH on the field or should i modify my sql.

  2. #2
    Join Date
    Sep 2010
    Location
    Germany, Brunswick
    Posts
    55
    Its role of the optimizer to use or dont use the index.

    In some cases the sequential scan might be faster.

    It also depends on the age of your statistics.

    Set explain and look if the index is used or not.

    Its not possible to create an index on build-in SQL functions, but you can create it on a stored procedure which returns the length.

    You can force the optimizer to use the index but thats again this "I dont tell you too much for free"-situation.

Posting Permissions

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