Oracle扩展索引示例

最新推荐文章于 2023-06-28 23:06:20 发布

boboSir

最新推荐文章于 2023-06-28 23:06:20 发布

阅读量1.7k

点赞数

CC 4.0 BY-SA版权

分类专栏：数据库类索引 Data Cartridge 文章标签： oracle integer function sql 扩展 null

本文链接：https://blog.youkuaiyun.com/liaojiabo/article/details/8021166

数据库类同时被 3 个专栏收录

12 篇文章

订阅专栏

索引

1 篇文章

订阅专栏

Data Cartridge

1 篇文章

订阅专栏

在开始之前，用户需要权限有：

Resource

connect

createview

createoperator

createindextype

1 创建测试用的数据表：

createtable TTT

(

PID INTEGERnotnull,

PERSIONID VARCHAR2(18),

RPERSIONID VARCHAR2(100),

NAME VARCHAR2(10),

RNAME VARCHAR2(100),

BALANCE NUMBER

);

2 创建辅助的Oracle包

CREATEORREPLACEPACKAGE power_pkg AS
is_debug number := 0;
TYPE rowidcurISREFCURSOR;
/*
编码函数－单条记录,自己去想招，最重要能保持有序(可以搞个一字节变多字节算法)
有些特殊字符还得考虑一下
*/
FUNCTION Encode(p_base64INVARCHAR2)RETURNVARCHAR2;
/*
BASE64解码函数－单条
*/
FUNCTION Decode(p_base64INVARCHAR2)RETURNVARCHAR2;

FUNCTION getversion(idxschemaINVARCHAR2,
idxname INVARCHAR2,
newblock OUTPLS_INTEGER)RETURNVARCHAR2;
PROCEDURE checkversion(versionINVARCHAR2);

PROCEDURE ODC_Print(in_print_strINVARCHAR2);

FUNCTION ODC_SetDebug(in_isdebugINNUMBER) RETURNNUMBER;

--添加一个casttoraw的函数索引
PROCEDURE Proc_AddNativeIndex(in_SchemaNameINVARCHAR2,
in_TableName INVARCHAR2,
in_FieldName INVARCHAR2,
in_Unique INNUMBERDEFAULT0,
in_IndexName INVARCHAR2DEFAULTNULL);
--干掉这个本地的函数索引
PROCEDURE Proc_DropNativeIndex(in_SchemaNameINVARCHAR2,
in_TableName INVARCHAR2
);

END power_pkg;
/
CREATEORREPLACEPACKAGEBODY power_pkg AS
-- iterate is a package-level variable used to maintain state across calls
-- by Export in this session.
iterate NUMBER := 0;

/*
编码函数－单条记录,自己去想招，最重要能保持有序(可以搞个一字节变多字节算法)
有些特殊字符还得考虑一下
*/
FUNCTION Encode(p_base64INVARCHAR2)RETURNVARCHAR2
IS
l_len PLS_INTEGER;
l_base64 VARCHAR2(32767) := p_base64;
l_raw VARCHAR2(32767);--raw(24576);
-- base64 values
base64_before PLS_INTEGER;
base64_after PLS_INTEGER;
result_base PLS_INTEGER;

BEGIN
NULL;
RETURN UTL_RAW.cast_to_varchar2(l_raw);
END Encode;

/*
BASE64解码函数－单条
*/
FUNCTION Decode(p_base64INVARCHAR2)RETURNVARCHAR2
IS
l_len PLS_INTEGER;
l_base64 VARCHAR2(32767) := p_base64;
l_raw VARCHAR2(32767);--raw(24576);
-- base64 values
base64_before PLS_INTEGER;
base64_after PLS_INTEGER;
result_base PLS_INTEGER;

BEGIN
NULL;
RETURN UTL_RAW.cast_to_varchar2(l_raw);
END Decode;

FUNCTION getversion(idxschemaINVARCHAR2,
idxname INVARCHAR2,
newblock OUTPLS_INTEGER)RETURNVARCHAR2IS
BEGIN
-- We are generating only one PL/SQL block consisting of one line of code.
newblock :=1;
IF iterate =0THEN
-- Increment iterate so we'll know we're done next time we're called.
iterate := iterate +1;
-- Return a string that calls checkversion with a version 'V1.0'
-- Note that export adds the surrounding BEGIN/END pair to form the anon.
-- block... we don't have to.
RETURN'power_pkg.checkversion(''V1.0'');';
ELSE
-- reset iterate for next index
iterate :=0;
-- Return a 0-length string; we won't be called again for this index.
RETURN'';
ENDIF;
END getversion;

--检查版本号
PROCEDURE checkversion(versionINVARCHAR2)IS
wrong_versionEXCEPTION;
BEGIN
IF version !='V1.0'THEN
RAISE wrong_version;
ENDIF;
END checkversion;

--打印调试信息
PROCEDURE ODC_Print(in_print_strINVARCHAR2)IS
BEGIN
IF is_debug=0then
RETURN;
ELSE
DBMS_OUTPUT.PUT_LINE(in_print_str);
ENDIF;
END ODC_Print;

--决定是否打印调试信息
FUNCTION ODC_SetDebug(in_isdebugINNUMBER)RETURNNUMBERIS
BEGIN
is_debug := in_isdebug;
return is_debug;
END ODC_SetDebug;

PROCEDURE Proc_AddNativeIndex(in_SchemaNameINVARCHAR2,
in_TableName INVARCHAR2,
in_FieldName INVARCHAR2,
in_UniqueINNUMBERDEFAULT0,
in_IndexName INVARCHAR2DEFAULTNULL
)
AS
PRAGMAAUTONOMOUS_TRANSACTION;
l_indexsqlVARCHAR2(512):='';
l_indexnameVARCHAR2(30):='';
--l_nativeindex VARCHAR2(30):='';
--l_domainindex VARCHAR2(30):='';
--l_fieldid PLS_INTEGER:=0;
--l_checkindex_sql VARCHAR2(512):='';
--l_insert_sql VARCHAR2(512):='';
--my_cur rowidcur;
l_object_maxlenPLS_INTEGER:=30;
--l_havenativeindex PLS_INTEGER:=0;
l_retnum PLS_INTEGER:=0;
BEGIN

IF in_IndexNameISNOTNULLTHEN
l_indexname:=in_IndexName;
ELSE
l_indexname:='IDX#'||dbms_random.string('X',6);
ENDIF;
--l_indexname:='IDX#'||l_fieldid;
IF lengthb(l_indexname)>l_object_maxlenTHEN
l_indexname:='IDX#'||dbms_random.string('X',14);
ENDIF;

--创建相关的函数索引
IF in_Unique>0THEN
--是唯一性索引,无论是否有分区，都要创建全局索引
l_indexsql:='CREATE UNIQUE INDEX '||in_SchemaName||'.'||l_indexname||' ON ';
--IF COFFER.ODC_PACK_EIMANAGER.Func_CheckCanUseFBI(l_fieldid)=1 THEN
l_indexsql:=l_indexsql||in_SchemaName||'.'||in_TableName||'FUNC_INDEX('||in_FieldName||'))';
ELSE
l_indexsql:='CREATE INDEX '||in_SchemaName||'.'||l_indexname||' ON ';
l_indexsql:=l_indexsql||in_SchemaName||'.'||in_TableName||'(FUNC_INDEX('||in_FieldName||'))';
ENDIF;
ODC_Print(l_indexsql);
EXECUTEIMMEDIATE l_indexsql;

END Proc_AddNativeIndex;

--干掉这个本地的函数索引
PROCEDURE Proc_DropNativeIndex(in_SchemaNameINVARCHAR2,
in_TableName INVARCHAR2
)
AS
l_nativeIndexNameVARCHAR2(32) :='';
l_sql VARCHAR2(2000) :='';
BEGIN
select t1.index_nameINTO l_nativeIndexNamefrom user_indexes t1
where t1.index_type='FUNCTION-BASED NORMAL'and t1.table_name=in_TableName;
IF l_nativeIndexName =''THEN
RETURN;
ELSE
--有兴趣的童鞋去实现一下吧，找个中间存一下，然后再查
--l_sql:='drop index '||in_SchemaName||'.'||l_nativeIndexName;
null;
ENDIF;
POWER_PKG.ODC_Print(l_sql);
executeimmediate l_sql;

