Re: index unused

because its slower to use the index. indexes arent always faster.

try this

select /*+ use_ind(name of index)
from blah

"Kai Zimmer" <kai_at_zimmer.net> wrote in message news:d7118738.0401110637.eca8651_at_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/teiHea der/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()').getStri ngVal()
> 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 - 11:41:12 CST