Please help with a very difficult tuning problem

From: Roman Gelfand <rgelfand_at_masmid.com>
Date: Wed, 14 Apr 1999 23:12:14 -0400
Message-ID: <7f3l9v$cc2$1@news.monmouth.com>

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 Wed Apr 14 1999 - 22:12:14 CDT