Posted by Peter on 04/16/06 10:12

Serge,

I see you are a IBM expert. Is your story true for DB2 only or do you know
it is true for Microsoft SQL Server 2000 too?

Because, if i do a select on a view (select * from <atable>) with only a
projection (only a few colums in the select list), I see in the execution
plan a clustered index scan with a minimized returned data row size. Also,
when I use a where clause on a indexed column, I see an index seek in the
query plan. Also, in the query plan, I dont see view names, but only table
names. It seems to me it works like documented by Microsoft, in the
execution plan, the view is replaces by the underlying view logics, but only
what is nessecary.

Indeed, when I add a new column to the underlying table, the view is not
changed until I recompile the view. What is the problem with that if
existing code never do a select * on the view? And if existing code does a
select *, then, I think this code wants all columns. So it is better to
recompile.

Do I oversee something? Please give a reaction if I am right or I oversee
something.

"Serge Rielau" <srielau@ca.ibm.com> wrote in message
news:4adcb8Fs7njpU1@individual.net...
> Peter wrote:
>> People are telling me it is bad to put
>>
>> select * from <atable>
>>
>> in a view. I better should list all fields of the table inside the
>> definition of the view.
>>
>> I dont know exactly why but some say:
>>
>> A select * from makes sql server does a table scan.
>>
>> Is that true, even if i put a where on the select on the view? And what
>> if i dont list all fields in the select on the view?
> There are two reasons speaking against select * in views and select * in
> general.
> 1. Typically your app does not need all columns.
> So by using select * you will:
> a) flow wider rows than needed
> b) force the DBMS to access the data page even if, in reality a mere
> index access would have been sufficient. Once you access the data page
> anyway the optimizer will be tempted to use more table scans, but that's
> really secondary damage.
> Within teh context of a view you will force the optimizer to do more work
> than needed. I.e. it needs to drop unused columns which may or may not
> work depending on teh capabilities of the DBMS.
>
> 2. When a column is added to the table after the view is created the SQL
> standard required "conservative" semantics. That is teh existing view will
> not pick up the new column.
> If however you drop and recreate the view (for whatever reason) the view
> will pick up the new column. This can cause some rather unexpected
> behavior which may be hard to debug.
> The select * in a way is a time bomb.
>
> I use SELECT * as a convenience for throw-away, ad-hoc queries, but not
> for anything related to a production system.
>
> Cheers
> Serge
> --
> Serge Rielau
> DB2 Solutions Development
> IBM Toronto Lab

[Back to original message]