|
|
Posted by Serge Rielau on 04/16/06 02:01
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
Navigation:
[Reply to this message]
|