Is SQL 7 optimiszer smart enough ?!

Article View: alt.comp.databases

Article #43

Back to alt.comp.databases Previous Next

#43

From: Dan G
Date: Fri, 03 Sep 1999 00:00

33 lines
2173 bytes

Hello all,

I propose all of you one puzzle:
Problem:
Consider one table with col1,col2 (with 23.682 records) with non-cluster
index on col2

Run this query with show stat i/o on (in SQL 7):

select col1
from
 table
order by
col2


The result was:
(23682 row(s) affected)

1) Table 'POS'. Scan count 1, logical reads 23782, physical reads 1175,
read-ahead reads 94.
in 22 seconds
2) Table 'POS'. Scan count 1, logical reads 1132, physical reads 628,
read-ahead reads 1136.
in 7 seconds

Query:

1)Which of the above results have been returned by using table scan and
which one by using the index on col2 ? .
2)Does the optimiser used table scan or the index ?.

I am looking forward ...

Technical Details

Message-ID: <7qooh5$h93$1@pollux.ip-plus.net>

Path: arc1-ca.pugleaf.net!unknown!not-for-mail

Show Full Headers

Complete Article Headers

X-Google-Language: ENGLISH,ASCII-7-bit
X-Google-Thread: f816f,e51e0c474236855d,start
X-Google-Attributes: gidf816f,public
X-Google-Thread: fa4f9,e51e0c474236855d,start
X-Google-Attributes: gidfa4f9,public
X-Google-Thread: 104de1,e51e0c474236855d,start
X-Google-Attributes: gid104de1,public
X-Google-Thread: f6611,e51e0c474236855d,start
X-Google-Attributes: gidf6611,public
X-Google-Thread: 10a90d,e51e0c474236855d,start
X-Google-Attributes: gid10a90d,public
X-Google-Thread: f99a8,e51e0c474236855d,start
X-Google-Attributes: gidf99a8,public
X-Google-Thread: fc874,e51e0c474236855d,start
X-Google-Attributes: gidfc874,public
X-Google-Thread: 107d2f,e51e0c474236855d,start
X-Google-Attributes: gid107d2f,public
From: "Dan G" <Gavrilescu.Daniel@pmintl.ch>
Subject: Is SQL 7 optimiszer smart enough ?!
Date: 1999/09/03
Message-ID: <7qooh5$h93$1@pollux.ip-plus.net>
X-Deja-AN: 520646111
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3
X-Complaints-To: news@ip-plus.net
X-Trace: pollux.ip-plus.net 936371557 17699 194.209.91.2 (3 Sep 1999 15:12:37 GMT)
Organization: Swisscom IP+ (post doesn't reflect views of Swisscom)
NNTP-Posting-Date: 3 Sep 1999 15:12:37 GMT
Newsgroups: alt.comp.databases,bit.databases,bit.databases.mssql-l,comp.databases.ms-sqlserver,comp.databases.oracle.misc,comp.databases.oracle.server,microsoft.public.sqlserver,microsoft.public.sqlserver.misc,microsoft.public.sqlserver.programming,microsoft