| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Please help with a very difficult tuning problem
On Wed, 14 Apr 1999 23:12:14 -0400, "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.
Maybe optimizer chooses this index on ID_NUMBER because this way it can avoid sorting - if it would use your composite index it would still need to perform a final sort (order by tta.id_number) as the ID_NUMBER is not the leading column in the composite index.
As both TTA.OBJID and TTA.ID_NUMBER are unique I would try changing their position in a composite index (ie TTA.ID_NUMBER, TTA.OBJID, TTA.CASE_REPORTER2SITE), thus enabling optimizer to read all the needed data in pre-sorted maner withot having to access base table.
Also, depending on the nature/contents of the column TABLE_SITE.S_TITLE, consider creating the composite index on TABLE_SITE(OBJ_ID, S_TITLE). This will eliminate the need for "TABLE_SITE access by rowid" operation, as all the needed data will be available from index.
>Thanks,
>
>Roman
HTH,
Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)
 |
 |