Re: SQL database makovers (was: Query optimizer tells me to build an...

[R Bruce Hoffman <bruce.hoffman@xxxxxxxxxxxxxxxxx>]

On Tue, 2004-03-30 at 18:08, Vern Hamberg wrote:
> AFAIK, physical files are not the same as flat files. They ARE the same as 
> tables created through the SQL CREATE TABLE statement.
> 

Not entirely true. For example, there are two code paths used for
selecting from a table scan.

If you are selecting on a numeric value, and that table was built with
DDS, then the optimizer is forced to use the 'Derived field' selection.
If the table was built with SQL, then the table scan can use the Direct
selection. The code path is shorter on the Direct selection.

> And keyed logical files and SQL indexes are essentially the same thing - 
> B-trees.

The Access Path part is, but most keyed logical files show up as views,
even when no mapping takes place. And sometimes, they don't even show up
as views. But the optimizer still considers non-sparse access paths
during the optimization phase.