Re: doubt on order of table scan

From: DA Morgan <damorgan_at_psoug.org>
Date: Sat, 15 Oct 2005 16:16:35 -0700
Message-ID: <1129418200.7123@yasure>

News wrote:
> "Rene Nyffenegger" <rene.nyffenegger_at_gmx.ch> wrote in message
> news:dirph3$924$1_at_klatschtante.init7.net...
>

>>What good is the index for if the entire table must be scanned anyway?

>
> I think if there's where clause so the optimizer chooses the most convenient
> index based on statistics provided by ANALYZE in order to minimize i/o.
> Since I'm familiar with Sybase so I know it chooses the clustered index if
> it's exists where pages are sorted according to index and data & index are
> in the same pages in order to minimize I/O. I don't know if there's
> something similar in Oracle since I'm new to Oracle.
>
> If there's no where clause so it follows page chaining (no index pages
> needed).

Assuming anything about Oracle based on your experience with Sybase is, at best, high risk.

In this case your use of the phrase "clustered index" indicates that you have not read the Oracle docs. Cluster refers to many things in Oracle not one of which relates to the use of the same word in Sybase. Same goes for PAGE. This is Oracle ... PAGE does not exist: Learn BLOCK.

Thus your conclusion, too, is incorrect.

I'd suggest you get a copy of Tom Kyte's book "Expert one-on-one Oracle" and digest, with great care, the first three chapters. Then go to http://tahiti.oracle.com and read the Concept docs.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)

Received on Sat Oct 15 2005 - 18:16:35 CDT