Re: index unused

BTW : Having said that FBI's arent clever, I note that

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

Actually *does* use myidx, so the FBI definition does not alway have to match the where clause exactly.

 Funny enough :

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

will not use mydix, but this one will

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

The cleverness of FBI's seems limited, so I wouldn't count on it. Any opinions, anyone ?

• Kenneth Koenraadt

On Sun, 11 Jan 2004 18:39:37 GMT, Kenneth Koenraadt wrote:

>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:48:09 CST