Creating XMLIndex and Run Testing Query

The following is how to create a XMLIndex
BEGIN
  DBMS_XMLINDEX.modifyParameter(
    'einvoiceparam',
    '
       XMLTable einvoice_idx_tab (TABLESPACE "FSMS_IDEX" ''/A0401/Invoice/Main''
         COLUMNS invoicenumber   VARCHAR2(10)  PATH ''InvoiceNumber'',
                 SellerID   VARCHAR2(8)  PATH ''Seller/Identifier'',
                 BuyerID    VARCHAR2(8)  PATH ''Buyer/Identifier''
');
END;
/


CREATE INDEX invoice_ix
ON einvoice(object_value)
INDEXTYPE IS XDB.XMLINDEX
PARAMETERS('PARAM einvoiceparam')

After XMLIndex Created,wanna to know what's the performance impact to sqlloader 
(Note: Now sqlloader cannot use direct path load only conventional path)
The command:
$ sqlldr userid=fsms/fsms@orcl control=filelist.ctl parallel=y

From filelist.log
Run began on Mon Mar 14 19:36:42 2011
Run ended on Tue Mar 15 00:06:38 2011


Elapsed time was:     04:29:53.42
CPU time was:         00:14:09.60

Run Query

00:40:05 SQL> l
  1  SELECT
  2  XMLQuery('/A0401/Invoice/Main' passing object_value returning content)
  3  from einvoice p
  4  where
  5* XMLExists('/A0401/Invoice/Main[InvoiceNumber="AA66279380"]' PASSING OBJECT_VALUE)
00:40:06 SQL>
00:40:07 SQL>
00:40:09 SQL>
00:40:09 SQL> /


XMLQUERY('/A0401/INVOICE/MAIN'PASSINGOBJECT_VALUERETURNINGCONTENT)
--------------------------------------------------------------------------------
<Main>
  <InvoiceNumber>AA71600519</InvoiceNumber>
  <InvoiceDate>
    <Year>100




00:41:14 SQL>

It still takes around 1 mins.

Execution Plan

寄件者 Oracle Spatial Working Notes

Create B-tree on XMLIndex table can boost query performance

create index einvoice_idx_tab_ix

on einvoice_idx_tab (invoicenumber);

And run the same query, you will check the cost is reduced.

Compare the cost 88 vs 7 and the query run less 1 sec.