Re: Please help with a very difficult tuning problem

The indexes are chosen to satisfy the equijoin first - they ended up driving it.

jh

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

--

Jared Hecker	| HWA Inc. - Oracle architecture and Administration
jared_at_hwai.com	|  ** serving NYC and New Jersey **