Very Computer

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?

I believe the table scan plan is correct since there are no joins or row
filtering criteria.  Unfortunately, the plan does not show that the
evaluation of the literal apparently occurs first so the subordinate table
scan never takes place when the literal condition is false.

Hope this helps.

Quote:>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?

Hello,
        I believe the optimizer always considers a table scan and will usually
take that path if the table doesn't have many rows in it.

Robert

Quote:

> 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?

I always think that the where clause is the row filtering criteria

: I believe the table scan plan is correct since there are no joins or row
: filtering criteria.  Unfortunately, the plan does not show that the
: evaluation of the literal apparently occurs first so the subordinate table
: scan never takes place when the literal condition is false.
:
: Hope this helps.
:

: >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?
:
:

I found one of the exception in MS-SQL7 Query Analyzer.  The SQL
Execution Plan displays Constant Scan instead of table scan.

: Hello,
:       I believe the optimizer always considers a table scan and will usually
: take that path if the table doesn't have many rows in it.
:
: Robert

:

: >
: > 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?

Quote:>I always think that the where clause is the row filtering criteria

By 'row filtering criteria', I meant column references in the where clause.
The where clause in question contains only literals so it is the rows from
the result set that are filtered, not individual table rows.  No indexes
will be taken into account in the generation of the execution plan
because.there is no criteria placed on individual table rows.

The point I was trying to make is that the query plan (at least with MSSQL
Server 6.5) does not show that the boolean condition seems to be evaluated
before the table scan actually takes place so the query will run
instantaneously regardless of the size of the table as long as the condition
is false.  If condition is true, a table scan will occur, as it should due
to the absence of row filtering criteria.

I think you can guess what is the execution path of SQL Server running
the following query.

select * from table1 where col1 <> col1

: >I always think that the where clause is the row filtering criteria
: >
:
: By 'row filtering criteria', I meant column references in the where clause.
: The where clause in question contains only literals so it is the rows from
: the result set that are filtered, not individual table rows.  No indexes
: will be taken into account in the generation of the execution plan
: because.there is no criteria placed on individual table rows.
:
: The point I was trying to make is that the query plan (at least with MSSQL
: Server 6.5) does not show that the boolean condition seems to be evaluated
: before the table scan actually takes place so the query will run
: instantaneously regardless of the size of the table as long as the condition
: is false.  If condition is true, a table scan will occur, as it should due
: to the absence of row filtering criteria.
:
:
:
:
:
:

Sql Power Tools
12 Barnfield Ct.
Upper Saddle River, N.J. 07458

Contact Person:   Mary Owens       September 21, 1998
Sql Power Tools   www.sqlpower.com

20% discount for ISUG Hamburg conference attendees:
--------------------------------------------------

Attention fellow DBAs and developers attending the
October 5-9, 1998 International Sybase User Group
Conference in Hanburg, Germany.

You are eligible for a one time offer for a single
copy of the Sql Power SniFFFer enterprise version
at a 20% discount!

Regular list price is $7,500 US.

Any registered conference attendee with a Sybase
database server license that is not a product or
service competitor to Sql Power Tools is eligible
for this offer.

Offer expires 11/15/98.  Sql Power Tools reserves
total rights to determine individuals, parties or
corporations that qualify for this offer.

Sql Power Tools
12 Barnfield Ct.
Upper Saddle River, NJ  07458 USA

www.sqlpower.com
201.825.9511