index scan and seek help...

hi,
I create a view within 3 tables unoin and every table has a cluster index
(corgn ,cssym).
create view vsc1
as
(select * from tableA
union
select * from tableB
union
select * from tableC)
select * from vsc1 where corgn = '2213' and cssym = '200502'
It's very weired the execution plan shows one of the tables, says tableA,
is clustered index scan instead of clustered index seek.
I recreate the view with only one table (tableA) and found the execution
plan shows a clustered index seek.
Why SQL SERVER has such a different execution plan?
Any help is very appreciated.
moash wrote:
> hi,
> I create a view within 3 tables unoin and every table has a cluster
> index (corgn ,cssym).
> create view vsc1
> as
> (select * from tableA
> union
> select * from tableB
> union
> select * from tableC)
> select * from vsc1 where corgn = '2213' and cssym = '200502'
> It's very weired the execution plan shows one of the tables, says
> tableA, is clustered index scan instead of clustered index seek.
> I recreate the view with only one table (tableA) and found the
> execution plan shows a clustered index seek.
> Why SQL SERVER has such a different execution plan?
> Any help is very appreciated.
I guess your tables differ in any of
- size
- distribution
- indexes
You would have to provide table definitions including indexes and an
outline of the volume in those tables for more concrete answers.
Kind regards
robert
|||An index scan does not always mean a full scan. If you look closely it
usually says scanning an index or a particular range of rows from the index.
If the value you chose had several rows that matched it the leaf level of
the index can be scanned to retrieve all the matching rows after the initial
seek.
Andrew J. Kelly SQL MVP
"moash" <moashPPP@.hotmail.com> wrote in message
news:%238pmbE1ZFHA.1448@.TK2MSFTNGP09.phx.gbl...
> hi,
> I create a view within 3 tables unoin and every table has a cluster index
> (corgn ,cssym).
> create view vsc1
> as
> (select * from tableA
> union
> select * from tableB
> union
> select * from tableC)
> select * from vsc1 where corgn = '2213' and cssym = '200502'
> It's very weired the execution plan shows one of the tables, says tableA,
> is clustered index scan instead of clustered index seek.
> I recreate the view with only one table (tableA) and found the execution
> plan shows a clustered index seek.
> Why SQL SERVER has such a different execution plan?
> Any help is very appreciated.
>
|||Also, just as an FYI... the union operator automatically eliminates
duplicate rows, which can be very expensive... If you know there will not be
dupe rows, or do not care, use the Union All command instead.
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"moash" <moashPPP@.hotmail.com> wrote in message
news:%238pmbE1ZFHA.1448@.TK2MSFTNGP09.phx.gbl...
> hi,
> I create a view within 3 tables unoin and every table has a cluster index
> (corgn ,cssym).
> create view vsc1
> as
> (select * from tableA
> union
> select * from tableB
> union
> select * from tableC)
> select * from vsc1 where corgn = '2213' and cssym = '200502'
> It's very weired the execution plan shows one of the tables, says tableA,
> is clustered index scan instead of clustered index seek.
> I recreate the view with only one table (tableA) and found the execution
> plan shows a clustered index seek.
> Why SQL SERVER has such a different execution plan?
> Any help is very appreciated.
>
|||Actually, I import these 3 tables to another DB in the same instance,with
same view and clustered indexes ,
and I found the execution plan showed all table are index seek.
But when I try to import these 3 tables with different table name to the
same DB,
the execution paln just nothing changed , tableA is always clustered index
scan instead of clustered index seek.
My box is 4 2.80 ZEON CPU,8G RAM(AWE enabled), SQL SERVER Tranditional
Chinese SP3,WIN2003 Enterprise,RAID 5
Any help is very appreciated.

> Also, just as an FYI... the union operator automatically eliminates
> duplicate rows, which can be very expensive... If you know there will not
be[vbcol=seagreen]
> dupe rows, or do not care, use the Union All command instead.
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "moash" <moashPPP@.hotmail.com> wrote in message
> news:%238pmbE1ZFHA.1448@.TK2MSFTNGP09.phx.gbl...
index[vbcol=seagreen]
tableA,[vbcol=seagreen]
execution
>
|||How I can send u these infromation?

> moash wrote:
> I guess your tables differ in any of
> - size
> - distribution
> - indexes
> You would have to provide table definitions including indexes and an
> outline of the volume in those tables for more concrete answers.
> Kind regards
> robert
>
|||Also, to add to everyone else's comments, whenever you specify something
like SELECT *, chances are if there is a "Covering" Index, the Optimizer
will weigh the cost of scanning that index verses seeking a different index
and then incuring a Bookmark Lookup. The best of the two would be a
filtered "scan" on the clustered index because all columns are covered.
Since you specified that the View should return all columns, it is a safe
bet that the optimizer chose the cluster index scan over a seek because of
the covering effect.
You should always specify every column returned, explicitly, even if it is
every column.
Make this minor modification and see if it makes any difference, even when
you call the View.
Sincerely,
Anthony Thomas

"moash" <moashPPP@.hotmail.com> wrote in message
news:%238pmbE1ZFHA.1448@.TK2MSFTNGP09.phx.gbl...
hi,
I create a view within 3 tables unoin and every table has a cluster index
(corgn ,cssym).
create view vsc1
as
(select * from tableA
union
select * from tableB
union
select * from tableC)
select * from vsc1 where corgn = '2213' and cssym = '200502'
It's very weired the execution plan shows one of the tables, says tableA,
is clustered index scan instead of clustered index seek.
I recreate the view with only one table (tableA) and found the execution
plan shows a clustered index seek.
Why SQL SERVER has such a different execution plan?
Any help is very appreciated.