I am using structured storage type for xmltype column. I am trying to create an index on the attribute but somehow Oracle is not taking the index and doing a full table scan. I have checked the documentation at http://docs.oracle.com/cd/B28359_01/appdev.111/b28369/xdb_indexing.htm#CHDCJIJB but it doesnot give an example on creating index on attribute. Can someone please give me an example of creating the xml index on attribute?
Thanks Kevin
you'd need to show some code here.
here's an example of creating an xmlindex on 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 would contain the child <b> node in the index but not the test2 attribute. ommitting /@test would have the index pick up the test2 attribute as well.