Extensible optimizer interface: LOB-s are passed as null value.

From: <dmitry.pavlov_at_gmail.com>
Date: 22 May 2006 03:27:21 -0700
Message-ID: <1148293641.265231.179640@38g2000cwa.googlegroups.com>

Hi all,

Does somebody know some Oracle extensible optimizer limitation related to passing CLOB or BLOB object to query? I am getting null value in ODCIStatsSelectivity, ODCIStatsFunctionCost and ODCIStatsIndexCost instead of LOB locators. The values of the other datatypes (strings and numbers) are passed correctly.

For example if I create an indextype

create or replace function MyFunc (param1 in clob, param2 in clob) return NUMBER
...

create or replace operator MyOperator binding (CLOB, CLOB) return NUMBER using MyFunc;
create or replace type MyMethods as object
...

create or replace type MyStat as object
(

   ...
  static function ODCIStatsSelectivity(pred sys.ODCIPredInfo,

     sel OUT NUMBER, args sys.ODCIArgDescList, strt NUMBER, stop NUMBER,
     val CLOB, querylob CLOB, env sys.ODCIEnv) return NUMBER,   static function ODCIStatsFunctionCost(func sys.ODCIFuncInfo,

     cost OUT sys.ODCICost, args sys.ODCIArgDescList,
     val CLOB, querylob CLOB, env sys.ODCIEnv) RETURN NUMBER,
  static function ODCIStatsIndexCost(ia sys.ODCIIndexInfo,
     sel NUMBER, cost OUT sys.ODCICost, qi sys.ODCIQueryInfo,
     pred sys.ODCIPredInfo, args sys.ODCIArgDescList,
     strt NUMBER, stop NUMBER, querylob CLOB, env sys.ODCIEnv) RETURN

NUMBER,
   ...
);
create or replace indextype MyIndex for MyOperator(CLOB, CLOB) using MyMethods;
associate statistics with functions MyFunc USING MyStat; associate statistics with indextypes MyIndex USING MyStat;

then do

select clobval from ... where MyOperator(clobval, <some CLOB>)=0;

from an indexed table, the ODCIStatsSelectivity() is called with 'val' and 'querylob' parameters both equal to null. Same thing with ODCIStatsFunctionCost() and ODCIStatsIndexCost().

Can by any workaround here? Or it is a known Oracle limitation and manuals say something about it?

Thanks in advance,

                                                Dmitry

Received on Mon May 22 2006 - 05:27:21 CDT