Re: Yet another "why is my index not used" question
From: Radoulov, Dimitre <cichomitiko_at_gmail.com>
Date: Tue, 27 Jan 2009 09:48:11 +0100
Message-ID: <glmhob$b4$1_at_reader.motzarella.org>
Shakespeare wrote:
>>
>> Hi all,
>>
>> our environment: 9i EE 9.2.0.4 64bit, Solaris 8
>>
>> We're trying to understand why the CBO picks suboptimal
>> single partition scan, when there is a more efficient
>> index range scan available.
>>
>> Some details (table name modified):
>> segment statistics are gathered with the following command:
>>
>> exec dbms_stats.gather_table_stats(user,'T', cascade=>true, -
>> method_opt=>'for all columns size 1')
>>
>> CPU costing is disabled (no system statistics).
>>
>> The computed cardinality seems correct, the clustering factor of the
>> index, we're interested in, seems realistic.
>>
>>
>> Segment space management is manual (no ASSM).
[...]
.....
Date: Tue, 27 Jan 2009 09:48:11 +0100
Message-ID: <glmhob$b4$1_at_reader.motzarella.org>
Shakespeare wrote:
> Radoulov, Dimitre schreef:
>>
>> Hi all,
>>
>> our environment: 9i EE 9.2.0.4 64bit, Solaris 8
>>
>> We're trying to understand why the CBO picks suboptimal
>> single partition scan, when there is a more efficient
>> index range scan available.
>>
>> Some details (table name modified):
>> segment statistics are gathered with the following command:
>>
>> exec dbms_stats.gather_table_stats(user,'T', cascade=>true, -
>> method_opt=>'for all columns size 1')
>>
>> CPU costing is disabled (no system statistics).
>>
>> The computed cardinality seems correct, the clustering factor of the
>> index, we're interested in, seems realistic.
>>
>>
>> Segment space management is manual (no ASSM).
[...]
> > Does a change of size in exec dbms_stats.gather_table_stats(user,'T', > cascade=>true, - > method_opt=>'for all columns size 1') > > have any effect? What happens if you set it to, say, 100 ?
It's a very busy and critical production environment where any "change" (especially for access paths) should be tested thoroughly so introducing histograms will take time.
Perhaps I'm missing something, but if the estimated cardinality of the index scan is correct (see below) the issue should not be related to data distribution (skewed data):
hinted version:
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=11018 Card=56013 Byt
es=9298158)
1 0 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'T' (Cost=11
018 Card=56013 Bytes=9298158)
2 1 INDEX (RANGE SCAN) OF 'IDX_2' (NON-UNIQUE) (Cost=34
1 Card=56013)
^^^^^
.....
53710 rows processed
Regards
Dimitre
Received on Tue Jan 27 2009 - 02:48:11 CST