END Proc_DropNativeIndex;

END power_pkg;
/

3 创建三个中间的辅助函数

--用于将数据转换成raw存储到ttt表
CREATEORREPLACEFUNCTION varToRaw(acc_var INvarchar2)
RETURNvarchar2DETERMINISTIC
IS acc_balVARCHAR2(2000);
BEGIN
acc_bal:=UTL_RAW.cast_to_raw(acc_var);
RETURN(acc_bal);
END;
/
--用于视图的查询返回，此函数并用于创建操作符
CREATEORREPLACEFUNCTION rawTovar(acc_var INvarchar2)
RETURNvarchar2DETERMINISTIC
IS acc_balVARCHAR2(2000);
BEGIN
acc_bal:=UTL_RAW.cast_to_varchar2(acc_var);
RETURN(acc_bal);
END;
/
--此函数用于创建函数索引，扩展索引内部通过此查询来进行一个加速
CREATEORREPLACEFUNCTION func_index(acc_var INvarchar2)
RETURNvarchar2DETERMINISTIC
IS acc_balVARCHAR2(2000);
BEGIN
acc_bal:=power_pkg.encode(utl_raw.cast_to_varchar2(acc_var));
RETURN(acc_bal);
END;
/

4 创建索引类型

--没有太多好的，固定定义，但是有些的函数参数是比较灵活的，可以综合利用
CREATEORREPLACETYPE power_idxtype_im ASOBJECT
(
curnum NUMBER,
howmany NUMBER,
cacheflag INTEGER,
scan_curnum NUMBER,
scan_fetchnumNUMBER,

STATICFUNCTION ODCIGetInterfaces(ifclistOUT sys.ODCIObjectList)
RETURNNUMBER,

STATICFUNCTION ODCIIndexCreate(ia sys.ODCIIndexInfo,
parms VARCHAR2,
env sys.ODCIEnv) RETURNNUMBER,
STATICFUNCTION ODCIIndexDrop(ia sys.ODCIIndexInfo, env sys.ODCIEnv)
RETURNNUMBER,

STATICFUNCTION ODCIIndexStart(sctx INOUT power_idxtype_im,
ia sys.ODCIIndexInfo,
op sys.ODCIPredInfo,
qi sys.ODCIQueryInfo,
strt VARCHAR2,
stop VARCHAR2,
env sys.ODCIEnv) RETURNNUMBER,

MEMBERFUNCTION ODCIIndexFetch(
selfINOUT POWER_IDXTYPE_IM,
nrows NUMBER,
rids OUT sys.ODCIRidList,
env sys.ODCIEnv) RETURNNUMBER,

MEMBERFUNCTION ODCIIndexClose(env sys.ODCIEnv)RETURNNUMBER,

STATICFUNCTION ODCIIndexInsert(ia sys.ODCIIndexInfo,
rid VARCHAR2,
env sys.ODCIEnv) RETURNNUMBER,

STATICFUNCTION ODCIIndexDelete(ia sys.ODCIIndexInfo,
rid VARCHAR2,
env sys.ODCIEnv) RETURNNUMBER,

STATICFUNCTION ODCIIndexUpdate(ia sys.ODCIIndexInfo,
rid VARCHAR2,
env sys.ODCIEnv) RETURNNUMBER,

STATICFUNCTION ODCIIndexGetMetadata(ia sys.ODCIIndexInfo,
expversion VARCHAR2,
newblock OUTPLS_INTEGER,
env sys.ODCIEnv)
RETURNVARCHAR2
);
/
CREATEORREPLACETYPEBODY power_idxtype_im IS

