function based index problem
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: function based index problem

  1. #1
    Join Date
    Mar 2001
    Posts
    109
    I created a function based index by using:

    create unique index UQ_TABLE1_NAME on TABLE1 ( upper(NAME) );

    Then tried to run a simple query with SQLPLUS:

    SELECT * FROM table1 WHERE UPPER(name) LIKE UPPER('%abcd%') ORDER BY UPPER(name);

    I got a error:

    ORA-03113: end-of-file on communication channel

    The strange thing is running "SELECT * FROM table1 WHERE name LIKE UPPER('%abcd%') ORDER BY UPPER(name)" or "SELECT * FROM table1 WHERE UPPER(name) LIKE UPPER('%abcd%') ORDER BY name" OK. I also removed the index and re_created it, then the problem disappeared.

    Does any one konw how this happens.

    Thanks.

    zm

  2. #2
    Join Date
    Mar 2001
    Posts
    109
    when checked the dump trace file, found :

    ORA-07445: exception encountered: core dump [evaopn2()+1104] [SIGSEGV] [Address not mapped to object] [0x0] [] []

    zm

  3. #3
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    Did u retry the same old statement after u got connected.Try the same old command,i guess the problem was with the memory mapping to the corresponding object.

    regards
    anandkl
    anandkl

  4. #4
    Join Date
    Mar 2001
    Posts
    109
    got same problem when I got reconnected. The strang thing is that if I used only ONE upper function in the query, either in WHERE or ORDER BY, didn't have any problem.
    zm

  5. #5
    Join Date
    Aug 2000
    Location
    Belgium
    Posts
    342
    found on metalink :

    fact: Oracle Server Enterprise Edtion 9.0.1.0.1
    symptom: Errors selecting from a table
    symptom: ORA-7445: exception encountered: core dump [evaopn2()+1104]
    [SIGSEGV] [Address not mapped to object] [0X0] [] []
    symptom: DBMS_STATS have been gathered for the table
    symptom: Optimizer mode set to cost based
    symptom: Column constraint of UPPER used in table
    cause:
    ORA-7445: [EVAOPN2()+1104] error reported when select
    statement using cost based optimizer and statistics is also on the table

    fix:

    Fixed in RDBMS 9.2

    Workarounds:
    Use Rule based optimizer
    or
    Set parameter optimizer_features_enable=8.1.7
    or
    Set parameter compatible="8.1.7"
    or
    Set ALTER SESSION SET EVENTS '10195 trace name context forever,level 10';



  6. #6
    Join Date
    Apr 2002
    Location
    Phoenix, AZ
    Posts
    175
    There have been problems with function based indexed in both initial versions of 8.x and 9.x.

    Refer to patch read files whether there is a fix for Function Indexes. 9.2 should probably fix the problem
    Sridhar R Patnam

  7. #7
    Join Date
    Jun 2003
    Posts
    1
    Analyse the tables statistics after creating the index and this may sort it out.

  8. #8
    Join Date
    Nov 2000
    Posts
    440
    you need the following parameter to use function-based index:

    query_rewrite_enabled = TRUE
    query_rewrite_integrity = TRUSTED

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by msassoon
    Analyse the tables statistics after creating the index and this may sort it out.
    DBMS_STATS allso has problems with FBI's -- might have to use the old ANALYZE statement there.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

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



Click Here to Expand Forum to Full Width