使用xmlDB实例

下面的实例是参照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)