Re: Table scan ....

From: <sybrandb_at_yahoo.com>
Date: 28 Jul 2004 07:12:07 -0700
Message-ID: <a1d154f4.0407280612.560a1b6f@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.

If you are using Cost Based Optimizer (CBO), CBO will calculate the cost of each access path. Apparently a full table scan is cheaper in terms of necessary IO. In a full table scan Oracle will read db_file_multiblock_read_count blocks with one request, with 8k blocksize you usually read 64k in one go. If you are using indexes, and need an index range scan, 1 IO request will read one block. That you should need to avoid Full Table Scan at all cost is a Myth!

Sybrand Bakker
Senior Oracle DBA Received on Wed Jul 28 2004 - 09:12:07 CDT