Re: index unused

See comments below:

P.S.
Stop cross-posting

Kai Zimmer wrote:
> 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

 > where
p.xmldoc.extract('/TEI.2/teiHeader/fileDesc/titleStmt/title/text()').getStringVal()   like 'Lexiko%';

requires a FTS Received on Sun Jan 11 2004 - 08:47:30 CST