Re: Please help with a very difficult tuning problem

You haven't given enough information.

What optimizer are you using? Are your tables analyzed? Are you using parallel query?

And what about all thos chained rows? That isn't good!

rr

Roman Gelfand 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

Received on Thu Apr 15 1999 - 07:21:50 CDT