Index Full Scan used for DBMS_STATS Query even though there is a INDEX_FFS hint in Query (Doc ID 1073987.1)

Last updated on APRIL 06, 2015

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.6 to 11.2.0.3 [Release 11.1 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 25-Jan-2012***

Symptoms

When gathering stats on indexes like below, some indexes take an extreme amount of time for statistics to be gathered.

dbms_stats.gather_index_stats(ownname => '',indname => '<index>',granularity
=> 'AUTO',estimate_percent => dbms_stats.AUTO_SAMPLE_SIZE,degree => 1);


The time can be seen to be taken in queries of the form

select /*+ no_parallel_index(t, "IREQ_REQ_TYPE_ID") dbms_stats
cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
no_expand index_ffs(t,"IREQ_REQ_TYPE_ID") */ count(*) as nrw,count(distinct
sys_op_lbid(3240035,'L',t.rowid)) as nlb,count(distinct
hextoraw(sys_op_descend("REQ_TYPE_ID")||sys_op_descend("DATE_OF_REQ"))) as
ndk,sys_op_countchg(substrb(t.rowid,1,15),1) as clf
from "CRM"."REQUEST" sample block ( .0647241806,1) t
where ("REQ_TYPE_ID" is not null or "DATE_OF_REQ" is not null)
and (TBL$OR$IDX$PART$NUM("CRM"."REQUEST",0,4,0,"ROWID") = :objn);


Even though there is a index_ffs hint, INDEX FULL SCAN is used on the index.
For other indexes where the query runs faster , FAST FULL SCAN is indeed used.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms