使用xmlDB实例
来源:互联网 发布:linux 如何卸载svn 编辑:程序博客网 时间:2024/06/05 11:38
下面的实例是参照oracle 9i的文档内容的,具体如下:
--requiment: oracle 9.2 and CTXSYS user and CTXAPP role
CREATE TABLE philip_test
(
siteid number(10) PRIMARY KEY,
sitename varchar2(100) not null,
itemname varchar2(100) not null,
itemvalue xmltype
)
XMLType COLUMN itemvalue
STORE AS clob
(TABLESPACE LOB_DATA)
TABLESPACE WBXOBJ_LARGE;
CREATE INDEX idx_siteid_itemname ON philip_test
(SITEID, itemname)
TABLESPACE WBXOBJ_LARGE_IDX;
/*CREATE INDEX idx_xml_philip ON philip_test(itemvalue)
indextype is
ctxsys.context;*/
--ctxsys.CTXXPATH;
CREATE INDEX idx_xml_philip ON philip_test
(extract(itemvalue,'/user/uid_/text()').getNumberVal());
--existsNode()---improve by CTXXPATH
--contains() ---improve by context
--1.STAT table and index
EXECUTE DBMS_STATS.GATHER_TABLE_STATS('TEST','PHILIP_TEST');
EXECUTE DBMS_STATS.GATHER_INDEX_STATS('TEST','IDX_SITEID_ITEMNAME');
EXECUTE DBMS_STATS.GATHER_INDEX_STATS('TEST','IDX_XML_PHILIP');
--2.insert data
DECLARE
v_count PLS_INTEGER:=0;
v_xml VARCHAR2(1024):='<?xml version="1.0"?>
<user>
<username>$username$</username>
<uid_>$uid_$</uid_>
<privilege>
<name_host>n</name_host>
<Host>n</Host>
<SysAdmin>ff</SysAdmin>
</privilege>
</user>';
v_temp VARCHAR2(1024);
BEGIN
FOR i IN 1..100000 LOOP
v_temp:=v_xml;
v_temp:=replace(v_temp,'$username$',i||'philip');
v_temp:=replace(v_temp,'$uid_$',i);
INSERT INTO philip_test(siteid,sitename,itemname,itemvalue)
VALUES(i,i||'philip',i||'namehost',v_temp);
v_count:=v_count+1;
IF MOD(v_count,500)=0 THEN
COMMIT;
END IF;
END LOOP;
COMMIT;
END;
/
Elapsed: 00:17:02.63
--a.INPATH checks if the given word appears within the path specified.
--b.HASPATH checks if the given XPath is present in the XML document.
UPDATE philip_test
SET ITEMVALUE =UPDATEXML(ITEMVALUE,'/user/privilege/name_host/text()','off')
where extract(ITEMVALUE,'/user/uid_/text()').getnumberVal()=1000;
1 row updated.
Elapsed: 00:00:00.53
Execution Plan
----------------------------------------------------------
0 UPDATE STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=2002)
1 0 UPDATE OF 'PHILIP_TEST'
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'PHILIP_TEST' (Cost=1 C
ard=1 Bytes=2002)
3 2 INDEX (RANGE SCAN) OF 'IDX_XML_PHILIP' (NON-UNIQUE) (C
ost=1 Card=1)
select siteid,sitename,itemname from philip_test where extract(ITEMVALUE,'/user/uid_/text()').getnumberVal()=1000
and extract(ITEMVALUE,'/user/privilege/name_host/text()').getStringVal()='off';
Elapsed: 00:00:00.53
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=2119)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'PHILIP_TEST' (Cost=1 Car
d=1 Bytes=2119)
2 1 INDEX (RANGE SCAN) OF 'IDX_XML_PHILIP' (NON-UNIQUE) (Cos
t=1 Card=1)
DELETE FROM philip_test where extract(ITEMVALUE,'/user/uid_/text()').getnumberVal()=10000;
Elapsed: 00:00:00.52
Execution Plan
----------------------------------------------------------
0 DELETE STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=2067)
1 0 DELETE OF 'PHILIP_TEST'
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'PHILIP_TEST' (Cost=1 C
ard=1 Bytes=2067)
3 2 INDEX (RANGE SCAN) OF 'IDX_XML_PHILIP' (NON-UNIQUE) (C
ost=1 Card=1)
- 使用xmlDB实例
- 改变Oracle XMLDB Ports
- Oracle XMLDB 占用了8080端口
- ORA-24248 XMLDB extensible security not installed
- ORA-24248 XMLDB extensible security not installed
- Oracle 9i以后XMLDB占用8080端口的修改
- 怎样安装和卸载 Oracle XML database (XMLDB/XDB)?
- 怎样安装和卸载 Oracle XML database (XMLDB/XDB)?
- How to deinstall and install Oracle XML database (XMLDB/XDB)? 详细
- 避免使用实例陷阱
- SAFEARRAY使用实例
- log4j使用实例
- VSS使用实例1
- Label使用实例
- classpath使用故障实例
- Timer使用实例
- 使用实例文档
- DataList使用实例
- the Secret DAILY TEACHINGS-Day 27
- How to screenshot in Linux
- 国庆小遗憾
- 再回到Linux —— Ubuntu 9.10 环境笔记
- swish-e代码分析,索引部分(1)
- 使用xmlDB实例
- 在Netbeans下配置Tomcat manager用户名与密码
- Windows7 任务栏功能的开发
- 关于数据字典的常用SQL文
- 如何从零开始开发一款嵌入式产品(20年的嵌入式经验分享学习)
- 依赖倒转原则
- cmwap与cmnet的区别
- 迪米特法则
- JS的鼠标点击效果