|
|
Posted by Peter on 04/16/06 22:50
"Serge Rielau" <srielau@ca.ibm.com> wrote in message
news:4af2kbFst43jU1@individual.net...
> Well, first of all IBM is not a product. I could very well be an IBM SQL
> Server expert (working for IGS or Websphere for example), but I am a DB2
> expert, which I take it was what you meant. :-)
>
> My comments were generic and I'm confident they apply to any SQL RDBMS out
> there.
>
> Couple of observations:
> 1. A thumb rule for using TBSCAN over (index scan & page fetch) is when
> the selectivity is worse than, say 5%. Sure any DBSM will pick it's own
> secret number, but I don't doubt for a moment that SQL Server, having a
> cost based optimizer will eventually say: "Crap. You make me go after
> columns not in my index and I'm going to touch next to at least one row in
> any page anyway!".
> Enabling queries to use covering indexes is important. SELECT * is counter
> productive on that end.
>
I never execute select * from sometable. I only use it in a definition of a
view. If I only select a few columns from that view, I see in the query plan
the select * is never executed. Even I dont even see the name of the view.
So, the query plan shows:
select
column1
from
someviewwithselect*
returns less bytes per row compared to
select
column1,
column2
from
sameviewwithselect*
Also, if the columns I select are in a alternate index, and SQL server uses
that alternate index, SQL server doesnt go to the datapages of the table.
SQL Server gets is data only form the index blocks.
Unless the query plan is lying to me.
So to me te code
create table x (column1, column2, colum3)
select column1 from x
has the same execution plan as
create table x (column1, column2, column3)
create view y as select * from x
select column1 from y
Serge, what do I miss? I dont know what your story about indexes and scans
has to do whith selecting on views.
Navigation:
[Reply to this message]
|