Re: Function based index not being used

From: Barbara Boehmer <baboehme_at_hotmail.com>
Date: 6 Jul 2005 20:46:40 -0700
Message-ID: <1120708000.536299.266260@g47g2000cwa.googlegroups.com>

Is query_rewrite_enabled set to true? Is query_rewrite_integrity set to trusted? Is the function actually deterministic, returning the same result given the same parameters? Just adding the keyword deterministic does not make it deterministic. Have you gathered statistics for the optimizer to use to determine the best execution plan? Please see the demonstration below.

scott_at_ORA92> CREATE USER otherUser IDENTIFIED BY otherUserPassword   2 /

User created.

scott_at_ORA92> GRANT CONNECT, RESOURCE TO otherUser   2 /

Grant succeeded.

scott_at_ORA92> CONNECT otherUser/otherUserPassword Connected.
scott_at_ORA92> @ LOGIN
scott_at_ORA92> SET ECHO OFF

GLOBAL_NAME

---

otheruser_at_ORA92

otheruser_at_ORA92> CREATE OR REPLACE FUNCTION myFunction   2 (inData VARCHAR2)
  3 RETURN VARCHAR2
  4 DETERMINISTIC
  5 AS
  6 BEGIN
  7 RETURN LOWER (inData);
  8 END myFunction;
  9 /

Function created.

otheruser_at_ORA92> SHOW ERRORS
No errors.
otheruser_at_ORA92> CONNECT scott/tiger
Connected.
otheruser_at_ORA92> @ LOGIN
otheruser_at_ORA92> SET ECHO OFF

GLOBAL_NAME

---

scott_at_ORA92

scott_at_ORA92> create table indxtest as select * from all_objects where rownum <= 1000
  2 /

Table created.

scott_at_ORA92> ALTER SESSION SET QUERY_REWRITE_ENABLED=TRUE   2 /

Session altered.

scott_at_ORA92> ALTER SESSION SET QUERY_REWRITE_INTEGRITY=TRUSTED   2 /

Session altered.

scott_at_ORA92> create index indxtest_func_idx2 on indxtest
(otherUser.myFunction (object_name))

  2 /

Index created.

scott_at_ORA92> EXECUTE DBMS_STATS.GATHER_TABLE_STATS ('SCOTT', 'INDXTEST') PL/SQL procedure successfully completed.

scott_at_ORA92> SET AUTOTRACE ON EXPLAIN
scott_at_ORA92> select * from indxtest where otherUser.myFunction
(object_name) = 'aaa'

  2 /

no rows selected

Execution Plan

---

          0
SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=125)

          1                  0

  TABLE ACCESS (BY INDEX ROWID) OF 'INDXTEST' (Cost=2 Card=1 Bytes=125)

          2                  1

    INDEX (RANGE SCAN) OF 'INDXTEST_FUNC_IDX2' (NON-UNIQUE) (Cost=1 Card=1)

scott_at_ORA92> Received on Wed Jul 06 2005 - 22:46:40 CDT