Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2004
    Location
    Austria
    Posts
    5

    Question Unanswered: Problem with function based index

    Hello

    I have the following problem:
    I have a table PERSON with column LASTNAME VARCHAR2.

    Now i do a case insensitiv search form my Java-Webapp on this field lastname. (...WHERE lower(lastname) like 'xyz')

    To increase performance I wanted to create a function based index on this lastname field.

    CREATE INDEX ind_lastname_lower ON PERSON (LOWER(LASTNAME));

    The index is created but explain plan tells me that full table search is performed and my index isn't used.

    I've followed this rules but doesn't help.

    If somebody of you has experience using function based indexes, specially with JDBC please help me.

    Thanks in advance.

    Herb

  2. #2
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    In your SQL statement, include a hint for the index you've created:

    SELECT /*+ index( person ind_lastname_lower) */ lastname
    FROM person
    WHERE lower( lastname ) = 'xyz';

    Then check your explain plan again.

    JoeB

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Have you analyzed the table since you added the FBI?

  4. #4
    Join Date
    Apr 2002
    Posts
    56
    To actually use function-based indexes and not only be able to create them, the database parameter (INIT.ORA) which name is like
    'QUERY_REWRITE_ENABLE' (my spelling is probably incorrect) must be TRUE.

    Then it will appear in your execution plan (if that makes it cheaper, of course).

  5. #5
    Join Date
    Jun 2004
    Location
    Austria
    Posts
    5
    Is there any way to set the parameter query_rewrite_enabled to 'true' using JDBC?
    I don't think, sending "alter session set query_rewrite_enabled=true;" as an SQL statement via JDBC will work properly.
    Neither I think, my DBA lets me set the parameter for the whole DB.
    Would there be a way to set it only for one specific schema?

    thnx,
    Herb

  6. #6
    Join Date
    Apr 2002
    Posts
    56
    I don't know about that JDBC thing. My collegue thinks that sending the command 'ALTER SESSION ...' should work just fine.
    Did you try setting it for your session an did the index appear in the execution plan?

    It's not really a big thing though, for a DBA to change that for a database.
    If you have a version older than 9i the database must 'bounce', but most of the databases still do that occasionally.
    It's not like 'Could you double the SGA and give me the SYS password'.

  7. #7
    Join Date
    Jun 2004
    Location
    Austria
    Posts
    5
    In SQL*Plus I tried setting the parameter using ALTER SESSION ....., did an ANALYZE TABLE .... but it didn't work, explain plan showed a full table access.

    As mentioned in this thread the reason for not using the index could be that the number of rows in the table is to small and that a full table access is more efficient.

    I'll keep on trying on monday.

    Herb

  8. #8
    Join Date
    Jun 2004
    Location
    Austria
    Posts
    5
    Hi all,

    I've tried it today and it works, yeah

    Even without setting QUERY_REWRITE_ENABLED=TRUE, perhaps this is standard in our enviroment.

    Thanks for your help

    Herb

Posting Permissions

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