Re: Full table scan despite appropriate indexes

From: John K. Hinsdale <hin_at_alma.com>
Date: 8 Feb 2007 02:28:59 -0800
Message-ID: <1170930539.835418.266080@l53g2000cwa.googlegroups.com>

On Feb 8, 2:59 am, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:

> The obvious guess regarding legality is that the
> two columns involved in the joins are of different
> types, and a type-coercion is taking place which
> invalidates the use of index.

Not obvious enough. ;) That would also explain why Oracle is not using
the index w/out any hints, which it should know enough to do (Aravind said stats are up to date).

Aravind, what're the data types for two columns you are joining (TCOVERAGES_SUMMARY.MONTH_SK and TDATE.DATE_SK)? You can get these in SQL*Plus with

	describe tcoverages_summary
	describe tdate

> Always use dbms_xplan.display to review execution
> plans so that you can see the use of predicates.

So what is dbms_xplan.display saying as to the predicates being used to effect the join?

If type coercion is going on, you can still create and use an index on TCOVERAGES_SUMMARY.MONTH_SK, but you may have to create it so as to convert the data type to agree w/ the type in TDATE.DATE_SK. --JH Received on Thu Feb 08 2007 - 04:28:59 CST