Re: Show/Explain Query Plan

There is still problem when we are looking at io statistics because when we execute the same query several times, the io statistics may be changed.

Gus (gus_goose_at_hotmail.com) wrote:
: 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 Sat Sep 19 1998 - 00:00:00 CDT