STATICFUNCTION ODCIGetInterfaces(ifclistOUT sys.ODCIObjectList)
RETURNNUMBERIS
BEGIN
ifclist := sys.ODCIObjectList(sys.ODCIObject('SYS','ODCIINDEX2'));
return ODCIConst.Success;
END ODCIGetInterfaces;

STATICFUNCTION ODCIIndexCreate(ia sys.ODCIIndexInfo,
parms VARCHAR2,
env sys.ODCIEnv) RETURNNUMBERIS
l_schemanameVARCHAR2(32):='';
l_tablenameVARCHAR2(32):='';
l_fieldnameVARCHAR2(32):='';
l_indexnameVARCHAR2(32):='';
l_categoryNUMBER:=0;
l_isuniqueNUMBER:=0;
l_tablepartitionVARCHAR2(32):='';
l_indexpartitionVARCHAR2(32):='';
BEGIN
sys.ODCIIndexInfoDump(ia);

IF UPPER(parms)='UNIQUE'THEN
l_isunique:=1;
ENDIF;
l_schemaname:=ia.IndexCols(1).TableSchema;
l_tablename:=ia.IndexCols(1).TableName;
l_fieldname:=replace(ia.IndexCols(1).ColName,'"','');
l_indexname:=ia.IndexName;
POWER_PKG.ODC_Print('SCHEMA：'||l_schemaname);
POWER_PKG.ODC_Print('TABLE：'||l_tablename);
POWER_PKG.ODC_Print('FIELD：'||l_fieldname);
POWER_PKG.ODC_Print('INDEX NAME：'||l_indexname);

IF UPPER(parms)='NODATA'THEN
--不生成索引数据，直接返回成功
POWER_PKG.ODC_Print('ODCIIndexCreate<<<<<');
RETURN ODCICONST.SUCCESS;
ELSE
--底层函数索引的名为随机字符串
POWER_PKG.Proc_AddNativeIndex(
in_SchemaName => l_schemaname,
in_TableName => l_tablename,
in_FieldName => l_fieldname,
in_Unique => l_isunique,
in_Indexname => '');
ENDIF;
RETURN ODCICONST.SUCCESS;
END ODCIIndexCreate;

STATICFUNCTION ODCIIndexDrop(ia sys.ODCIIndexInfo, env sys.ODCIEnv)
RETURNNUMBERIS
l_exec_sql VARCHAR2(2000);
l_schemanameVARCHAR2(32):='';
l_tablenameVARCHAR2(32):='';
l_fieldnameVARCHAR2(32):='';
l_indexnameVARCHAR2(32):='';
--cnum INTEGER;
--junk INTEGER;
BEGIN
-- Construct the SQL statement.
POWER_PKG.ODC_Print('sys.ODCIIndexInfoDump(ia);');
sys.ODCIIndexInfoDump(ia);
--sys.Odcienvdump(env);
l_schemaname:=ia.IndexCols(1).TableSchema;
l_tablename:=ia.IndexCols(1).TableName;
l_fieldname:=replace(ia.IndexCols(1).ColName,'"','');
l_indexname:=ia.IndexName;

POWER_PKG.ODC_Print('SCHEMA：'||l_schemaname);
POWER_PKG.ODC_Print('TABLE：'||l_tablename);
POWER_PKG.ODC_Print('FIELD：'||l_fieldname);
POWER_PKG.ODC_Print('INDEX NAME：'||l_indexname);

--先干掉这个domainIndex
l_exec_sql:='drop index '||l_schemaname||'.'||l_indexname;
executeimmediate l_exec_sql;

--然后跟着干掉这个函数索引
POWER_PKG.Proc_DropNativeIndex(in_SchemaName => l_schemaname,
in_TableName => l_tablename);

RETURN ODCICONST.SUCCESS;
END ODCIIndexDrop;

