Oracle xmltype column indexing based on xml attribute

Question

I am using a structured storage type for an xmltype column. I am trying to create an index for an attribute, but somehow Oracle does not accept the index and does a full table scan. I checked the documentation at http://docs.oracle.com/cd/B28359_01/appdev.111/b28369/xdb_indexing.htm#CHDCJIJB , but does not give an example of creating an index by attribute. Can someone please give me an example of creating an xml index for an attribute?

Thanks Kevin

Answer 1

You will need to show the code here.

here is an example of creating xmlindex for an attribute (and its descendant nodes):

 SQL> create table xmltest ( a xmltype); Table created. SQL> insert into xmltest values ('<root> 2 <a test="foo" test2="asd"> 3 <b>hi</b> 4 </a> 5 <a test="foo2" test2="asd2">hi</a> 6 </root>'); 1 row created. SQL> commit; Commit complete. SQL> create index test_xmlindex on xmltest (a) 2 indextype is XDB.XMLIndex 3 parameters ('paths (include (/root/a/@test))'); Index created. SQL> set pagesize 100 lines 200 heading off SQL> explain plan for select * from xmltest where xmlexists('/root/a[@test="foo"]' passing a); Explained. SQL> select * from table(dbms_xplan.display()); Plan hash value: 2925043098 ------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 2026 | 6 (17)| 00:00:01 | | 1 | NESTED LOOPS | | 1 | 2026 | 6 (17)| 00:00:01 | | 2 | VIEW | VW_SQ_1 | 1 | 12 | 4 (0)| 00:00:01 | | 3 | HASH UNIQUE | | 1 | 5046 | | | | 4 | NESTED LOOPS | | | | | | | 5 | NESTED LOOPS | | 1 | 5046 | 4 (0)| 00:00:01 | |* 6 | TABLE ACCESS BY INDEX ROWID| SYS82641_TEST_XMLIN_PATH_TABLE | 1 | 3524 | 2 (0)| 00:00:01 | |* 7 | INDEX RANGE SCAN | SYS82641_TEST_XMLIN_VALUE_IX | 1 | | 1 (0)| 00:00:01 | |* 8 | INDEX RANGE SCAN | SYS82641_TEST_XMLIN_PIKEY_IX | 1 | | 1 (0)| 00:00:01 | |* 9 | TABLE ACCESS BY INDEX ROWID | SYS82641_TEST_XMLIN_PATH_TABLE | 1 | 1522 | 2 (0)| 00:00:01 | | 10 | TABLE ACCESS BY USER ROWID | XMLTEST | 1 | 2014 | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------------------- 

by indexing paths (include (/root/a/@test)) it will contain the child <b> node in the index, but not the test2 attribute. ommitting /@test will have an index that will also receive the test2 attribute.