Deterministic functions not recognized by optimizer - will they ever?

I am surely not the last to find out that deterministic PL/SQL functions are not recognized as such by the optimizer, contrary to what I was led to expect on two grounds:

1. using common sense, and
2. reading chapter 9 of Application Developer's Gude - Fundamentals.

At least it doesn't give any performace gain on 8.1.5 and 8.1.7.2 on AIX, and according to bug # 2070151 on MetaLink it doesn't work in 9.0.1 either. Testcase is supplied below.

I am wondering whether any of you experts out there have any view upon whether it ever has worked or will work in the forseeable future?

Regards to all, Hans Henrik Krohn

TESTCASE, to be cut/pasted into sql*plus:

• 1) Create a package containing a very deterministic function: (could just as well be a stand-alone function) create or replace package TESTD is function fortyseven (pNum IN number) return number PARALLEL_ENABLE DETERMINISTIC; PRAGMA RESTRICT_REFERENCES (fortyseven, WNDS, RNDS, WNPS, RNPS, TRUST); END TESTD; / create or replace package body TESTD is function fortyseven (pNum IN number) return number is begin return 47; end; END TESTD; /
• 2) Create a test-table containing 20.000 values: create table test_determ as select a1.object_id from all_objects a1, all_objects a2 where rownum <= 20000; commit;
• 3) Run two selects, and note the difference in execution time: set timing on select count (1) from test_determ where object_id != to_number ('47'); select count (1) from test_determ where object_id != TESTD.fortyseven (47);

--4) That's all, Folks! Received on Mon Apr 15 2002 - 08:58:06 CDT