STATICFUNCTION ODCIIndexStart(sctx INOUT power_idxtype_im,
ia sys.ODCIIndexInfo,
op sys.ODCIPredInfo,
qi sys.ODCIQueryInfo,
strt VARCHAR2,
stop VARCHAR2,
env sys.ODCIEnv) RETURNNUMBERIS
cnum INTEGER;
rid ROWID;
nrows INTEGER;
relop VARCHAR2(2);
stmt VARCHAR2(1000);
conditionstrvarchar2(100):='';
l_schemanameVARCHAR2(32):='';
l_tablenameVARCHAR2(32):='';
l_fieldnameVARCHAR2(32):='';
BEGIN
--完成一些初如化工作
POWER_PKG.ODC_Print('ODCIIndexStart>>>>>');
sys.ODCIIndexInfoDump(ia);
POWER_PKG.ODC_Print('start key : ' || strt);
POWER_PKG.ODC_Print('stop key : ' || stop);

POWER_PKG.ODC_Print('dump ENV,dump op,dump qi');
--sys.Odcienvdump(env);
sys.ODCIPredInfoDump(op);
--sys.Odciqueryinfodump(qi);
--获取相关的模式名、表名、字段名
l_schemaname:=ia.IndexCols(1).TableSchema;
l_tablename:=ia.IndexCols(1).TableName;
l_fieldname:=replace(ia.IndexCols(1).ColName,'"','');

--接下来才是真正的开工
--如查是精确查询
IF (bitand(op.Flags, ODCIConst.PredExactMatch)
= ODCIConst.PredExactMatch)
THEN
conditionstr := POWER_PKG..encode(strt);
stmt := 'select /*+First_rows */rowid from '||l_schemaname||'.'||l_tablename||' where '
||'POWER_PKG.FUNC_INDEX('||l_fieldname||')='''||conditionstr||'''';
POWER_PKG.ODC_Print(stmt);
cnum := dbms_sql.open_cursor;
dbms_sql.parse(cnum, stmt, dbms_sql.native);
-- Set context as the cursor number.
sctx := POWER_IDXTYPE_IM(cnum,0,0,0,0);
ELSE
--如果是模糊查询，而且当前只支持到这个前缀的查询
IF (bitand(op.Flags, ODCIConst.PredPrefixMatch)
= ODCIConst.PredPrefixMatch)
THEN
conditionstr:=substr(strt,1,length(strt)-1);
conditionstr := COFFER.odc_pack_fi.encode(conditionstr);
stmt :='select /*+First_rows */rowid from '||l_schemaname||'.'||l_tablename||' where '
||'POWER_PKG.FUNC_INDEX('||l_fieldname||') like '''||conditionstr||'%''';
POWER_PKG.ODC_Print(stmt);
cnum := dbms_sql.open_cursor;
dbms_sql.parse(cnum, stmt, dbms_sql.native);
-- Set context as the cursor number.
sctx := POWER_IDXTYPE_IM(cnum,0,0,0,0);
ELSE
POWER_PKG.ODC_Print('暂时只支持等值与模糊查询,其他条件查询，自己去查相关资料');
ENDIF;
ENDIF;
POWER_PKG.ODC_Print('native searchSQL='||stmt);
RETURN ODCICONST.SUCCESS;
END ODCIIndexStart;

MEMBERFUNCTION ODCIIndexFetch(selfINOUT POWER_IDXTYPE_IM,
nrows NUMBER,
rids OUT sys.ODCIRidList,
env sys.ODCIEnv) RETURNNUMBERIS
cnum INTEGER;
idx INTEGER := 1;
rlist sys.ODCIRidList := sys.ODCIRidList();
done boolean := FALSE;

l_rid ROWID:='';
rid_tab DBMS_SQL.Varchar2_Table;
r_num INTEGER:=0;

counter INTEGER:=1;
l_totalcostINTEGER:=0;
l_getnum_constINTEGER:=200;
BEGIN
POWER_PKG.ODC_Print('ODCIIndexFetch>>>>>');
POWER_PKG.ODC_Print('Nrows : ' || round(nrows));
cnum := self.curnum;
--官方方式
/*
WHILE not done LOOP
if idx > nrows then
done := TRUE;
else
rlist.extEND;
if dbms_sql.fetch_rows(cnum) > 0 then
dbms_sql.column_value_rowid(cnum, 1, rlist(idx));
idx := idx + 1;
else
rlist(idx) := null;
done := TRUE;
END if;
END if;
END LOOP;
*/
--coffer方式
IFself.howmany =0THEN
dbms_sql.define_array(cnum,1, rid_tab, l_getnum_const,1);
--dbms_sql.define_array(cnum, 1, rid_tab, nrows, 1);
r_num := DBMS_SQL.EXECUTE(cnum);
--DBMS_OUTPUT.put_line('r_num='||r_num);
ENDIF;
r_num := DBMS_SQL.FETCH_ROWS(cnum);
--Odc_Pack_Util.ODC_Print('num:'||r_num,1);

