Re: Table scan ....

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 28 Jul 2004 06:18:25 -0700
Message-ID: <2687bb95.0407280518.50461a93@posting.google.com>

raquel_rodriguezus_at_yahoo.com (Raquel) wrote in message news:<9a73b58d.0407272150.37acf334_at_posting.google.com>...
> I have this simple query:
>
> SELECT COUNT(*)
> FROM T1, T2
> WHERE T1.COL1 = T2.COL2
>
> There is a unique index on COL1 (on table T1) and a unique index on
> COL2 (on table T2). Then why does the Explain plan show me that Oracle
> is using a table scan on T1 and T2? Should it not have used indexes on
> both tables?
>
> TIA
> Raquel.

The Cost Based Optimizer, CBO, chooses what it believes is the best plan. If the amount of data is very small then there is no point in using the indexes. Failure to generate valid statistics or allowing the statistics to become out of date can lead the CBO to make less than optimal choices.

HTH -- Mark D Powell -- Received on Wed Jul 28 2004 - 08:18:25 CDT