Re: Does NOT automatically cause a full table scan? 9.2.0.5

Ben wrote:
> yes, sorry, for example sake we'll say that t.a is indexed. I was just
> told that if a statement has NOT in it, then regardless of all else,
> you would get a full table scan. I don't think anything in Oracle is
> that cut and dry though.

The database initialization parameters can affect the apparent cost of index access paths, or force an index access path. Changing the optimizer mode to FIRST_ROWS_1, FIRST_ROWS_10, FIRST_ROWS_100, FIRST_ROWS_1000, or RULE can have this effect.

http://www.oracledba.co.uk/tips/9i_first_rows.htm

http://www.jlcomp.demon.co.uk/cbo_book/ch_01.html

http://www.oracle.com/technology/oramag/webcolumns/2003/techarticles/burleson_cbo_pt1.html

Specifying the wrong optimizer mode can cause a query that would otherwise complete in 5 seconds to take several minutes, or even hours, based on a bit of experimentation that I performed on an Oracle 10.2.0.2 database a couple months ago.

The optimizer mode is but one of several parameters that could impact whether a full table scan is used. Imagine that the initialization parameters tell Oracle that 100% of indexes are cached, the cost of an index access is 0, and the multi-block read count is 2. Could Oracle be convinced that using an index access method is more efficient than a full table scan, even if 95% of the index entries must be accessed? It could happen.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Fri Nov 03 2006 - 10:04:57 CST