IF r_num = l_getnum_constTHEN
rlist.extend(r_num);
ELSE
rlist.extend(r_num+1);
--rlist(r_num+1):=NULL;
ENDIF;

DBMS_SQL.COLUMN_VALUE(cnum,1, rid_tab);
--COFFER.ODC_PACK_UTIL.ODC_Print('rid_tab:'||rid_tab.count||' '||rid_tab.first);
for iin1..r_numloop
rlist(i) := rid_tab(i+SELF.howmany);
--rlist(i) := rid_tab(i);
--COFFER.ODC_PACK_UTIL.ODC_Print('rlist(i):'||rlist(i));
endloop;
--IF r_num != l_getnum_const THEN
--rlist(r_num+1):=NULL;
--ELSE
SELF.howmany :=SELF.howmany + r_num;
--end coffer type
rids := rlist;
RETURN ODCICONST.SUCCESS;
END ODCIIndexFetch;

MEMBERFUNCTION ODCIIndexClose(env sys.ODCIEnv)RETURNNUMBERIS
cnum INTEGER;
BEGIN
POWER_PKG.ODC_Print('ODCIIndexClose>>>>>');
cnum := self.curnum;
dbms_sql.close_cursor(cnum);
RETURN ODCICONST.SUCCESS;
END ODCIIndexClose;

STATICFUNCTION ODCIIndexInsert(ia sys.ODCIIndexInfo,
rid VARCHAR2,
env sys.ODCIEnv) RETURNNUMBERAS
cid INTEGER;
i BINARY_INTEGER;
nrows INTEGER;
stmt VARCHAR2(1000);
BEGIN
POWER_PKG.ODC_Print(' ');
sys.ODCIIndexInfoDump(ia);
-- Construct the statement,just do nothing

RETURN ODCICONST.SUCCESS;
END ODCIIndexInsert;

STATICFUNCTION ODCIIndexDelete(ia sys.ODCIIndexInfo,
rid VARCHAR2,
env sys.ODCIEnv) RETURNNUMBERAS
cid INTEGER;
stmt VARCHAR2(1000);
nrows INTEGER;
BEGIN
POWER_PKG.ODC_Print(' ');
sys.ODCIIndexInfoDump(ia);
-- Construct the statement,just do nothing

RETURN ODCICONST.SUCCESS;
END ODCIIndexDelete;

STATICFUNCTION ODCIIndexUpdate(ia sys.ODCIIndexInfo,
rid VARCHAR2,
env sys.ODCIEnv) RETURNNUMBERAS
cid INTEGER;
cid2 INTEGER;
stmt VARCHAR2(1000);
stmt2 VARCHAR2(1000);
nrows INTEGER;
i NUMBER;
BEGIN
POWER_PKG.ODC_Print(' ');
sys.ODCIIndexInfoDump(ia);
-- Delete old entries,just do nothing

RETURN ODCICONST.SUCCESS;
END ODCIIndexUpdate;

STATICFUNCTION ODCIIndexGetMetadata(ia sys.ODCIIndexInfo,
expversion VARCHAR2,
newblock OUTPLS_INTEGER,
env sys.ODCIEnv)
RETURNVARCHAR2IS
BEGIN
-- Let getversion do all the work since it has to maintain state across calls.
RETURN power_pkg.getversion(ia.IndexSchema, ia.IndexName, newblock);
EXCEPTION
WHENOTHERSTHEN
RAISE;
END ODCIIndexGetMetaData;

END;
/