index unused

From: Kai Zimmer <kai_at_zimmer.net>
Date: 11 Jan 2004 08:41:20 -0800
Message-ID: <d7118738.0401110841.18a048bd@posting.google.com>

Hi there,

i use Oracle 9i XML DB for a small sample DB (300 records). I created a functional index (XPATH-based), but Oracle doesn't use it. Even though I tried "hints" and options on the low cost optimizer. Any hints would be greatly appreciated.

This is what I have done:

CREATE TABLE xmlDemoTable (name VARCHAR2(60), XMLDOC sys.XMLType) NOLOGGING;
...

create index ititle3 on xmldemotable p
(substr(sys.xmlType.getStringVal(sys.xmlType.extract(p.xmldoc,'/TEI.2/teiHeader/fileDesc/titleStmt/title/text()')),1,5000));

analyze table xmldemotable compute statistics for table;

analyze index ititle3 compute statistics;

select /*+ index(xmldoc ititle3) */ name from xmldemotable p where p.xmldoc.extract('/TEI.2/teiHeader/fileDesc/titleStmt/title/text()').getStringVal() like 'Lexiko%';
...

8 rows selected.
Execution Plan

---

   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2 Card=15 Bytes=
          1335)

   1    0   TABLE ACCESS (FULL) OF 'XMLDEMOTABLE' (Cost=2 Card=15 Byte
          s=1335)

Statistics

---

          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
          0  bytes sent via SQL*Net to client
          0  bytes received via SQL*Net from client
          0  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          8  rows processed

Received on Sun Jan 11 2004 - 10:41:20 CST