Re: Please help with a very difficult tuning problem

In article <7f3l9v$cc2$1_at_news.monmouth.com>,   "Roman Gelfand" <rgelfand_at_masmid.com> wrote:
> Consider the following SQL:
>
> select tta.objid from table_case tta, table_site
> where table_site.objid=tta.case_reporter2site and table_site.s_title like
> '%Williams%'
> order by tta.id_number
>
> Explain
>
> Nested Loops
> TABLE_CASE access by rowid
> Full index scan of IND_CASE_CASE
> TABLE_SITE access by rowid
> Unique index scan IDX_TABLE_SITE
>
> 1. TABLE_CASE - 50000 rows (6000 chained rows)
> TABLE_SITE - 15000
>
> 2. unique index for tta.objid - IDX_TABLE_CASE
> unique index for tta.id_number - IND_CASE_CASE
> high cardinality non-unique index tta.case_reporter2site -
> IND_CASE_REPORTER2SITE
> composite index (tta.objid, tta.id_number, tta.case_reporter2site) -
> IND_CASE2SITE
>
> Why wasn't the composite index chosen? Taking into consideration SQL
> statement cannot be modified,
> is there a way to cause the composite index to be used by the query.
>
> Thanks,
>
> Roman
>

I've had a number of similar scenarios where Oracle has chosen not to use a composite index - even going as far as using a full table scan on a 50000 row (shallow) table, and have raised the issue with Oracle tech support as well. Oracle's 'official' respone is that the optimizer is leaning more towards full scans, on the assumption that high parallelism will give better performance in most cases: Since the optimizer can't know how many rows are likely to be returned in a composite index query, it assumes the worst and thinks full scans are better. I think it's #!@!, but that's the way they do it (and invalidating a common schema design used here). The workarounds: delete statistics (even if you use the rule hint, oracle ignores it in this case and goes for full scans: you have to spcify the index in a hint) or (worse) set max_parallel_servers to 0 in the initSID. I haven't tried using noparallel in the table definition - since it's doing a full scan on the index in your case, I don't know if that would work - anybody else tried that approach ?

ed m

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Apr 15 1999 - 19:02:28 CDT