| one problem about extensible optimizer [message #472255] |
Thu, 19 August 2010 07:43 |
hshwang
Messages: 1
Registered: August 2010
|
Junior Member |
|
|
using the function of extensible optimizer,we can define the cost of user defined function,which is implement thought the interface of ODCIStatsFunctionCost().
now,i create two object type: the first object named test_obj is that it has a attribution which is the type of nested table and two member function which are function a and function b,the second object named db_obj is mainly implement the interface of ODCIGetInterfaces and ODCIStatsFunctionCost, i define the cost of function a and function b in the funtion of ODCIStatsFunctionCost,and the cost of function a is great than the cost of function b.lastly executing sql "associate statistics with types test_obj using db_obj".
then, i create a table named t1 which has a segment named t_o whose type is test_obj.
when i want to execute the sql,such that select * from t1 t where t.t_o.a and t.t_o.b ,the optimizer shoud first execute t.t_o.b and then execute t.t_o.a ,because the cost of function b is less than the cost of function a.
but though the query plan, i can see the optimizer first execute t.t_o.a and then execute t.t_o.b. why and how to deal with this problem?
a simple test is following:
database is oracle 10g
CREATE OR REPLACE type type_nested_table IS TABLE of integer;
/
CREATE OR REPLACE type test_n AS object
(
t_n_t type_nested_table,
member FUNCTION a (n1 number) RETURN varchar2,
member FUNCTION b (n2 number) RETURN varchar2
);
CREATE OR REPLACE type body test_n IS
member FUNCTION a (n1 number) RETURN varchar2
IS
begin
IF (n1 > 10) then
RETURN 'TRUE';
else
RETURN 'FALSE';
end IF;
end;
member FUNCTION b (n2 number) RETURN varchar2
IS
begin
IF (n2 <= 10) then
RETURN 'TRUE';
else
RETURN 'FALSE';
end IF;
end;
end;
/ CREATE TABLE t1(id number,t_n test_n) nested TABLE t_n.t_n_t store AS t_ex;
/
begin
FOR i IN 1..20 loop
INSERT INTO t1 VALUES(i,test_n(type_nested_table(i)));
end loop;
end;
CREATE OR REPLACE type dbtest AS object
(
num number,
STATIC FUNCTION ODCIGetInterfaces(ifclist OUT sys.ODCIObjectList) RETURN NUMBER,
STATIC FUNCTION ODCIStatsFunctionCost(func sys.ODCIFuncInfo,
cost OUT sys.ODCICost, args sys.ODCIArgDescList,
p number, env sys.ODCIEnv) RETURN NUMBER,
PRAGMA restrict_references(ODCIStatsFunctionCost, WNDS, WNPS)
);
CREATE OR REPLACE type body dbtest IS
STATIC FUNCTION ODCIGetInterfaces(ifclist OUT sys.ODCIObjectList) RETURN NUMBER IS
BEGIN
ifclist := sys.ODCIObjectList(sys.ODCIObject('SYS','ODCISTATS2'));
RETURN ODCIConst.Success;
END ODCIGetInterfaces;
STATIC FUNCTION ODCIStatsFunctionCost(func sys.ODCIFuncInfo,
cost OUT sys.ODCICost, args sys.ODCIArgDescList,
p number ,env sys.ODCIEnv) RETURN NUMBER IS
fname VARCHAR2(30);
BEGIN
cost := sys.ODCICost(NULL, NULL, NULL, NULL);
IF bitand(func.Flags, ODCIConst.ObjectFunc) > 0 THEN
fname := upper(func.ObjectName);
ELSE
fname := upper(func.MethodName);
END IF;
IF fname LIKE upper('%a') THEN
cost.CPUCost := 100;
RETURN ODCIConst.Success;
elsif fname LIKE upper('%b%') THEN
cost.CPUCost := 1;
RETURN ODCIConst.Success;
else RETURN ODCIConst.Error;
end IF;
END ODCIStatsFunctionCost;
end;
/
associate statistics WITH types test_n USING dbtest;
/
SELECT * FROM t1 t WHERE t.t_n.a(15) = 'FALSE' AND t.t_n.b(5) = 'TRUE' ; -- it is also first execute t.t_n.a() and then execte t.t_n.b().
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
