Home » SQL & PL/SQL » SQL & PL/SQL » Function-based index not used
Function-based index not used [message #212638] Sat, 06 January 2007 23:17 Go to next message
Messages: 20
Registered: November 2006
Junior Member
Hello -

I am trying to use a function-based index on an object table that contains several subtypes, but the optimizer seems to completely ignore the index (even when I use the hint /*+). It just goes for a full table scan.
The function is declared deterministic, query_rewrite_enabled is set to true, query_rewrite_integrity is set to trusted and I gathered statistics on the table in question.

I am starting to suspect that it is the fact that the function is defined for some of the subtypes only. Other subtypes in the table do not have that function defined. Any clues would be appreciated.

Here is the query:
SELECT /*+ INDEX_ASC(p salary_idx) */
        p.name, p.street, p.city, p.state, p.zipcode
FROM PersonTab p
WHERE VALUE(p) IS OF (EmployeeType)
        and Treat (Ref(p) AS Ref EmployeeType).salary() >= 96000;

All employyetype subtypes have the salary function defined, but other subtypes of persontype do not.

The index:
CREATE INDEX salary_idx ON personTab p
   (TREAT(VALUE(p) AS employeeType).salary() ASC);

Re: Function-based index not used [message #212705 is a reply to message #212638] Sun, 07 January 2007 20:50 Go to previous messageGo to next message
Messages: 3727
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I've never worked with object types like this, but if function-based indexes work the same way in Object Relational as in regular relational tables, then the problem is that the syntax is different between the index and the SQL:

SQL:    TREAT(REF  (p) AS Ref EmployeeType).salary()
Index:  TREAT(VALUE(p) AS     EmployeeType).salary()
              *****       ***

Function-based indexes typically only work when the SQL syntax is identical to the index declaration. Again though, I don't know whether these rules vary with Object Relational.

Ross Leishman
Re: Function-based index not used [message #213677 is a reply to message #212705] Thu, 11 January 2007 17:02 Go to previous message
Messages: 20
Registered: November 2006
Junior Member
Thanks Ross - You got a point there, but even when I change the SQL to look like the index (Value instead of REF), it does not work. Interestingly, when I try to create an index on REF, this is what happens:
SQL> CREATE INDEX salary_idx ON personTab p
  2  (TREAT(REF(p) as ref employeeType).salary() ASC);
(TREAT(REF(p) as ref employeeType).salary() ASC)
ERROR at line 2:
ORA-01743: only pure functions can be indexed

Elapsed: 00:00:00.01


I believe by "pure function", it refers to TREAT and not to salary().
Previous Topic: Mview with primary key table and no primary key table
Next Topic: ORA-01422: exact fetch returns more than requested number of rows
Goto Forum:

Current Time: Mon Jan 23 05:13:49 CST 2017

Total time taken to generate the page: 0.18287 seconds