Re: Show/Explain Query Plan

sybaseuser_at_hotmail.com wrote:
>
> In article <6ttohk$mvb$1_at_nnrp1.dejanews.com>,
> kenho_at_my-dejanews.com wrote:
> > In article <6tsuh2$neq$1_at_imsp009a.netvigator.com>,
> > paulkcng_at_news.netvigator.com (Ng K C Paul) wrote:
> > > Sometimes, I wonder if I should trust the plan returned from the query
> > > optimizer. For example,
> > >
> > > create table aaa (a int)
> > >
> > > select * from aaa
> > > where 1=2
> > >
> > > Some plans show table scan for the above query. Is it normal?
> > >
> >
> > It should be always table scan because this query ask to return all
> > the data in table aaa: it does not make sense to use index scan right?
> >
> > Ken
> >
>
> Actually this query ask that no row be returned because 1 is never equal to 2.
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum

Looking at it, I would be tempted to say that it is not the query plan which is significant, but the statistics i/o. If you say "select @@version", you get a table scan in the showplan! "set statistics io on" and run your query. How many times does it scan the table, how many io's? That is a more relevant question. I have just played with this in System 10. This is what I got...

select *
from #temp
where 1 = 2

STEP 1
The type of query is SELECT.
FROM TABLE
#temp
Nested iteration
Table Scan
Table: #temp________00000080017302431 scan count 0, logical reads: 0, physical reads: 0
Total writes for this command: 0

#temp has following structure ...
(I created it as a select * into #temp from <big-database>..sysobjects)

name    rowtotal  reserved  data     index_size  unused
#temp   2261      222 KB    218 KB   0 KB        4 KB

This shows that although it shows a table scan in show-plan, it doesn't actually do one.

gus Received on Fri Sep 18 1998 - 00:00:00 CDT