Re: index unused

Hi Kai,

1. You first of all need to set these parameters (either at the session level or system level) in order to user FBI's:

alter session set query_rewrite_enabled = true; alter session set query_rewrite_integrity = trusted;

2) Your FBI does not match the actal query. A FBI is usable only if the where clause is *exactly* the same as the index definition. FBI's aren't that clever:

create table mytab(col1 number not null, col2 varchar2(100));

insert into mytab (select object_id,object_name from dba_objects);

commit;

create index myidx on mytab(col1 * 4);

analyze table mytab compute statistics;
analyze index myidx compute statistics;

select *
from mytab
where somefunc(col1 * 4) > 2000;

will use myidx;

select * from mytab
where somefunc(col1 * 2) > 1000

will *not* use myidx, nor will

select * from mytab
where somefunc(col1) > 500;

3) Even when the 1) and 2) are satisfied, CBO may still find a FTS more efficient, it's only 300 rows, after all.

• Kenneth Koenraadt

On 11 Jan 2004 06:37:44 -0800, kai_at_zimmer.net (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
Received on Sun Jan 11 2004 - 12:39:37 CST